ssis - How to Add the Result Set from a T-SQL Statement to a Data Flow?

Question

I have a simple SSIS package, and I'd like to complicate it a little.

Right now, it executes a stored procedure in an OLE DB Source, and adds the rows returned from the stored procedure to the data flow. Then, for each row returned, it executes an OLE DB Command transform, executing a second stored procedure (in a second database), passing the columns from the source as parameters.

The second stored procedure performs a synchronization function, and I would like to log the grand total number of adds, deletes and updates. The "sync" stored procedure uses the OUTPUT clause of a MERGE statement to get this data and return it as a resultset.

I don't see a way to get this resultset out of the OLE DB Command transform. It does not allow me to add output columns.

Short of adding a Script Transform, is there a way for me to log the grand total of the add, delete and update columns?

Answer 1

This is not as straight forward as it ought to be. That or I need to go back to SSIS class.

The OLE DB Command component can't add new rows to the dataflow, as it's a synchronous component.

It also cannot add new columns to the data flow. That's the first thing that was non-intuitive. So you'll see in my source, I have added an ActionName column of type nvarchar(10)/string length of 10. You could add the column in a Derived Column Transformation prior to the OLE DB Command component if you so wish.

Since I can't add rows to the data flow, that means I'm only able to use an OUTPUT parameter for my proc instead of using the recordset it could generate. Perhaps your stored procedure only allows for one row to be altered at a time and this is ok but has a general code smell to me.

Table definition and set up

CREATE TABLE dbo.so_27932430
(
    SourceId int NOT NULL
,   SourceValue varchar(20) NOT NULL
);
GO
INSERT INTO
    dbo.so_27932430
(SourceId, SourceValue)
VALUES
    (1, 'No change')
,   (3,'Changed');

Stored Proc

CREATE PROCEDURE
    dbo.merge_27932430
(
    @SourceId int
,   @SourceValue varchar(20)
,   @ActionName nvarchar(10) OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE
        @BloodyHack table
    (
        ActionName nvarchar(10) NOT NULL
    ,   SourceId int NOT NULL
    );

    MERGE
        dbo.so_27932430 AS T
    USING
    (
        SELECT
            D.SourceId
        ,   D.SourceValue
        FROM
        (
            SELECT @SourceId, @SourceValue
        ) D(SourceId, SourceValue)
    ) AS S
    ON
    (
        T.SourceId = S.SourceId
    )
    WHEN
        MATCHED 
            AND T.SourceValue <> S.SourceValue
        THEN
        UPDATE
        SET
            T.SourceValue = S.SourceValue
    WHEN
        NOT MATCHED THEN
        INSERT
        (
            SourceId
        ,   SourceValue
        )
        VALUES
        (
            SourceId
        ,   SourceValue
        )
    OUTPUT 
        $action, S.SourceId
    INTO
        @BloodyHack;

    /* Pick one, any one*/
    SELECT
        @ActionName = BH.ActionName
    FROM
        @BloodyHack AS BH

END

Source Query

SELECT
    D.SourceId
,   D.SourceValue
,   CAST(NULL AS nvarchar(10)) AS ActionName
FROM
(
    VALUES
        (1, 'No change')
    ,   (2, 'I am new')
    ,   (3,'I Changed')
) D(SourceId, SourceValue);

OLE DB Command setup

EXECUTE dbo.merge_27932430 @SourceId = ?, @SourceValue = ?, @ActionName = ? OUTPUT;

Results

References

• OUTPUT clause

Biml

Assuming you have the free BidsHelper the following Biml was used to generate this package.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="CM_OLE" ConnectionString="Data Source=localhostdev2014;Initial Catalog=tempdb;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;" />
    </Connections>
    <Packages>
        <Package ConstraintMode="Linear" Name="so_27932430">
            <Variables>
                <Variable DataType="String" Name="QuerySource">
                    <![CDATA[SELECT
    D.SourceId
,   D.SourceValue
,   CAST(NULL AS nvarchar(10)) AS ActionName
FROM
(
    VALUES
        (1, 'No change')
    ,   (2, 'I am new')
    ,   (3,'I Changed')
) D(SourceId, SourceValue);
]]></Variable>
                <Variable DataType="String" Name="QueryCommand">EXECUTE dbo.merge_27932430 @SourceId = ?, @SourceValue = ?, @ActionName = ? OUTPUT;</Variable>
            </Variables>
            <Tasks>
                <Dataflow Name="DFT OLEDB Test">
                    <Transformations>
                        <OleDbSource ConnectionName="CM_OLE" Name="OLESRC GenData">
                            <VariableInput VariableName="User.QuerySource" />
                        </OleDbSource>
                        <OleDbCommand ConnectionName="CM_OLE" Name="OLECMD Test">
                            <DirectInput>EXECUTE dbo.merge_27932430 @SourceId = ?, @SourceValue = ?, @ActionName = ? OUTPUT;</DirectInput>
                            <Parameters>
                                <Parameter SourceColumn="SourceId" DataType="Int32" TargetColumn="@SourceId"></Parameter>
                                <Parameter SourceColumn="SourceValue" DataType="AnsiString" Length="20" TargetColumn="@SourceValue"></Parameter>
                                <Parameter SourceColumn="ActionName" DataType="String" Length="10" TargetColumn="@ActionName"></Parameter>
                            </Parameters>
                        </OleDbCommand>
                        <DerivedColumns Name="DER PlaceHolder" />
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>