CODEWITHSUNDEEP
sql-serverssissql-server-2008-r2
M.Ali
M.Ali

Reputation: 69524

Foreach Container to loop through Multiple Excel File to load

I have had Packages in the past where I was looping through multiple Text files in a folder and loading into sql server tables.

Now I am asked to create a package which will loop through Multiple Excel Files in a folder and load them into sql server table.

I went through the following steps to create this package assuming it shouldn't be much different from what I have in other packages where it loops through multiple Flat file.

  1. Added an Execute Sql Task, Truncating my staging table, A simple Truncate table statement.

  2. Added a Foreach Loop Container. Selected Foreach File Enumerator and created a variable called File_Path with data type string. enter image description here
    enter image description here

  3. Added a Data Flow Task.

  4. Added an Excel Data Source. and configured the Excel Connection manager By selecting any one 'Excel' File in the destination folder. (At this point is configured correctly as it is not showing any red cross or warring messages.)

  5. Then I selected the Excel File Connection Manager and in Properties windows Under Expressions, Selected Connection String property and Used the User Variable @User::File_Path.

enter image description here

At this point the Excel Data source is showing a Red Cross as it needs further configuration.

enter image description here

I have tried a few things Like changing the Data Access Mode from Table name to Table Name or View Name Variable, And passing variable @User::File_Path but it gives me the following error.

enter image description here

Can someone please have a look and advice where I am going wrong and how I can fix this? Any Advice or a pointer in the right direction is much appreciated.

Thank you.

Upvotes: 1

Views: 2817

Answers (1)

Joost
Joost

Reputation: 1913

You shouldn't use an expression on the ConnectionString property, but on the ExcelFilePath property.

Upvotes: 1

Related Questions