Search code examples
sqlsql-serverssisetlsql-server-data-tools

Get Inserted ID from OLE DB Destination using SSIS Package

I have used OLE DB Destination to insert data to the SQL server table. Is it possible to get the Inserted ID from the table without using query.

I can achieve this using 

INSERT INTO testBook(name) VALUES ('Test Book')
SELECT SCOPE_IDENTITY()

by selecting data access mode to SQL COMMAND.

But Is there any way to achieve this without using SQL query and using only the SSIS components?

Please advice.

Solution

  • Execute SQL Task

    If i have this issue, i will add an Execute SQL Task after the DataFlow Task, and i will use the following command to get the Identity (or i can use one of the Identity function to retrieve it) :

    SELECT MAX(ID) FROM testBook

    And i will map the result to a variable

    OLEDB Command

    You can also use OLEDB Command instead of OLEDB Destination. you can follow This Article for a detailed solution.

    Similar Questions

    While searching i found these two similar questions asked on this website, you can refer to one of them.