question

LeauBeeLin-2133 avatar image
0 Votes"
LeauBeeLin-2133 asked LeauBeeLin-2133 answered

SSIS ForEachFile loop with Bulk Insert Task - Dynamic Source Connection

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.





sql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered ZoeHui-MSFT edited

Hi @LeauBeeLin-2133,

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]?

131736-screenshot-2021-09-14-104504.jpg

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



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LeauBeeLin-2133 avatar image
0 Votes"
LeauBeeLin-2133 answered

It works. Thanks, Zoe.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.