Insert one row and return its primary key

Question

In SSIS, how to use the SQL execution task to insert a single row without parameters and return the primary key so that I can set it to a user variable? My insert request is simple:

INSERT INTO [AdWords.ImportData] (EndDate) VALUES (null) 

Answer 1

Good question, took me a few attempts to figure it out. Declare an SS32 variable of type Int32 (unless you need calibration for bigint or numeric). I chose tablePk as mine.

Option 1

SQL task execution

• General tab

ResultSet: None

SQL

 INSERT INTO dbo.ImportData (EndDate) VALUES (NULL); SELECT ? = SCOPE_IDENTITY() 

• Parameter Display Tab

Variable Name: User :: tablePk

Direction: Exit

Data type: long

Parameter Name: 0

Parameter: -1

Option 2

This was an original solution, as I could not figure out how to get a placeholder ? in a regular request. It could not be as simple as what I had above, except that it was.

The only difference is the use of the query

SQL

 DECLARE @sql nvarchar(500) , @paramDef nvarchar(500) SELECT @sql = N'INSERT INTO dbo.ImportData (EndDate) VALUES (NULL); SELECT @ident = SCOPE_IDENTITY();' , @paramDef = N'@ident int OUTPUT' EXECUTE sp_executesql @sql, @paramDef, @ident = ? OUTPUT 

Option 3

If you use a data stream, I am describing an approach. How to add a result set from a T-SQL statement to a data stream? In short, you need to add a column to the data stream before the OLE DB command. In the OLE DB command, you map this empty column to the OUTPUT parameter from the stored procedure, and then as the stored procedure starts, it will replace the column with the value from the procedure.

Answer 2

Alternative for bilinkc version without parameters:

SQL task execution

General Tab ResultSet: Single Row

SQL

 INSERT INTO dbo.ImportData (EndDate) VALUES (NULL); SELECT SCOPE_IDENTITY() AS LastId 

In the single row result set display, enter LastId in the result name field and map your variable.

It may be slightly slower with a single output parameter (bilinkc version), depending on how SSIS does this “under the covers” and whether it creates a complete datareader compared to a single sp_ExecuteSQL call with an output parameter.