July 10, 2008 at 11:13 am
I'm looking to import a flat file with 140,820 records. What I'm trying to do is on one field, I've created as an output with script component, add a number that is incremented by one for each row processed. So the first row would have this field as 731 for example and the next would be 732 and so on. Not sure on how to set this up in the script component. I was thinking a while loop or something along those lines. I searched through the forums and didn't see anything like I'm trying to accomplish. If there is something like this out there a point in the right direction would be greatyly appreciated.
Thanks much,
Z
July 10, 2008 at 11:22 am
So you are loading this data into a table and you want to create a column on the table that holds this incremental value and you want to be able to tell the load what number to start the incremental column at?
Load your data into a staging table, then add an Identity column to the staging table with the seed value of your starting value (731 in your example). Once the column is added, load your data from the staging table to the final destination table.
July 10, 2008 at 11:27 am
Close. The import is going to a table in the db. These records are being added. This field needs to be incremented by for each row processed. I'm not using a staging table. I've got a flat file source, a script component, and an ole db connection with fast load. The ouput columns from the script component are mapped directly to the database. When I test this package to a flat file I can see the counter counting the rows processed. Is there a way to access that counter from my script component? Like System. .Counter?
Thanks,
Z
July 10, 2008 at 2:16 pm
If you have the freedom to add a component to your flow, you might try using the Row Number Transformation, a free component from Konesans (http://www.konesans.com/rownumber.aspx).
July 10, 2008 at 2:31 pm
This appears to be what you need to do.
"How to add an incremental counter in a SQL Server 2005 Integration Services package by using a Script component in a Data Flow task" From Microsoft Technet
Article ID : 908460
Last Review : March 11, 2006
Revision : 2.1
This contains a scripting example which will most likely perform the task of adding a row number to each row imported.
Give it a read
July 10, 2008 at 2:54 pm
bitbucket, that works beautifully. Thanks all for the input.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply