SSIS Excel Loop Into SQL

February 8th, 2012

If you don’t know how to import an Excel file into a SQL database, check out my tutorial here: SSIS: Import Excel File Into SQL Database

I have a folder with multiple Excel files (.xls) that I need SSIS (SQL Server Integration Services) to loop through and insert into a SQL table.

Here are my files:

Each file is setup like this:

Note: It doesn’t matter what your file names are, as long as they are .xls extension. Make sure all the headers (row 1) are identical through out each file.

I’m using Microsoft Visual Studio 2008

1. Use the SQL Server Import and Export Wizard to import excel into sql. (pick any one of the .xls files in the folder you want.)

2. Drag a Foreach Loop Container to the workspace.

3. Drag the Data Flow Task 1 we just created into the Foreach Loop Container.

4. Right-click on the Foreach Loop Container and select Edit .

5. This brings up the Foreach Loop Editor

6. Go to the Collection tab:
   a.Enumerator: Click on Foreach File Enumerator
   b.Folder: where the files are contained
   c.Files: *.xls
   d.Retrieve file name: Fully qualified

7. Select the Variable Mappings tab:
   a.Click under Variable, on drop down choose New Variable. Leave everything but change name to: FileWeJustFound. (Note Package2 is what my package name is)


   b.Click OK.
   c.Index should be set to 0 (default)

   b.Click OK.

REST OF UPDATE COMING SOON:

8.Right-click the Foreach Loop Container and select properies
   a. Find DelayValidation and set to True

9. Right-click on the SourceConnectionFile which is located inside of Connection Managers
   a. Locate Expressions and click the ellipsis (…) next to it.

   b. Property: Connection String
   c. Expression: “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + @[User::FileWeJustFound] + “;Extended Properties=\”Excel 8.0;HDR=Yes\”" You can also click the ellipsis (…) next to it to open up the Expression Builder: (if copying & pasting from here, replace the ” with real ones)

   d. Click OK.

10. Now you should be able to run the package:

11. The package will loop through all your .xls files and import them into SQL. You should get a screen that looks like the one below. If your package is red instead of green, be sure to check the Output to see what errors may have happened.

12. Now open up Microsoft SQL Server Management Studio, Select your database and then select all files in your table. The excel file data you just imported should show up:

I learned from: http://www.sqlis.com/sqlis/post/Looping-over-files-with-the-Foreach-Loop.aspx

Related posts:

4 Responses to “SSIS Excel Loop Into SQL”

  1. dinesh says:

    its not working

  2. Laura says:

    Thanks for this… I spent the last two days trying the other examples around the web. I kept getting errors on changing metadata and was absolutely stuck. Your example using the import wizard worked perfectly! My project is due tomorrow… Thanks to you, I still have a day left to use my newly imported data to generate a web report! Thanks!!!

RSS feed for comments on this post. And trackBack URL.

Leave a Reply