Hi i am new to ssis and i dont know vb scripting much. I have a set of excel files in a folder and i want to create a loop that will read onlythe first sheet of the different files in the folder. The Excel files and sheet doesnt have the same name but they have the same format. Is it possible to create such a loop... Hope u will be able to help me... it really important.. thanks in advance regards, vaylen

Yes you can create a foreach loop for worksheets: http://microsoft-ssis.blogspot.com/2012/07/foreach-excel-worksheet-enumerator.html But it always sorts alphabetical because the Getschema method is sorted ascending on the name. Or does the first worksheet always has the same name? Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Hi vaylen, 1. Drag a foreach loop container in to Control flow, 2.Create a package variable "FileName" with datatype string, and specify the value.(file location with extension.) 3.Edit the foreach loop container Click on the collection ,set the enumarator type to foreach file enumarator , and provide the folder name (C:\hai\excelfiles)and file name(*.xlsx) in mentioned text boxes and check fully qualified option. 4. Click on variable Mappings and specify the variable "FileName" and set index to "0". 5. Now Drag an DataFlowTask(DFT) inside the foreachloop container, edit the DFT and provide an connection to source and destination. Imp:: Now Create a connection to your excel file and , select the Data Access Mode as "Table or View" and mention the Name of the excel sheet as Sheet1$(this allow you to read data from first sheet of each file). 6. Now Go to Connection Manager tab and right click on connection manager and Click on properties, On Properties Window open the toggle expresion tab and select the ConnectionString on properties and wite the Expresion as "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+ @[User::FileName] +";Extended Properties="+"\""+"Excel 12.0;HDR=YES"+"\""+";" Close it and save it. 7. Now executhe the package :) Please mark the post as answered if it answers your question

Hello Kingxxx1 Your solution works fine. i have already been able to implement it but the first worksheet has different name depending on the excel files the loop is executing. the package fails When the loop execute a second Excel file as the worksheet's name doesnot match.

Hii ssis Joost, I have seen this blog previously but " RIGHT(@[User::WorksheetName], 1) == "$" " is not working. I dont know what the problem with it. I have also try the script, this also is bugging. The worksheets has different names depending on the excel file. mayBe thats y the script is not working.

Hii ssis Joost, I have seen this blog previously but " RIGHT(@[User::WorksheetName], 1) == "$" " is not working. I dont know what the problem with it. I have also try the script, this also is bugging. What's not working? Any errors? The expression RIGHT(@[User::WorksheetName], 1) == "$" is for the Foreach ADO.NET Schema Rowset enumerator solution, because it also returns named ranges and not only worksheets. You have to adjust it so that it uses the variable that you used in the foreach loop. I'm working on a new (free) excel enumerator so that you don't need scripting. Will be ready in a couple of days. The worksheets has different names depending on the excel file. mayBe thats y the script is not working. It's not possible to just get the first worksheet in the excel. Only if you start programming against the interop dll, but that requires an office installation which is not common/recommanded. Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

I have tried to put in the Ado.net schema rowset enumerator expression as in the screen shot. i don't know if i have place it in the right place. I am getting an error: SSIS package "Package1.dtsx" starting. Error: 0x6 at Foreach Loop Container worksheet: The parameter is incorrect. Warning: 0x80019002 at Foreach Loop Container worksheet: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "Package1.dtsx" finished: Failure. You Excel Worksheet Enumerator seems very interesting. It would be great to have it to make task easier. :)

I have tried to put in the Ado.net schema rowset enumerator expression as in the screen shot. i don't know if i have place it in the right place. I am getting an error: SSIS package "Package1.dtsx" starting. Error: 0x6 at Foreach Loop Container worksheet: The parameter is incorrect. Warning: 0x80019002 at Foreach Loop Container worksheet: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "Package1.dtsx" finished: Failure. You Excel Worksheet Enumerator seems very interesting. It would be great to have it to make task easier. :) No... that's not how it works Description from blog: One downside: it returns both worksheets (which have the $ suffix) and named ranges. So you need an extra dummy task in your foreach loop with an expression on the precedence constraint to the next task. Something like: RIGHT(@[User::WorksheetName], 1) == "$" Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

