Hi,

If you want to traverse each folder in the base folder in the foreach loop check the property traverse sub folder. It will automatically read all the files in all the sub folders.

If you want to set the base folder by variable set it not to the connection string property but source file path property.

I hope your files have the same meta data else your DFT will will fail.

Hi Sudeep

I am check marking traverse subfolders in the foreach loop enumerator configuration properties. But I am not sure how to make the connection dynamic to loop through all the excel files within the subfolders to a particular sheetname$ in my case irrespective of number of sheets the excel file has I am only interested in the sheet name "Sales Data". How do I make this happen. Any pointers would be of great help.

Thanks

Let the foreach loop the way it is. Pull a data flow task inside the DTF. Drag an excel source to it. In excel source editor, Select the excel connection manager for the any file that exists in the folder. For DataAccess Mode set it to "Tabename or view name from Variable" from the drop down list. Have a string value set to the sheet name you want as the source. Refer this variable from the drop down Variable name.

You would also need to change the file path using expression in the excel connection manager.

Hi Sudeep

Thanks for your response. Let me explain my case a bit more clear. I have a folder named Sales and this folder will have many sub folders with the name of prefix salesdate (for instance sales20111114, sales20111113, sales20111112 etc...) and each sub folder will have many different excel files and all these excel files have a sheet named Sale Data and SSIS package should populate this data to a SQL Server table. This a weekly load so when the package runs it has to loop through 7 different sub folders to grab that data. I get the below error when I try implement your inputs.

SSIS Error:

[SSIS.Pipeline] Error: Cannot find the connection manager with ID "{35481915-DC69-4359-96CD-60C0B860C94E}" in the connection manager collection due to error code 0xC0010009. That connection manager is needed by "runtime connection "OleDbConnection" (88)" in the connection manager collection of "component "Excel Source" (81)". Verify that a connection manager in the connection manager collection, Connections, has been created with that ID.

Thanks in advance............

I get what you are saying. And my solution will work in that case. For the error: Are you copying the package from another place and pasting here? If that is the case, create the excel connection manager again, and open the excel source and select the connection manager again. This error should go.

Hi Sudeep

I am doing accordingly per your inputs I get these below errors:

Error at SSISPackage [Connection manager "Sale Excel Source"]: The connection string format is not valid. It must consist of one or more components of the form X=Y, separated by semicolons. This error occurs when a connection string with zero components is set on database connection manager.

Error at SSISPackage: The result of the expression "@[User::varFilePath]" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property.

Error at Data Flow Task [Sale Source [52]]: SSIS Error Code DTSEOLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

Error at Data Flow Task [Sale Source [52]]: Opening a rowset for "'Sale Data$'" failed. Check that the object exists in the database.

Thanks

For excel conection donot set the connection property using the variable. Set the ExcelFilePath expression to your variable. This should resolve your issus.

Hi Sudeep

I tried that option too, no luck yet. Is there a way I could share my package with you so you could have a look too?

Thanks

Mail it to sudeep@beyondrelational.com