SSIS Loop Excel Workbook

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
green arrow. 


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:!AuZYHSwsevw1gm56VrA27ILqQ4GA


About chanmingman

Since March 2011 Microsoft Live Spaces migrated to Wordpress ( till now, I have is over 1 million viewers. This blog is about more than 50% telling you how to resolve error messages, especial for Microsoft products. The blog also has a lot of guidance teaching you how to get stated certain Microsoft technologies. The blog also uses as a help to keep my memory. The blog is never meant to give people consulting services or silver bullet solutions. It is a contribution to the community. Thanks for your support over the years. Ming Man is Microsoft MVP since year 2006. He is a software development manager for a multinational company. With 25 years of experience in the IT field, he has developed system using Clipper, COBOL, VB5, VB6, VB.NET, Java and C #. He has been using Visual Studio (.NET) since the Beta back in year 2000. He and the team have developed many projects using .NET platform such as SCM, and HR based applications. He is familiar with the N-Tier design of business application and is also an expert with database experience in MS SQL, Oracle and AS 400.
This entry was posted in .Net, Community, Computers and Internet and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s