February 28, 2013 at 4:19 pm
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
March 6, 2013 at 7:46 am
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"
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply