How to loop through Excel files and load them into a database using SSIS package

Question

I need to create an SSIS package for importing data from multiple Excel files into an SQL database  I plan on using nested Foreach Loop containers to achieve this  One Foreach File Enumerator and nested within that  a Foreach ADO net Schema Rowset Enumerator  Problem to consider  Sheet names are different between excel files but structure remains the same   I have created an Excel Connection Manager  but the Schema Rowset Enumerator is not accepting the connection manager in the Enumerator configuration   After researching  I found that you can use the Jet Ole db provider to connect to an excel file  However  I can only specify Microsoft Access Database Files as the data source  Attempting to insert an Excel File as the data source fails  After more research I found that you can use the Odbc Data Provider with a connection string instead of a DSN  After inserting a connection string specifying the Excel file this also failed  I have been told not to use a Script Task to accomplish this and even after trying a last ditch effort to extract data from sheets be accessing the sheets by index I found that the index for the sheets in the different excel files are different  Any help would be greatly appreciated

User · Answer

I had a similar issue and found that it was much simpler to to get rid of the Excel files as soon as possible. As part of the first steps in my package I used Powershell to extract the data out of the Excel files into CSV files. My own Excel files were simple but here

Extract and convert all Excel worksheets into CSV files using PowerShell

is an excellent article by Tim Smith on extracting data from multiple Excel files and/or multiple sheets.

Once the Excel files have been converted to CSV the data import is much less complicated.

User · Answer

Here is one possible way of doing this based on the assumption that there will not be any blank sheets in the Excel files and also all the sheets follow the exact same structure  Also  under the assumption that the file extension is only  xlsx  Following example was created using SSIS 2008 R2 and Excel 2007  The working folder for this example is F  Temp   In the folder path F  Temp   create an Excel 2007 spreadsheet file named States 1 xlsx with two worksheets   Sheet 1 of States 1 xlsx contained the following data    Sheet 2 of States 1 xlsx contained the following data    In the folder path F  Temp   create another Excel 2007 spreadsheet file named States 2 xlsx with two worksheets   Sheet 1 of States 2 xlsx contained the following data    Sheet 2 of States 2 xlsx contained the following data    Create a table in SQL Server named dbo Destination using the below create script  Excel sheet data will be inserted into this table   CREATE TABLE  dbo   Destination        Id   int  IDENTITY 1 1  NOT NULL       State   nvarchar  255  NULL       Country   nvarchar  255  NULL       FilePath   nvarchar  255  NULL       SheetName   nvarchar  255  NULL  CONSTRAINT  PK Destination  PRIMARY KEY CLUSTERED   Id  ASC   ON  PRIMARY  GO   The table is currently empty     Create a new SSIS package and on the package  create the following 4 variables  FolderPath will contain the folder where the Excel files are stored  FilePattern will contain the extension of the files that will be looped through and this example works only for  xlsx  FilePath will be assigned with a value by the Foreach Loop container but we need a valid path to begin with for design time and it is currently populated with the path F  Temp States 1 xlsx of the first Excel file  SheetName will contain the actual sheet  name but we need to populate with initial value Sheet1  to avoid design time error     In the package s connection manager  create an ADO NET connection with the following configuration and name it as ExcelSchema   Select the provider Microsoft Office 12 0 Access Database Engine OLE DB Provider under  Net Providers for OleDb  Provide the file path F  Temp States 1 xlsx    Click on the All section on the left side and set the property Extended Properties to Excel 12 0 to denote the version of Excel  Here in this case 12 0 denotes Excel 2007  Click on the Test Connection to make sure that the connection succeeds     Create an Excel connection manager named Excel as shown below     Create an OLE DB Connection SQL Server named SQLServer  So  we should have three connections on the package as shown below     We need to do the following connection string changes so that the Excel file is dynamically changed as the files are looped through   On the connection ExcelSchema  configure the expression ServerName to use the variable FilePath  Click on the ellipsis button to configure the expression     Similarly on the connection Excel  configure the expression ServerName to use the variable FilePath  Click on the ellipsis button to configure the expression      On the Control Flow  place two Foreach Loop containers one within the other  The first Foreach Loop container named Loop files will loop through the files  The second Foreach  Loop container will through the sheets within the container  Within the inner For each loop container  place a Data Flow Task that will read the Excel files and load data into SQL    Configure the first Foreach loop container named Loop files as shown below       Configure the first Foreach loop container named Loop sheets as shown below       Inside the data flow task  place an Excel Source  Derived Column and OLE DB Destination as shown below     Configure the Excel Source to read the appropriate Excel file and the sheet that is currently being looped through       Configure the derived column to create new columns for file name and sheet name  This is just to demonstrate this example but has no significance     Configure the OLE DB destination to insert the data into the SQL table       Below screenshot shows successful execution of the package     Below screenshot shows that data from the 4 workbooks in 2 Excel spreadsheets that were creating in the beginning of this answer is correctly loaded into the SQL table dbo Destination     Hope that helps

User · Answer

I ran into an article that illustrates a method where the data from the same excel sheet can be imported in the selected table until there is no modifications in excel with data types    If the data is inserted or overwritten with new ones  importing process will be successfully accomplished  and the data will be added to the table in SQL database   The article may be found here  http   www sqlshack com using-ssis-packages-import-ms-excel-data-database    Hope it helps

[excel] How to loop through Excel files and load them into a database using SSIS package?

Examples related to excel

Examples related to ssis