Post reply

Lookup a unique id and add it to the data flow

  • August 6, 2008 at 2:32 am

    #122836

    Hi,

    I'm trying to import a flat file into a table. Before this, I have to lookup a sequence number in another table, increase it’s value and add this number as a unique id to each row. I tryied to do this with a script component in synchronous and asynchronous transformation but couldn’t make it work, because in the synchronous mode I don’t have the opportunity of CreateNewOutputRows() in the asynchronous mode the values of the flat file get lost during transformation.

    Does anybody has another suggestion, how to mananage this task?

    Thanks in advance!

    Alexander

  • August 6, 2008 at 12:46 pm

    #854068

    a quick thought on how to do this would be to have a stored procedure that handles the unique id processing and return the value back in an output parameter. then, in the ssis execute sql task, map that output parameter to a package variable.

    after the execute sql task, place in a data flow task that will transform the flat file into the database but do not do a direct connect from the file to the database. instead, add in the file source, add in a derived column task and then connect the derived task to the database destination.

    your derived column would be the package variable and now that value is part of your data flow set.

    hth

  • August 7, 2008 at 9:13 am

    #854527

    Chuck,

    thanks for your advice but I try to avoid using stored procedures, because I want to stay db independent. I found a very interesting article by Phil Brammer:

    http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/[/url]

    This is how it works in short words: First I create a sql task in the control flow to get the max(id) from my sequence table and pass it to a variable. In the dataflow I take that variable, increase it in a script component loop and append this new ids to each row set. Then I pass the value of the last id to that variable back again. After the data flow has ended, I write the variable back into the sequence table. And thats it. It's pretty easy, isn't it.

    There might be one problem, I couldn't check yet. I don'nt know, what happens, if a user saves data during the import. This could get me trouble with my unique ids.

    Alexander

Viewing 3 posts - 1 through 2 (of 2 total)

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