Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Adding identity values in a data flow


Adding identity values in a data flow

Author
Message
sperry-750868
sperry-750868
SSC-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 Visits: 362
Hi All

Having an issue with SSIS that is doing my nut in.

In one of my data flows i wish to assign identity values to all new rows based on the results on a stored procedure. The stored procedure in question takes the number of inserts as a parameter and returns the seed values of the start of the identity increment.

I have been using a script component (We will call this SC1) to add the identity values to the data flow as well as calling the stored procedure from the same script component, both of these actions are working fine. What I am having trouble with is gathering the number of rows to insert and passing that value into SC1.

I have been used both the row count component or a second script component (Called SC2) to gather the number of rows to insert and pass this value into a variable , again both of these work fine and i can see by using a breakpoint at the end of the dataflow that the variable has the correct value.

What I am having problems with is using that variable in the same data flow. This issue is highlighted in SC1, In the pre execute method i am passing the variable into the script component and then executing the stored procedure to get the seed value. In processinputrow method i am thing using that seed value to add a new column to the date flow then incrementing the seed value by one.

The stored procedure executes fine but the range value (Which is set from the variable or the rowcount or SC2) is always passed through as 0 rather than the number of new rows. I believe this is because the pre execute method executes on entry to the data flow where the variable had not yet been set.

I am at a bit of a loss as to how I achieve my desired outcome, any ideas.

I have tried to explain this as best i can but please let me know if further clarification is required.

Thanks in advance!

Steve
marcel.eppel
marcel.eppel
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 50
You would have to wait for the scripttask to process all rows and set variable value after endofrowset.

Since you are already using stored procedure here, why not just insert the new data via procedure and let the proc return the inserted.id or SCOPE_IDENTITY() as an output variable. You can use these output values directly in the dataflow by using OleDBCommand task.

Also you might want to have a look at the following articles by Todd McDermid:
"Todd MCDermid - Inserting Records AND Getting the Identity in SSIS"
Part 1
Part 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search