Loop through Excel files in SSIS

Question

Hi all,
Can anyone tell me how I can loop through several excel files in a folder please?

thnks

Milli

Answer 1

Check out my blog. It deals with Flat file. you could use an excel source instead.
However you need to keep in mind that all the excel files should have the same meta data if you wish to use data flow task.
http://rajsudeep.blogspot.com/

---

Hope this helps !!
Sudeep   |    My Blog

Answer 2

Hello Milli,

Here is an example

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

Hope it will help!!

Answer 3

Hi im getting the following error:

I have created a File enuemrator and did *.xlsx
and then created a variable mapping called "FileWeJustFound" and added that to the variable mapping and then went to the Excel Connection and went to expression and went ConnectionString and mapped that variable "FileWeJustFound" and i get the following error:

TITLE: Package Validation Error
------------------------------

Package Validation Error

------------------------------
ADDITIONAL INFORMATION:

Error at Data Flow Task [Excel Source [54]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Error at Data Flow Task [SSIS.Pipeline]: component "Excel Source" (54) failed validation and returned error code 0xC020801C.

Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

Error at Loop [Connection manager "Excel Connection Manager"]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D.

 (Microsoft.DataTransformationServices.VsIntegration)

------------------------------
BUTTONS:

OK
------------------------------

Answer 4

1.Create a ADO.NET Connection
   Select .NET Providers for OLEDB\Microsoft Jet 4.0 OLEDB Provider. Select the excel workbook using Browse
   Go to All and set Extended Properties to Excel 8.0

2.Create a variable as SheetName with value as a valid sheet (Inof$)

3. Take a Foreach loop and inside it take a DFT
    Edit the For Each Loop:
   Collection: Foreach ADO.NET Shema Rowset Enumerator
   Enumerator Configuration: Select the ADO.NET Connection created above.
   Select Table as Schema.
   Variable Mapping: Select a variable (SheetName) that will capture the sheet name and set the index as 2.

Take the excel source in DFT and define a excel connection manager (MYExcel) for it. Select the data access mode as TableName/ViewName variable and select the SheetName variable from the drop down box.
Now complete the data flow as per your requirement.

Note:
This will work for one excel work book.
For multiple workbooks you need one more foreach loop and add the earlier defined foreach loop inside this.
Select the collection as for each file enumeraror. Selec the folder where the workbooks are located.
Retrieve file name: fully qualified.
Capture the workbook name in a variable (excelfilepath) using Varibale Mapping.
Give a valid filepath to this variable while creating. (C:\A.xls)
Then go to MYExcel connection manager's Property. Go to Expressions and select the Conenction String property. Under Expressions, click on ellipsis (...) and
write
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + @[User::excelfilepath] + "
;Extended Properties="  +"\"EXCEL 8.0;HDR=YES;;"  + "\";"

You can check this thread (I have copied from there)
http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/1f2ea0ae-98e8-467d-af80-dcc3dd5e466d

---

Nitesh Rai- Please mark the post as answered if it answers your question

Answer 5

please share your excel connection string with us.

---

Let us TRY this | Don’t forget to mark the post(s) that answered your question

Answer 6

First create an excel connection for a valid file.
Once that works set the expression to set the value dynamically.

---

Hope this helps !!
Sudeep   |    My Blog

Answer 7

IF i double click my Excel Connection It shows

D:\LoopThroughExcel\A.xlsx

Then  version 2007

In  properties its:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\LoopThroughExcel\A.xlsx;Extended Properties="Excel 12.0;HDR=YES";

Now when I add the variable to my For Each Loop and then go to the properties of the Excel Connection and set the expression of ConnectionString to my Varialbe i get the error described above....

Answer 8

Check this link:
http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/f00a3766-7ce4-4911-80d8-3768cfe97464/

---

Hope this helps !!
Sudeep   |    My Blog