Generating Primary Key with Non-Zero Index (SSIS Data Flow)

Question

I've got a data flow task that takes a pair of tables, mashes the relevant data together, and comes out with some results to be put into an indexed table. The indexed table already has data that I'm not getting rid of and for simplicity's sake should retain their existing keys. So, I need to generate a key that starts from the highest Primary Key value already in the column.

I have found a blog post that works when starting from any known value, but this data flow will eventually be used on different databases, so that value won't be constant. It will always be the max of the column, though, but I can't find a way to grab that value using the script component suggested there.

Answer 1

This type of thing is notoriously difficult to do in SSIS which is why I try to avoid it. You need to:

...brace yourself...

-create a variable in your SSIS package to hold the start value

-create a SQL Task with a Parameter mapped to that variable with a direction of output and a query something like "SET ? = (SELECT MAX(IDValue) FROM Table)" - the question mark is the placeholder for the parameter which maps to the variable

-work the variable into your data flow - probably with a derived column transformation

I hope this helps...