You Excel Worksheet Enumerator seems very interesting. It would be great to have it to make task easier. :) I'm finetuning it, will be online in a couple of days. Check out my blog or twitter accountPlease mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Great... thnks joost I have been able to understand how to do it... thank you very much. :) But i have another question. Supose i have multiple excel files and i want to import only the first worksheet but their names are always different. can i apply this method again or i must do something else?

Great... thnks joost I have been able to understand how to do it... thank you very much. :) But i have another question. Supose i have multiple excel files and i want to import only the first worksheet but their names are always different. can i apply this method again or i must do something else? First isn't possible with the GETSCHEMA solutions because it's sorted on the worksheetname. See this example: The name isn't always the same, but is it totally different each time or is there something recognizable like a prefix, postfix, part of the name? Can you distinguish the correct sheet without looking to the position or content? Only alternative if you can't come up with some rule is programming against theinterop dll, but that requires an office installation which is not common/recommanded. Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

It is very simple to load the first sheet of an excel file with diff names.. Here is the steps to follow. 1. Drag a foreach loop container in to Control flow, 2.Create a package variable "FileName" with datatype string, and specify the value.(file location with extension.) 3. Create another 2 package variables "Sheet" with datatype string specify the value ( eg:Sheet1$ or abcd$), "SheetNo" with datatype as integer ,value is "0". 3.Edit the foreach loop container (rename to FELC1)Click on the collection ,set the enumarator type to foreach file enumarator , and provide the folder name (C:\hai\excelfiles)and file name(*.xlsx) in mentioned text boxes and check fully qualified option. 4. Click on variable Mappings and specify the variable "FileName" and set index to "0". 5. Now drag one more foreach loop container(rename to FELC2) with in the first one,Click on the collection ,set the enumarator type to foreach ADO.NET Rowset Schema Enumarator , and specify the conection (create an ADO.NET Rowset Schema Enumarator) and select schema as Tables from the dropdown. 6.Click on variable Mappings and specify the variable "Sheet" and set index to "2". 7.Now Drag an for loop container with in the FELC2, edit the for each loop, set intial experssion as @SheetNo set Eval Expres as @SheetNo<1 set AssignExpresion as @SheetNo=@SheetNo+1 (Note : You will get a warning,ignore it) 8. Now Drag an DataFlowTask(DFT) inside the foreachloop container, edit the DFT and provide an connection to source and destination. Imp:: Now Create a connection to your excel file and , select the Data Access Mode as "Table or View" and mention the Name of the excel sheet as Sheet1$(this allow you to read data from first sheet of each file). 9. Now Drag an ScriptTask(Script Task1) below the For Loop Container, 10.Edit script task, select SheetNo as a readwrite variable, and edit code, add the code below Dts.Variables[User::SheetNo].Value=2; Save and Close it. 11. Now Drag one more ScriptTask(Script Task2) below the ForEach Loop Container(FELC2), 12Edit script task, select SheetNo as a readwrite variable, and edit code, add the code below Dts.Variables[User::SheetNo].Value=0; Save and Close it. 12.Now Go to Connection Manager tab and right click on Excel connection manager and Click on properties, On Properties Window open the toggle expresion tab and select the ConnectionString on properties and wite the Expresion as "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+ @[User::FileName] +";Extended Properties="+"\""+"Excel 12.0;HDR=YES"+"\""+";" 13 Similarly for ADO.NET Connection Manager also Click on properties, use the above expression(same) Close it and save it. 14. Now execute the package :), this will fill your requirement.

Thanks kingxxx1 this solution also works. i have try this one also and adapt it to my package and it works perfectly. thank you:)