Cannot Set Connection String For Bulk Insert Source Connection

I came across something strange today. I was wondering I was doing something wrong.

I want to go through a directory and get files and assign the Fully Qualified File Name to a User Variable. 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 Task and File System task. It is NOT working for Bulk Insert. Did you come across this or am I doing something wrong?

- Expressions
ConnectionString @.[User::FileName]

Did anyone come across this before?

Thanks

Sutha

Seems to be a problem to me, it does not work within the context of a for each loop, keep getting the error message, connection not valid.

I have written this script and placed it into an activex task before the bulk insert, this seems to fix it.

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

Hope this helps !

Mark|||

Cheers Mark. It does work fine. I put a script task inside Foreach, just before Bulk Insert to set the connections.

Still thinks its a Bug. Anyone from MS can confirm it. If so do you want me to raise a Bug for this?

Thanks for your help

Sutha

|||

Hello Sutha,

I am trying to achieve the same. Can you help me.

Foreach loop container is reading all the files in the specified folder.

I can also read the variable. But I am having difficulties in assiging this variable file name to Bulk Insert, connection, Source connection.

thanks & regards

radha.

|||I could not reproduce this issue.

1. I could set the value of the "SourceConnection" property to the name of the connection I want to use in my bulk insert task and it works.

2. I can also assign the value of the "ConnectionString" property of the connection to a variable that points to the file and it works.
|||

I am having same issue:

Simple package: Foreach Loop, inside ForEach Loop is a Bulk Insert Task.

I set the variable User::FileName in the variable Mappings of the Foreach Loop. I create a FlatFile connection in connection manager and go into the Expressions of the flat file connection and set the ConnectionString to be @.[User::FileName]. I set the connection in the Bulk Insert to be the Flat file connection I created and I run the package... I get the error: "The specified connection "XYZ Files" is either not valid, or points to an invalid object. To continue, specify a valid connection." Its very reproduceable.

Been fighting with this all day... can someone post step-by-step how to do the workaround where the script sets the connection?

|||Dim connmgr As ConnectionManager = Dts.Connections("Name of Flat File Conn Manager")
connmgr.ConnectionString = Dts.Variables("Var Name of Full path and filename").Value.ToString()
connmgr.AcquireConnection(Nothing)|||See this link for a Dec 27 Blog. http://denster.spaces.live.com/