ForEach file enumeration with bulk insert problem

OK, a new package, with a Foreach container enumerating CSV files in a directory.

I create the container pointing it at the directory and retrieving the fully qualified name, and create a variable (called 'CSVFiles') with a package scope, but no value.

Inside the container is a bulk insert task. The destination db/table is set, and the input flat file connection manager for the CSV files is defined with the connection string set to the variable created above.

As it iterates through the files, the variable is correctly set to the next file in the directory (I put a message box in the stream to display the file name/variable). It resembles 'C:\temp\Location1.csv'.

But when it gets to the bulk insert, I get this error message:

[Bulk Insert Task] Error: The specified connection "CSVFiles" is either not valid, or points to an invalid object. To continue, specify a valid connection.

What's going on here? Can I not use a bulk insert task in the container? Or some other parameter needs to be set?

SQL Server 9.00.3159

You have to use a file connection instead of the variable.

HTH.

|||

thanks...I was typing a bit too fast on my first post.

The variable for the ForEach container is called 'CSVFN' and the connection manager name is 'CSVFiles'. In the properties for the connection manager, I changed the 'connectionstring' to equal the variable (@.[User::CSVFN]).

And on a related note, how do I use that variable in a T-SQL script in an Execute SQL task in the container (I get a syntax error about the variable not being defined)? I would like to insert the name of the file (from the variable) into a table for auditing purposes.

thx

|||

Kevin6 wrote:

thanks...I was typing a bit too fast on my first post.

The variable for the ForEach container is called 'CSVFN' and the connection manager name is 'CSVFiles'. In the properties for the connection manager, I changed the 'connectionstring' to equal the variable (@.[User::CSVFN]).

And on a related note, how do I use that variable in a T-SQL script in an Execute SQL task in the container (I get a syntax error about the variable not being defined)? I would like to insert the name of the file (from the variable) into a table for auditing purposes.

thx

You'd have to put the variable in the expression editor for the property "ConnectionString." Right click on the connection manager object and select properties. Scroll down to find "Expressions." Click the ellipsis and select ConnectionString. There is where you put the variable name.

Re: Execute SQL Task
Make sure that the variable is of package-level scope and that the Execute SQL Task can see it.
Then build a SQL statement like this:
insert into auditTable values (1,"Testing", ?)

Then click on the parameter mapping tab, click Add, and select the variable in the variable name column. Then use zero (0) for the parameter name. Change the data type to "VARCHAR". If you have another parameter, do the same, but use a one (1) in the parameter name box.