Search code examples
sqlssisdts

simple SSIS help

i need to create a simple package that will:

  1. insert a data to table A

  2. get the scope_identity()

  3. insert multiple rows to table B with the id from table A

i created one, but it only does bulk insert, so i want to redo it again.

any help would be appreciated.

Solution

  • Ah, in this case I would probably not use a lookup but an "Execute SQL"-task.

    You can have this execute an "arbitrary" SQL statement.Getting the last inserted identity value can be a bit tricky though. If you are not concerned about concurrent inserts not controlled by you a simple "MAX(...)" might do. Something along the lines of

    SELECT max(<column_name>) FROM <A>

    Otherwise it gets ab bit more involved. Look for an SQL statement that does what you want (this has nothing to do with SSIS, just with SQL). As I assume you are using MSSQL you might have a look at "SQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record" for example.

    Once you have your SQL plug it into an "Execute SQL" task and save its return value into a variable. See "How to load a new table with the value of a variable from SSIS package?" for hints how to do this (sorry, I can't find a perfect example right now...). Maybe also either "How to use OUTPUT parameters with SSIS Execute SQL Task" or "Technet - SSIS Execute SQL Task".

    And bear in mind: you do not "use <whatever component> from the destination component" but before it and store the result (here: current identity value) in a variable you can then use later.