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.

 clip_image002

3. Drag Excel Source to the  page.

The Excel Source Editor opened.

4. Click new to connect to the Excel file.

clip_image004

5. Select the Excel file and click  OK.

clip_image006

6. Choose Table or View in Data  access mode.

7. Choose the Worksheet in Name of the Excel  sheet.

clip_image008

8. Click on Columns to check the columns.

clip_image010

10. Click OK to complete the Data Source.

clip_image012

11. Drag the OLE DB Destination to Data Flow page.

clip_image014 

12. Double click on the OLD DB Destination to configure  the Database connection.

clip_image016

13. Click New to create a new connection.

clip_image018

14. I have created a MS SQL Server northwind connection  so I am going to use that.

clip_image020

15. I duplicated the Products table to become Products1 table in northwind database. I choose Products1 table here.

clip_image022

16. Click the Mappings to see the columns mappings between Excel and Database table.

clip_image024

17. Ignore the ProducId because it is generated by the database.

clip_image026

18. Click OK to complete the Link Excel Source and OLD DB Destination using the
green arrow. 

clip_image028

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.

clip_image030

23. DragForeach Loop Container intoControl Flow page.

24. Drag the Data Flow Task into Foreach Loop Container.

clip_image032

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.

clip_image034

32. Click on All.

This is very important.

33. Type Excel 12.0; HDR=Yes; in Extended Properties. Click Test Connection. Click OK.

clip_image036

34. Click on Variable Mappings choose User:SheetName that you have created earlier. Type 2 for Index. Click OK.

clip_image038

35. Click on Excel Source.

36. In the Properties, Access Mode choose OpenRowset from Variable.

37. Choose User:SheetName for OpenRowsetVariable  property.

clip_image040

You are good to run now.

The sample Excel Workbook you can download here: https://1drv.ms/f/s!AuZYHSwsevw1gm56VrA27ILqQ4GA

Advertisements

About chanmingman

Since March 2011 Microsoft Live Spaces migrated to Wordpress (http://www.pcworld.com/article/206455/Microsoft_Live_Spaces_Moves_to_WordPress_An_FAQ.html) 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:

WordPress.com Logo

You are commenting using your WordPress.com 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