Excel Connection Manager can be used to read the single/multiple Excel files. This connection manager supports for password protected workbooks (Not found in native SSIS Excel connection from Microsoft).

Download SSIS PowerPack

Content

Video Tutorial

Coming soon...

Step-By-Step

In this tutorial we will learn how to Create Excel Connection. You can create multiple connections too based on your requirements.
  1. Before we get started, We need Password credentials of the Excel file if it is password protected.
  2. Download and Install SSIS ZappySys PowerPack.
  3. Once you finished above steps, Open Visual Studio and Create New SSIS Package Project.
  4. Right click on Connection Managers Panel to Create New Excel Connection for Excel File, and Context Menu will appear, Select New Connection from the Context Menu.
    SSIS Create New Connection
  5. Select ZS-Excel Connection Manager from the Connection Managers list and Click on Add Button.
    SSIS Create New Connection
  6. In the opened Excel connection manager window, set the "Source Path" with the file name and the appropriate extension. This will be useful for reading from and/or writing to the single Excel file. To read the multiple excel files using Excel Source component, you can use the Pattern approach in the Excel Connection Manager. 
    NOTE: The Pattern is only applicable for the Excel Source, you can't use it in Excel Destination component.
    SSIS Excel Source - Connection Configure 
    NOTE: If you want to operation with multiple files then use wildcard pattern as below (when you use wildcard pattern in source path then system will treat source path as folder regardless you end with slash).

Examples:
c:\SSIS\Excel\file123.xlsx (single file).
c:\SSIS\Excel\file*.xlsx (all files starting with name file).
c:\SSIS\Excel\subfolder\*.xlsx (all files with .xlsx Extension and located under folder subfolder).
    SSIS Excel Source - Connection Configure
  7. Click on "Test Connection" to check if the configuration is correct or not.
  8. Click "OK" to save the Excel Connection configuration settings in the UI.
  9. Once you have finished, you will see the Excel connection in the Connection Manager panel.
    Excel Connection Manager - Execute

Properties

Property Name Description
CreateFileIfMissing It will automatically create file if missing in the path, not valid if path contains pattern (i.e. *?)
EditPassword Set the Password for modifing an Excel file
OpenPassword Set the Password for Opening an Excel file
Path Set the physical path of the Excel file
ConnectionString Set the Excel file connection string, Sample connection string
(Path=C:\demo\demo1.xlsx;ExcelVersion=Default;OutputBadDateAsNull=False;CreateFileIfMissing=False;ConnectionStringFile=;RetainSameConnection=False)

Setting UI

SMTP Connection Manager - Setting UI

See Also

References