Capture first rows affected in transaction and assign to SSIS variable

Question

I'm running the follow T-SQL statement from within a SSIS Execute T-SQL Statement task:

BEGIN TRANSACTION
BEGIN TRY

    INSERT  FooTable (...)
    SELECT  ...
    FROM    FooTableStaging ts
    WHERE NOT EXISTS (
        SELECT  id
        FROM    FooTable
        WHERE   id=ts.id
    );

    -- reset staging table
    DELETE
    FROM    FooTableStaging
    ;

    COMMIT TRANSACTION

END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
    ...
END CATCH

When the script is run from within SSMS, it returns two counts:

(69 row(s) affected)

(217 row(s) affected)

How do I capture the first count and assign it to a SSIS variable? Wouldn't @@ROWCOUNT equal 217 in this scenario?

** edit **

Using OLE DB connection type.

Answer 1

There's probably a few ways to do it, but the easiest in my mind would be to add the following 3 new lines to your script

-- line 1  Make a variable to hold the desired value
DECLARE @NewRowCount int;
BEGIN TRANSACTION
BEGIN TRY

    INSERT  FooTable (...)
    SELECT  ...
    FROM    FooTableStaging ts
    WHERE NOT EXISTS (
        SELECT  id
        FROM    FooTable
        WHERE   id=ts.id
    );

    -- Line 2  Capture the intended count
    SELECT @NewRowCount = @@ROWCOUNT;

    -- reset staging table
    DELETE
    FROM    FooTableStaging
    ;

    COMMIT TRANSACTION

END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
    ...
END CATCH

-- Line 3 Make a single row return statement
SELECT @NewRowCount AS NewRowCount;

Now you need to make the Execute SQL Task "hear" the row so change the result set type from the default of None to SingleRow. In the Result Set tab, click the Add button and resultset name of 0 is mapped to User::MyVariableCount or whatever you've called it.

Execute T-SQL Statement Task versus Execute SQL Task

There are two similarly named tasks available to us in the SSIS domain. The far, far, more common Execute SQL Task and the Execute T-SQL Statement Task which is available under the Database Maintenance/Other Tasks (depending on your version of SSIS).

The Execute SQL Task can use an OLE, ODBC or ADO.NET connection manager. The T-SQL Task only supports ADO.NET connection managers.

The Execute SQL Task has the ability to accept parameters and return result sets and use an SSIS Variable or a file as the query source. The Execute T-TSQ Task accepts no parameters, provides no output and can only use hard coded queries.

Given the above, I know of no situation where I'd ever use the Execute T-SQL Statement Task over an Execute SQL Task. Swap out your existing task to use the Execute SQL Task and you'll be good to go. Otherwise, the answer is it cannot be done.

Demo for getting resultset from Execute SQL Task

It's working for me, not sure what error you're running into with getting the Execute SQL Task to return a single row result set.

To simplify the process, I've skipped everything but the last line in the above SQL to make my query SELECT 1 AS NewRowCount;

I have created two SSIS variables, User::RowCountNewADO and User::RowCountNewOLE both of type Int32

My Execute SQL Task is configured as shown

The Result Set tab is set thusly

Try it yourself

It wouldn't be a proper SSIS answer for me if I didn't demo Biml. The Business Intelligence Markup Language, Biml, allows me to describe an SSIS package so you can recreate it in your environment. All you need to do is download BIDS Helper It's a free add-on for Visual Studio to help with your SSIS/SSRS/SSAS development experience.

Once installed, right click on your SSIS project and select Add New Biml File.

Paste the following into the BimlScript.biml file

Edit lines 3 and 4 to point to a valid server (Unless you run a named instance on your local machine of dev2014) and save.

Right click on BimlScript.biml and select Generate SSIS Packages

Win

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <AdoNetConnection Name="CM_ADO_DB" ConnectionString="Data Source=localhost\dev2014;Integrated Security=SSPI;Connect Timeout=30;Database=msdb;" Provider="SQL"  />
        <OleDbConnection Name="CM_OLE" ConnectionString="Data Source=localhost\dev2014;Initial Catalog=tempdb;Provider=SQLNCLI11.0;Integrated Security=SSPI;"/>
    </Connections>    
    <Packages>
        <Package Name="so_34443637" ConstraintMode="Linear">
            <Variables>
                <Variable DataType="Int32" Name="RowCountNewADO">0</Variable>
                <Variable DataType="Int32" Name="RowCountNewOLE">0</Variable>
            </Variables>
            <Tasks>
                <ExecuteSQL 
                    ConnectionName="CM_ADO_DB" 
                    ResultSet="SingleRow"
                    Name="SQL - Get Row Count ADO">
                    <DirectInput>SELECT 1 AS NewRowCount;</DirectInput>
                    <Results>
                        <Result VariableName="User.RowCountNewADO" Name="0" />
                    </Results>
                </ExecuteSQL>
                <ExecuteSQL 
                    ConnectionName="CM_OLE" 
                    ResultSet="SingleRow"
                    Name="SQL - Get Row Count OLE">
                    <DirectInput>SELECT 1 AS NewRowCount;</DirectInput>
                    <Results>
                        <Result VariableName="User.RowCountNewOLE" Name="0" />
                    </Results>
                </ExecuteSQL>
                <ExecuteSQL ConnectionName="CM_OLE" Name="Breakpoint">
                    <DirectInput>SELECT 1 AS x;</DirectInput>
                </ExecuteSQL>
            </Tasks>
        </Package>
    </Packages>
</Biml>