SSIS ForEachFile loop with Bulk Insert Task - Dynamic Source Connection

Question

1) Foreachfile loop, get the all file name in c:\temp and store in user variable, TestFile.
2) There are 2 files in c:\temp i.e. test1.csv and test2.csv
3) Create Bulk Insert task in Foreachfile loop with source connection to c:\temp\test1.csv and it is running fine.
4) To bulk insert for all files in c:\temp, added Expression for Bulk Insert task where sourceConnection = @[User::TestFile]. Below error is encountered. Please help. THANKS!

The connection "c:\temp\test1.csv" is not found. This error is thrown by Connections collection when the specific connection element is not found.

Error at Bulk Insert Task [Bulk Insert Task]: The specified connection "c:\temp\test1.csv" is either not valid, or points to an invalid object. To continue, specify a valid connection.

Answer 1

Hi @Leau Bee Lin ,

added Expression for Bulk Insert task where sourceConnection = @[User::TestFile]

In this step, have you edited the property of test1 connection manager and set the ConnectionString with @[User::TestFile]?

You may also refer this blog which has detailed steps.

https://www.sqlservercentral.com/articles/ssis-basics-bulk-import-various-text-files-into-a-table

Regards，

Zoe

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October

Answer 2

It works. Thanks, Zoe.