Setting SourceConnection in Bulk Insert Task

Question

Setting SourceConnection in Bulk Insert Task

keymoo

SSCrazy

Points: 2975
More actions
July 2, 2008 at 2:31 am

#122292

Hi,
I have a Bulk Insert Task in my package and I want to set the SourceConnection property to a Package Variable value. My package variable is called SourceFileName which gets set from a Package Configuration.
The Package configuration sets the SourceFileName using a UNC path (I know, this is a common trap, but I'm not falling in it this time).
I have created an Expression on the Bulk Insert Task and set SourceConnection = [@User::SourceFileName]. I've also double-clicked the Bulk Insert Task and in the Expressions tab checked to see that SourceConnection is getting set there too (which it is).
When the task runs I get an error:
[Bulk Insert Task] Error: The specified connection "\\myserver\path\to\myfile\MySourceFileName.txt" is
either not valid, or points to an invalid object. To continue, specify a valid connection.
I did some research and I see that this may actually be a bug. So, I created a Script Task and set the SourceConnection property there prior to the Bulk Insert Task running. In the Script Task I have this code:
Dim connMgr As ConnectionManager = Dts.Connections("My.Text.Source")
connMgr.ConnectionString = Dts.Variables("SourceFileName").Value.ToString()
connMgr.AcquireConnection(Nothing)
This doesn't seem to work either. Has anyone managed to successfully set the ConnectionString of a Bulk Insert Task from a package variable? Please show me how!!
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

You need to use a connection manager and set the path for the connection manager using the expression. The bulk insert task does not accept just a path.

keymoo SSCrazy Points: 2975 More actions · Answer 2

I'm not sure I'm following you. I am setting the path and file name. Could you expand a bit more? Thanks.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

RE-hard code your bulk insert task.

Once you have done this, you will find a connection manager has been created for your text file. You need to use the expression you have indicated you are using in the expressions for the connection manager, not the expressions for the bulk insert task.

keymoo SSCrazy Points: 2975 More actions · Answer 4

Thanks Michael, but I still can't get it to work. This is exactly what I did:

Deleted my existing Bulk Insert Task

Deleted the associated Connection Manager for the flat file

Created a new Bulk Insert Task, pointed the Source File to the file I want (UNC path too!)

Indeed a new connection manager was created of type FILE.

On the FILE connection manager I added an Expression so that the ConnectionString property points to the path and file I want which is stored in a package variable.

I run the package but get this error:

[Bulk Insert Task] Error: The specified connection "\\myserver\path\to\myfile\MySourceFileName.txt" is

either not valid, or points to an invalid object. To continue, specify a valid connection.

Any other ideas? Can anybody here reproduce this either successfully or not? If you are successful please post exact steps to get it to work. If you get the same error as me, perhaps its a bug?

Cheers.

Frank Bazan SSCertifiable Points: 5378 More actions · Answer 5

What service pack are you on?

There used to be a bug (can't remember when it was sorted), where by the work around was to set the connection manager in a script task just before the bulk insert runs. Also, I think it occurred when you put the bulk insert task within a loop.

I don't think this applies any more though...

Kindest Regards,

Frank Bazan

gauravmounas Valued Member Points: 72 More actions · Answer 6

Hi,

I was also facing the same problem and tried following steps to resolve this.

you need to create one flat file connection and then go to the properties for newly created connection, click on ... sign next to expressions, from there you need to assign connectionstring property from Package level variable, once you done this. Go to Bulk Insert task and in SourceConnection > File just select the connection you created, it will resolve your issue.

Gaurav Tyagi