Search code examples
excelssismacrosxlsm

Loop through XLSM files SSIS 2012

I have a bunch of Macro-enabled Excel Files. I want to loop through each one of them with a SQL Query - select * from [Sheet1$A10:AZ100]. My Excel Connection Manager works when I have selected one file however there is a big problem when I add a For Each Loop Task and assign my variable to it. My steps are:

  • Create New Connection: Excel Connection Manager I choose the first XLSM file in the folder and hit OK.

  • Drag and Drop For Each Loop Container and double click. In collection: I choose For Each File Enumerator and in the Folder I specify the folder path. In files: *.xlsm The last option is : Fully Qualified
    In Variable Mappings: I create a new variable called 'FileFound'

  • Drag and Drop Data Flow Task in For Each Container and double click.

  • Drag Source Assistant: Choose Excel Connection Manager created above

  • Double Click Excel Source and for Data Access mode: I choose SQL Command. My query is select * from [Sheet1$A10:AZ100]. This query is same for all the XLSM files.

  • Click on columns and my data shows all OK

Now this is where the problem starts-

Right Click Excel Connection Manager and hit Properties.

  • I copy my connection String first. My connection string is: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\Excel data\ABCDE.xlsm;Extended Properties="Excel 12.0 MACRO;HDR=YES";

  • I click on Expressions and choose Connection Strings in Property. Next I edit my expression as: "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::FileFound] + " ;Extended Properties=\"Excel 12.0 MACRO;HDR=YES\";" This evaluates correctly.

  • I then set Delay Validation of the Excel Connection Manager to TRUE

  • I set Delay Validation of For Each Container to TRUE

The error I get is:

Error Message

How can I resolve this error? I have tried so much and read everything, but not able to go past this.

Solution

  • I fixed it!!

    I had to right click the properties of the 'Excel Connection Manager' and instead of choosing ConnectionString in the Extended Properties, I had to choose Excel File Path.