Insert a single row and return its primary key

ssis

In SSIS, How do I use the execute SQL task to insert a single row with no parameters and get the primary key back so I can set it to user variable? My insert query is simply:

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

Solution 1:

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

Option 1

Execute SQL Task

General tab

ResultSet: None

SQL

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

Parameter Mapping tab

Variable Name: User::tablePk

Direction: Output

Data Type: Long

Parameter Name: 0

Parameter Size: -1

Option 2

This was the original solution as I couldn't grok how to get the placeholder ? in a normal query. It couldn't as simple as what I had above, except it was.

The only difference is the query used

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're using a data flow, I outline an approach on How to Add the Result Set from a T-SQL Statement to a Data Flow? In short, you need to add a column into the data flow prior to an OLE DB Command. Within the OLE DB Command, you will map that empty column into a OUTPUT parameter from your stored procedure and then as the stored procedure fires, it will replace the column with the value from the procedure.

Solution 2:

Alternatively to bilinkc's version, without parameters:

Execute SQL Task

General tab ResultSet: Single Row

SQL

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

In the mapping of the single-row result set, enter LastId in the result name box, and map to your variable.

May well be marginally faster with a single output parameter (bilinkc's version), depends on how SSIS does it 'under the covers' and whether it creates a full datareader versus a single sp_ExecuteSQL call with output parameter.

Solution 3:

Here is another alternative I think is very clean. It uses OUTPUT syntax and the Result Set instead of Parameter Mapping, which requires less configuration.

insert dbo.ImportData (EndDate) 
output inserted.Id 
values (NULL);

Replace Id in this statement with the name of your identity column.
Use "Single row" as the Resultset type.
No parameter mappings
Add this to the Result Set tab: Result Name: 0 (meaning the first column) and select your variable name e.g. User::tablePk (Variable type: Int32)