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