One possible way to achieve this is to use the ForEach Loop container on the Control Flow tab, and then place the Data Flow task in the Data Flow task Control task. I explained this process in the example below. This example uses the SQL Server back-end as the destination and Excel 97-2003 format .xls as source files. note that Excel files must be in the same format.
Step-by-step process:
Create a table called dbo.Location , as shown in screenshot # 1 . This example will populate this table by reading three different Excel files that have the same layout. The screenshot shows an empty table before executing the package.
Create two Excel files in the path c:\temp\ , as shown in screenshots # 2 - # 4 . Note that both Excel files have the same layout, but different content.
In the SSIS package, create three variables, as shown in screenshot # 5 . The FolderPath variable will contain the path where the Excel files are located; FileExtension will contain the Excel file extension (here *. FilePath in this case) and FilePath should be configured to point to one valid Excel file (this is only required during the initial setup of the Excel Connection Manager).
Create Excel connection in Excel connection manager pointing to one valid excel file as shown in screenshot # 6 .
Create an OLE DB Connection in OLE DB Connection Manager pointing to SQL Server.
In the SSIS package, place the ForEach Loop container and the data flow task in the ForEach loop container, as shown in screenshot # 7 .
Configure the forEach loop container as shown in screenshots # 8 and # 9 . By doing this, the User::FilePath will contain the Excel full path files located in the c:\temp\ folder using the FolderPath and FileExtension variables configured in the Collection section.
Inside the data flow task, place the Excel source to read the data from the Excel file and O LE DB destination to insert the data into the SQL Server dbo.Location table . The data flow task should look like the screenshot # 10 .
Configure the Excel source as shown in screenshots # 11 and # 12 to read data using an Excel connection.
Configure the OLE DB assignment as shown in screenshots # 13 and # 14 to insert data into the SQL Server database table.
In Excel, in the Connection Manager, configure the ExcelFilePath and ServerName expressions, as shown in screenshot < 15 .
An example of performing a data flow task is shown in screenshot # 16 .
Screenshot # 17 displays the data in the dbo.Location table after the package is executed. Please note that it contains all the lines present in the Excel files shown in screenshots # 3 and # 4 .
In the Data Flow task properties, set DelayValidation to True so that SSIS does not give errors when opening the package.
Hope this helps.
Screenshot # 1:
Screenshot No. 2:
Screenshot 3:
Screenshot 4:
Screenshot No. 5:
Screenshot No. 6:
Screenshot No. 7:
Screenshot # 8:
Screenshot No. 9:
Screenshot No. 10:
Screenshot No. 11:
Screenshot No. 12:
<T411>
Screenshot No. 13:
Screenshot No. 14:
Screenshot No. 15:
Screenshot No. 16:
Screenshot No. 17:
Screenshot No. 18:
user756519
source share