Foreach Loop Enumerator Question

Any help appreciated here...

I'm inserting a CSV file into my SQL Server 2005 Sept CTP which has approx 1700 rows. It contains Companies and Contacts which I split in a data flow and determine if with Fuzzy Lookups whether dupes exist and perform either insert or update (That works great).

Here is my issue:
The database does not use Sequence for the Pkeys and to make matters worse they are prefixed with letters. I have to generate them on my own with a custom function I have written. I need to step through each record of the CSV and assign the Pkey value and upon successfull insert update the counters table.

I cannot seem to figure out how to enumerate through this CSV file? I pull my Pkeys into Variables and thought someone might be able to advise.

Thanks,

David

I assume you are using a Data Flow task to run the import, so could you not use a Script Component to add the new PK column, calling your function?

The Script Component can add a new column, in a similar manner that you may do with the Drived Column Tx for example. You would need to use a script to be able to leverage your existing code I assume. If you could use the Derived Column, that would probably perform better.

Will this work, or have I missed something? Not quite sure what you mean by "update the counters table"?

|||I have added a Derived Column for the Company Primary Key but it generates one primary key for the entire record set from the file (1700 records).

Here is the process I'm using.
1.) DataFlow
a.)Flat File Source Adapter (1700 records)
b.)Add Derived Column for PrimaryKey
c.)ConvertData to match input Table.Column datatypes
d.)Need to Assign Primary Key to each record here.
In the control flow I query my database function to give me the next available primaryKey into a variable. I need to perform an update on the "Counters" Table which holds the current max(primarykey) so I can use the function again for the next row.

I can't seem to figure out how to iterate through the recordset where I can update my variable(pkey) for each of the 1700 records.|||

DarrenSQLIS wrote:

I assume you are using a Data Flow task to run the import, so could you not use a Script Component to add the new PK column, calling your function?

The Script Component can add a new column, in a similar manner that you may do with the Drived Column Tx for example. You would need to use a script to be able to leverage your existing code I assume. If you could use the Derived Column, that would probably perform better.

Will this work, or have I missed something? Not quite sure what you mean by "update the counters table"?

I have added a Derived Column for the Company Primary Key but it generates one primary key for the entire record set from the file (1700 records).

Here is the process I'm using.
1.) DataFlow
a.)Flat File Source Adapter (1700 records)
b.)Add Derived Column for PrimaryKey
c.)ConvertData to match input Table.Column datatypes
d.)Need to Assign Primary Key to each record here.
In the control flow I query my database function to give me the next available primaryKey into a variable. I need to perform an update on the "Counters" Table which holds the current max(primarykey) so I can use the function again for the next row.

I can't seem to figure out how to iterate through the recordset where I can update my variable(pkey) for each of the 1700 records.|||Using the Derived Column, would mean that you assign the PK value as part of the derivation/expression. If you can't, loose this transform.

If you need to call a T-SQL function, then a couple of ideas-

Can you do that in a Lookup, customize the SQL? I haven't tried this in SSIS, but DTS allowed any old SQL to be used.

Or

Use a Script Component. This could open a SQL connection (ADO.Net connection manager), call your function, update your table, do what you want to get your new PK value. Then assign in. The Script component process row method is called once per row, so you should have no problem. The "how to iterate" issue is a non issue when your are doing row by row processing.|||I will work with the Script Component. I'm more DB savvy then Scripting but figured I would have to learn it sooner or later. Thanks for the help. Good to know the best option.

--David

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.