Dynamically Loading excel files using SSIS

In this post i will  design a package for looping through excel  files   on  a  64 bit computer  as it requires few extra steps otherwise the package will fail.

The Package Looks like this

For Each Loop  will be looping through all sales files  and  will be mapped to Salesfilename  Variable

All the files are located under  C:\

Open the Foreachloop container  and  set the Properties for Folder to enumerate and the File Qualifier as shown below

Variable Mapping

Next step is to configure connection string for the excel source

As we will provide the file name at run time we need to set few properties Add a connection string to the design surface and right click the connection string and go to properties  ,expressions  and set connection string as

The easiest way to set this expression is to connect to excel file initially and than  copy the connection string and make necessary changes to include the variable name  and reset the Expression with connection string with the expression

Open the DataFlow drag a Excel Data Source and Oledb Destination with a mapped table

Open the properties of the Excel Source and set the Validate external meta data to  False other wise it will give validation error ,as the connection string gets its value at run time

Also  go to the For each Loop Container properties and set  delay validation to true and run the package on your 64 bit System

The Package Fails and throws an error

[Excel Source [1]] Error:

SSISErrorCodeDTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.

The AcquireConnection method call to the connection manager "Excel Connection Manager 2"

failed with error code 0xC00F9304.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

The solution is to go to Project Menu and Select ProjectProperties and  go to  Debugging and set Run64BitRuntime  to False.

And re run the package the package will execute without any errors