Cannot Set Connection String For Bulk Insert Source Connection

I want to go through a directory in my local machine and get files and assign the Fully Qualified File Name to a User Variable using Foreach Component from SSIS.

I want to use that variable as a connection string within Expression in Bulk Insert Task Source Connection, Data Flow Task and File System Task. It is working fine within Data Flow and File System task. It is NOT working for Bulk Insert.

- Expressions ConnectionString @[User::FileName]

This is the expression I want to use within ConnectionString in Bulk Insert, File System Task and Data Flow Task. Same expression works fine in all except Bulk Insert. You can see the same issue being raised in Forum.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=188886&SiteID=1

From the forum I found out that I need to use Script within Foreach to set the connection string and then call Bulk Insert. You can set the connection strings as shown below (thanks to Mark)

------------------------------------------------------
Dim connmgr As ConnectionManager = Dts.Connections("[your connection name]")connmgr.ConnectionString = Dts.Variables("User::filename").Value.ToString()connmgr.AcquireConnection(Nothing)
------------------------------------------------------

I hope this helps
Thanks
Sutha