This blog is to show you how to loop through an Excel Workbook using SSIS.
To loop through Excel in SSIS you need some tips and tricks.
The expectations of this article are:
1. You have installed SQL Business Intelligence Development Studio (BIDS) in your Visual Studio 2015.
2. You know ha blank how to create a blank SSIS project.
To start with.
1. Create a blank SSIS project.
2. Drag a Data Flow Task from SSIS Toolbox to the page.
3. Drag Excel Source to the page.
The Excel Source Editor opened.
4. Click new to connect to the Excel file.
5. Select the Excel file and click OK.
6. Choose Table or View in Data access mode.
7. Choose the Worksheet in Name of the Excel sheet.
8. Click on Columns to check the columns.
10. Click OK to complete the Data Source.
11. Drag the OLE DB Destination to Data Flow page.
12. Double click on the OLD DB Destination to configure the Database connection.
13. Click New to create a new connection.
14. I have created a MS SQL Server northwind connection so I am going to use that.
15. I duplicated the Products table to become Products1 table in northwind database. I choose Products1 table here.
16. Click the Mappings to see the columns mappings between Excel and Database table.
17. Ignore the ProducId because it is generated by the database.
18. Click OK to complete the Link Excel Source and OLD DB Destination using the
19. Click on “SSIS (menu item)” in Microsoft Visual Studio.
20. Click on “Variables (menu item)”.
21 Name the variable SheetName and select String as Data type.
22. Click the Control Flow tab.
23. DragForeach Loop Container intoControl Flow page.
24. Drag the Data Flow Task into Foreach Loop Container.
25. Click on “Collection (tree item)” in Foreach Loop Editor.
26. Click on “Enumerator ()” in Foreach Loop Editor.
27 Choose Foreach ADO.NET Schema Rowset Enumerator for the Enumerator..
28. “Connection: (combo box)” in Foreach Loop Editor.
29. “New Connection… (button)” in Configure ADO.NET Connection Manager.
30. Expand .Net Providers for OleDb.
31. Choose Microsoft Office 12.0 Access Database Engine OLE DB Provider.
32. Click on All.
This is very important.
33. Type Excel 12.0; HDR=Yes; in Extended Properties. Click Test Connection. Click OK.
34. Click on Variable Mappings choose User:SheetName that you have created earlier. Type 2 for Index. Click OK.
35. Click on Excel Source.
36. In the Properties, Access Mode choose OpenRowset from Variable.
37. Choose User:SheetName for OpenRowsetVariable property.
You are good to run now.
The sample Excel Workbook you can download here: https://1drv.ms/f/s!AuZYHSwsevw1gm56VrA27ILqQ4GA