How to loop through Excel files in SSIS

Question

Hello Everyone

I am having one folder where Excel files are stored

now i want run each file one by one and

insert data into a sql table my destination table is same .

also I have scheduled my package for every  hour,

i want package should take only those files which are new not all the files means files which are comming after every hour in the folder

Answer 1

Create a ForEach Container, set the focus to your folder and select the *.xls(x).

In that ForEach place a DFT to import your data and a Filesystem Task to move the imported files to a new folder.

The Connectionstring to the Excel files should be dynamically set to the actual file.

Answer 2

Do all excel file have the same format? Then the Foreach Loop can be used.

Is it an option to move all processed files to an archive folder with a File System Task? That's easier then checking filedates.

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Answer 3

HOW CAN WE MOVE MORE FILE THROUGH FILESYSTEM TASK

Answer 4

first i want to know about how can we add data of five different file having same column name in a single table using for each loop

Answer 5

That's not the problem. In your Dataflow you use a Connection Manager to the Excel files. The Connectionstring is set to the filename you get from the Foreach Loop.
So you point always to the actual file.
After that move the file to another folder.

But this will only work if the structure of Source and Destination is the same.

If that's not the fact you have to use one DFT for each Excel file and hav one Connection Manager for each file. And no Loop is required.

Answer 6

HOW CAN WE MOVE MORE FILE THROUGH FILESYSTEM TASK

If you place it in a foreach loop then it will move one by one... or you can move an entire folder at once. 

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Answer 7

first i want to know about how can we add data of five different file having same column name in a single table using for each loop

I agree with christa... you can if the structure of the excel is the same (same worksheet name and same columnnames within that worksheet)

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Answer 8

can you provide me some link by which i can perform this task

first i want to know about how can we add data of five different file having same column name in a single table using for each loop

Answer 9

can you provide me some link by which i can perform this task

first i want to know about how can we add data of five different file having same column name in a single table using for each loop

http://microsoft-ssis.blogspot.com/2011/04/how-to-configure-foreach-loop-container.html

this one loops through csv files, but that's the same. Instead of adding an expression to the connectionstring property of the flat file connection manager you add that same expression to the filepath property of the excel connection manager. 

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Answer 10

First create a dataflow for one excel file

If you have this and it works fine, put this Dataflw to your ForEach, configure yoir Connection manger pointig to this file synamically with the variable you created for your ForEach

Aunt google will provide a lot af links:

http://msdn.microsoft.com/en-us/library/ms141724.aspx

http://consultingblogs.emc.com/jamiethomson/archive/2005/05/30/1489.aspx

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