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!