OLE DB Command Transformation for INSERT statement : get the inserted rows ID in the output

Question

Hi all,

I may ask a stupid question, but I don't know how to do this :

I am inserting rows into a database using an OLE DB Command Transformation in a dataflow. This is the SQL Statement I use :

INSERT INTO table1 (Field1, Field2) VALUES(?, ?)

I mapped the ? with the right params. The rows are well inserted.

What I want is to retrieve the ID (primary key) that has been given by the server as a new column output of the OLE DB Command Transformation. Of course, I could SELECT again the rows, but if possible, I want to output the ids and continue my dataflow.

Is this possible ? Thanks.

Best regards.

---

Matteo, .NET Developer and System Engineer

Answer 1

Hello Matteo,

you are not the first to ask, in general you incorporate it into a SP and then return the value off it using @@IDENTITY then in the Execute SQL Task capture the full resultset.

I will better point you out to a blog post on how to do that in more details: http://bit.ly/IXpeHV

---

Arthur My Blog

Answer 2

Use @@SCOPE_IDENTITY  not @@IDENTITY

---

Chuck

Answer 3

Hi,

Thank you for your response. Actually, I cannot use Stored Procedures, because I cannot change the database structure. I can only use SSIS objects.

Regards.

---

Matteo, .NET Developer and System Engineer

Answer 4

Still, must be doable, just write the proper code

---

Arthur My Blog

Answer 5

Hello Matteo,

you are not the first to ask, in general you incorporate it into a SP and then return the value off it using @@IDENTITY then in the Execute SQL Task capture the full resultset.

I will better point you out to a blog post on how to do that in more details: http://bit.ly/IXpeHV

---

Arthur My Blog

According to the blog, in my case I cannot do that without SELECT the rows again from the server...

---

Matteo, .NET Developer and System Engineer

Answer 6

Does this help: http://sqlserverpedia.com/blog/sql-server-bloggers/inserting-records-and-getting-the-identity-in-ssis-part-2/

---

Arthur My Blog

Answer 7

The only way I can think of would be to use a script transform to execute your query and a SELECT @@SCOPE_IDENITY.  Then you could insert the returned value into the stream in a new column.

---

Chuck

Answer 8

Thanks for it. That would be great but not for performance. I have many records to insert at each time...

---

Matteo, .NET Developer and System Engineer

Answer 9

Thanks for it. That would be great but not for performance. I have many records to insert at each time...

---

Matteo, .NET Developer and System Engineer

Its not going to be any slower than using a OLE DB Command Transformation once per row - you would be doing the same thing in script but have access to a more complete featureset.

---

Chuck