Update Table from SP in SSIS

Question

I know there are similar questions to this one, but I could not find one that answered my question.

Inside my current SSIS package... I have a dataflow task which... Takes output from a stored procedure (contains about 10k rows) on the db Inserts that output into a table in the dbo

The problem is that I have 2 choices of doing this (otherwise I break primary key restrictions)

1.) Truncate the table before I insert data to it

this is problematic because what if the SP fails for some reason? Other procedures use this table and depend on it to have data.

2.)Use "Update" command to update 1 row at a time for ~10k rows

This ties up resources on our server, which is already doing some heavy lifting just to execute the stored procedure (yes, it is a poorly written SP, but I digress). I looked into doing a row count then checking the count variable before truncating, but I couldn't figure out how to make that work inside a dataflow with data stemming from a stored procedure.

So there HAS to be a way I don't know of to easily take my data and ONLY update existing records or insert new records.

P.S. SQL Server 2014

Current SSIS:

OLE DB Source = EXEC somesp.sp

|

Count = Count rows (used later on, irrelevant to task at hand)

|

Sort = Sorts for data integrity to ensure nothing will break my primary key (data coming from the sp)

|

Destination = Place records from sp result into a destination table

Answer 1

What you described is a classic UPSERT pattern, named after the dual intention of Updating some records and Inserting others.

T-SQL offers the MERGE statement which can be used and has good performance. Not always the easiest to write/maintain.

SSIS options:

1) UPSERT via SSIS - Essentially what you do is identify your update rows and store them in a temp table so that you can handle Updates via an Execute SQL task in the SSIS Control Flow instead of the row by row OLEDB command in the data flow. Essential needs are to ID your update rows and send them to the TMP table destination. Add an Execute SQL task after your data flow and update as a set. You will see huge performance increase over the OLEDB command task and this may be all you need. Handle inserts as normal into the destination table. Source Query vs Existing table join if existing then determine if an update is needed otherwise insert. You can either blindly update or in my case I have a quick join if they exist, and another check to see if there is actually an update... you can do this all in a single step depending on your data/join conditions.

2) DELETE/INSERT via SSIS - Very similar to #1, but instead of performing an update you use two SQL tasks to first delete the update rows then insert new versions of them. All non-update rows get written as normal inserts in your data flow.

Answer 2

Materially, I'm not certain how much this reply differs from Hannah or Dave, but I figured I'd drop it here in case it helps you at all. Below please find a slightly anonymized version of a dynamic sql proc I wrote for a long ETL where I have a series of parallel staging and production tables that I want incremental updates for but do not want to use the merge syntax on. Parameterized to accept table name inputs from a given staging schema.

use MyDatabase
go

if exists( select * 
            from sys.objects 
            where [object_id] = object_id( N'dbo.spInsertUpdateStageIntoProdDynamicSql' ) and 
                [type] in ( N'P' ) )
begin
    drop proc dbo.spInsertUpdateStageIntoProdDynamicSql ;
    print 'Dropped procedure dbo.spInsertUpdateStageIntoProdDynamicSql SUCCESSFULLY! At time ' + convert( varchar, getdate(), 126 ) ;
end ;
go

create proc dbo.spInsertUpdateStageIntoProdDynamicSql
    @TargetTable nvarchar( 116 ),
    @DebugOption varchar( 100 ),
    @SpoofDateTime datetime
as
/* who          when            what
petervandivier  2015-09-15      create proc to use dynamic sql to merge stage tables into prod for  
petervandivier  2015-09-23      added @TargetTable to results logging
*/
begin
    set nocount on ;
    set transaction isolation level read committed ;
    
    declare
        @RetMsg varchar( max ) = '',
        @ErrMsg varchar( max ) = '',
        @CountRows int,
        @ProcName varchar( 100 ) = 'dbo.spInsertUpdateStageIntoProdDynamicSql',
        @ExecDateTime datetime = getdate(),
        @ToggleOffClause nvarchar( max ) = N'',
        @UpdateRowsClause nvarchar( max ) = N'',
        @InsertRowsClause nvarchar( max ) = N'',
        @StageTable nvarchar( 255 ) = N'staging.' + @TargetTable,
        @DboTable nvarchar( 255 ) = N'dbo.' + @TargetTable,
        @Lb nchar( 1 ) = char( 10 ),
        @Tab nchar( 1 ) = char( 9 ) ;

    declare @ResultArray table
    (
        CountRows int,
        Step varchar( 100 ) unique,
        CommandText varchar( max)
    ) ;

    -- SpoofDateTime accepted for prior pd. cursor
    select 
        @ExecDateTime = coalesce( @SpoofDateTime, @ExecDateTime ),
        @RetMsg = 'Variables initialized SUCCESSFULLY. Parameter validation will begin. ' + @Lb + @Lb ;

    insert dbo.Log
    ( 
        Step, 
        Process, 
        CountRows, 
        Notes, 
        CommandText, 
        InsertedBy, 
        InsertDatetime, 
        LastUpdateBy, 
        LastUpdateDatetime 
    )
    select 
        'ParameterValidation',
        @ProcName,
        null,
        @TargetTable,
        '@TargetTable:=' + isnull( @TargetTable, 'null_val' ) + ';@DebugOption:=' + isnull( @DebugOption, 'null_val' ) + ';@SpoofDate:=' + isnull( '''' + convert( varchar, @SpoofDateTime, 126 ) + '''', 'null_val' ),
        @ProcName,
        @ExecDateTime,
        @ProcName,
        @ExecDateTime ;

-- Check if BOTH target and source tables exist, else escape proc
    if not( exists( select * 
                    from sys.tables
                    where [object_id] = object_id( @StageTable ) ) and
            exists( select * 
                    from sys.tables
                    where [object_id] = object_id( @DboTable ) ) )
    begin
        select @ErrMsg += 'One or both of target tables ' + @DboTable + ' and ' + @StageTable + ' does not exist current Database ' + db_name() + N'. Execution ABORTED!' + @Lb + @Lb ;
        goto QuitWithFailure ;
    end
-- Check if BOTH target and source tables have Primary Keys for merge / upsert, else escape proc
    else if not( exists( select * 
                        from sys.tables t
                        join information_schema.key_column_usage kcu on kcu.TABLE_NAME = t.name and
                            kcu.TABLE_SCHEMA = 'stage'
                        where t.[object_id] = object_id( @StageTable ) ) and
                exists( select * 
                        from sys.tables t
                        join information_schema.key_column_usage kcu on kcu.TABLE_NAME = t.name and
                            kcu.TABLE_SCHEMA = 'dbo'
                        where t.[object_id] = object_id( @DboTable ) ) )
    begin
        select @ErrMsg += 'One or both of target tables ' + @DboTable + ' and ' + @StageTable + ' is MISSING a PRIMARY KEY declaration across one or more columns. Execution ABORTED!' + @Lb + @Lb ;
        goto QuitWithFailure ;
    end
    else
    begin
        select @RetMsg += 'Parameters validated SUCCESSFULLY. Dynamic sql assignation will begin.' + @Lb + @Lb ;

            /****************************************************/
            /*  Toggle off rows that no longer exist in staging */
            /****************************************************/
        select @ToggleOffClause = 
            'with TableIdsRemainingFromYesterday as ' + @Lb + 
            -- get all Table Identitys from the target where the PK is found on the source
            ' ( select TableIdentity ' + @Lb + 
            ' from ' + @DboTable + ' as MyTarget ' + @Lb +
            -- trim last "and " off PK match
            ' inner join ' + @StageTable + ' as MySource on ' + left( pkm.PrimaryKeyMatch, len( pkm.PrimaryKeyMatch ) - 4 ) + @Lb + 
            ' where IsCurrentRow = ''Y'' ) ' + @Lb + 
            'update ' + @DboTable + ' set ' + @Lb +
            @Tab + ' Revision = Revision + 1, ' + @Lb + 
            @Tab + ' LastUpdateBy = ''' + @ProcName + ''', ' + @Lb + 
            @Tab + ' LastUpdateDateTime = ''' + convert( varchar, @ExecDateTime, 126 ) + ''', ' + @Lb + 
            @Tab + ' IsCurrentRow = ''N'' ' + @Lb + 
            'from ' + @DboTable + ' as MyTarget ' + @Lb +
            ' where MyTarget.IsCurrentRow = ''Y'' and ' + @Lb + 
-- when the PK ( and therefore the 1:1 corresponding ) is not on the Source, soft-delete it from target
            @Tab + ' MyTarget.TableIdentity not in ( select TableIdentity from TableIdsRemainingFromYesterday ) ; '
        from sysobjects so 
        cross apply
-- match on stage primary key = dbo primary key
        (
            select
                @Lb + @Tab + 'MyTarget.' + quotename( COLUMN_NAME ) + ' = MySource.' + quotename( COLUMN_NAME ) + ' and '
            from INFORMATION_SCHEMA.COLUMNS c
                where TABLE_SCHEMA = 'stage' and
                    TABLE_NAME = @TargetTable and
                    exists( select * 
                                from information_schema.key_column_usage kcu
                                where TABLE_SCHEMA = 'stage' and 
                                    TABLE_NAME = @TargetTable and
                                    COLUMN_NAME = c.COLUMN_NAME )
        ) pkm ( PrimaryKeyMatch ) ;

            /****************************************************/
            /*  Update rows that already exist AND have changed */
            /****************************************************/
        select @UpdateRowsClause = 
            'update ' + @DboTable + ' set ' + 
                    wmc.WhenMatchedClause + @Lb +
            @Tab + ' Revision = Revision + 1, ' + @Lb + 
            @Tab + ' LastUpdateBy = ''' + @ProcName + ''', ' + @Lb + 
            @Tab + ' LastUpdateDateTime = ''' + convert( varchar, @ExecDateTime, 126 ) + ''', ' + @Lb + 
            @Tab + ' IsCurrentRow = ''Y'' ' + @Lb + 
            'from ' + @DboTable + ' as MyTarget ' + @Lb +
-- trim last "and " off PK match
            ' inner join ' + @StageTable + ' as MySource on ' + left( pkm.PrimaryKeyMatch, len( pkm.PrimaryKeyMatch ) - 4 ) + @Lb + 
            ' where ' + wneoc.WhereNotEqualsOrClause + @Lb + 
            -- rows re-entering staging report that had previously left
            @Tab + ' MyTarget.IsCurrentRow != ''Y'' ; '
        from sysobjects so 
-- match on stage primary key = dbo primary key
        cross apply
            (
                select
                    @Lb + @Tab + 'MyTarget.' + quotename( COLUMN_NAME ) + ' = MySource.' + quotename( COLUMN_NAME ) + ' and '
                from INFORMATION_SCHEMA.COLUMNS c
                    where TABLE_SCHEMA = 'stage' and
                        TABLE_NAME = @TargetTable and
                        exists( select COLUMN_NAME 
                                    from information_schema.key_column_usage kcu
                                    where TABLE_SCHEMA = 'stage' and 
                                        TABLE_NAME = @TargetTable and
                                        COLUMN_NAME = c.COLUMN_NAME )
            ) pkm ( PrimaryKeyMatch ) 
-- WhenMatchedClause
        cross apply
            (
                select -- top( 10 ) -- top 10 for debugging. varchar( max ) can only display in print window to 4k chars
                    @Lb + @Tab + quotename( COLUMN_NAME ) + ' = MySource.' + quotename( COLUMN_NAME ) + ','
                from INFORMATION_SCHEMA.COLUMNS c
                where TABLE_SCHEMA = 'stage' and
                    TABLE_NAME = @TargetTable and
                    -- ignore table primary key
                    not exists( select COLUMN_NAME 
                                from information_schema.key_column_usage kcu
                                where TABLE_SCHEMA = 'stage' and 
                                    TABLE_NAME = @TargetTable and
                                    COLUMN_NAME = c.COLUMN_NAME )
                order by c.ORDINAL_POSITION
                for xml path( '' )
            ) wmc ( WhenMatchedClause ) 
-- WhereNotEqualsOrClause
        cross apply
            (
                select -- top( 10 ) 
                    @Lb + @Tab + 'MyTarget.' + quotename( COLUMN_NAME ) + ' != MySource.' + quotename( COLUMN_NAME ) + ' or'
                from INFORMATION_SCHEMA.COLUMNS c
                where TABLE_SCHEMA = 'stage' and
                    TABLE_NAME = @TargetTable and
                    -- ignore table primary key
                    not exists( select COLUMN_NAME 
                                from information_schema.key_column_usage kcu
                                where TABLE_SCHEMA = 'stage' and 
                                    TABLE_NAME = @TargetTable and
                                    COLUMN_NAME = c.COLUMN_NAME )
                order by c.ORDINAL_POSITION
                for xml path( '' )
            ) wneoc ( WhereNotEqualsOrClause ) ;

            /********************/
            /*  Insert New Rows */
            /********************/
        select @InsertRowsClause =
            'insert ' + @DboTable + @Lb + '( ' + 
            @Tab + ac.AllColumns + @Lb + 
            @Tab + ' InsertDateTime, InsertedBy, LastUpdateDateTime, LastUpdateBy ) ' + @Lb +
            ' select '+ ac.AllColumns + @Lb + 
            @Tab + '''' + convert( varchar, @ExecDateTime, 126 ) + ''', ''' + @ProcName + ''', ''' + convert( varchar, @ExecDateTime, 126 ) + ''', ''' + @ProcName + '''' + @Lb +
            ' from ' + @StageTable + @Lb +
            ' where not exists ( select 1 from ' + @StageTable + ' as MySource join ' + @DboTable + ' as MyTarget on ' + left( pkm.PrimaryKeyMatch, len( pkm.PrimaryKeyMatch ) - 4 ) + ' ) ;'
        from sysobjects so 
-- All non-metadata columns
        cross apply
            (
                select -- top( 10 ) 
                    @Lb + @Tab + quotename( COLUMN_NAME ) + ','
                from INFORMATION_SCHEMA.COLUMNS c
                where TABLE_SCHEMA = 'stage' and
                    TABLE_NAME = @TargetTable 
                order by c.ORDINAL_POSITION
                for xml path( '' )
            ) ac ( AllColumns )
        cross apply
-- match on stage primary key = dbo primary key
            (
                select
                    @Lb + @Tab + 'MyTarget.' + quotename( COLUMN_NAME ) + ' = MySource.' + quotename( COLUMN_NAME ) + ' and '
                from INFORMATION_SCHEMA.COLUMNS c
                    where TABLE_SCHEMA = 'stage' and
                        TABLE_NAME = @TargetTable and
                        exists( select COLUMN_NAME 
                                    from information_schema.key_column_usage kcu
                                    where TABLE_SCHEMA = 'stage' and 
                                        TABLE_NAME = @TargetTable and
                                        COLUMN_NAME = c.COLUMN_NAME )
            ) pkm ( PrimaryKeyMatch ) ;

            /****************/
            /*  Debug Step  */
            /****************/
        if @DebugOption is not null
        begin
            select @RetMsg += 'DebugOption was selected. Input Option of ' + isnull( nullif( @DebugOption, '' ), '{blank_string}' ) + '. Execution step will be skipped.' ;

            if charindex( '1', @ToggleOffClause ) <> 0 print @ToggleOffClause ;
            if charindex( '2', @UpdateRowsClause ) <> 0 print @UpdateRowsClause ;
            if charindex( '3', @InsertRowsClause ) <> 0 print @InsertRowsClause ;

            goto QuitWithSuccess ;
        end ;

        select @RetMsg += 'Dynamic sql assignation completed SUCCESSFULLY. Execution try will begin.' + @Lb + @Lb ;
            /********************************/
            /*  Begin Execution Try Series  */
            /********************************/
        begin try
            exec sp_executesql @ToggleOffClause ; 
            set @CountRows = @@rowcount ;
            insert @ResultArray
                ( CountRows, Step, CommandText )
            select 
                @CountRows, 'ToggleOff', @ToggleOffClause ;

            select @RetMsg += 'Toggle off step completed SUCCESSFULLY! ' + convert( varchar, @CountRows ) + ' row(s) affected. ' + @Lb + @Lb ;
        end try
        begin catch
            select @ErrMsg += error_message() + @Lb + @Lb ;
        end catch ; 

        begin try
            exec sp_executesql @UpdateRowsClause ; 
            set @CountRows = @@rowcount ;
            insert @ResultArray
                ( CountRows, Step, CommandText )
            select 
                @CountRows, 'UpdateRows', @UpdateRowsClause ;

            select @RetMsg += 'Update Rows step completed SUCCESSFULLY! ' + convert( varchar, @CountRows ) + ' row(s) affected. ' + @Lb + @Lb ;
        end try
        begin catch
            select @ErrMsg += error_message() + @Lb + @Lb ;
        end catch ; 
        
        begin try
            exec sp_executesql @InsertRowsClause ; 
            set @CountRows = @@rowcount ;
            insert @ResultArray
                ( CountRows, Step, CommandText )
            select 
                @CountRows, 'InsertRows', @InsertRowsClause ;

            select @RetMsg += 'Insert Rows step completed SUCCESSFULLY! ' + convert( varchar, @CountRows ) + ' row(s) affected. ' + @Lb + @Lb ;
        end try
        begin catch
            select @ErrMsg += error_message() + @Lb + @Lb ;
        end catch ; 

        select @RetMsg += @ProcName + N' executed SUCCESSFULLY on target table ' + @TargetTable + '. Results will be logged to [dbo].[Log].'+ @Lb + @Lb ;

    end ;

LogResults: 
    insert dbo.Log
    ( 
        Step, 
        Process, 
        CountRows, 
        Notes, 
        CommandText, 
        InsertedBy, 
        InsertDatetime, 
        LastUpdateBy, 
        LastUpdateDatetime 
    )
    select
        ra.Step,
        @ProcName,
        ra.CountRows,
        @TargetTable,
        ra.CommandText,
        @ProcName,
        @ExecDateTime,
        @ProcName,
        @ExecDateTime
    from @ResultArray ra 
    union all
    select 
        'LogResults',
        @ProcName,
        null,
        @TargetTable,
        @RetMsg,
        @ProcName,
        @ExecDateTime,
        @ProcName,
        @ExecDateTime ;

    goto QuitWithSuccess ;

-- Raiserror & return failure
QuitWithFailure:    
    insert dbo.Log
    (
        Step, 
        Process, 
        CountRows, 
        Notes, 
        ErrorMessage,
        CommandText, 
        InsertedBy, 
        InsertDatetime, 
        LastUpdateBy, 
        LastUpdateDatetime 
    )
    select
        'QuitWithFailure',
        @ProcName,
        null,
        @RetMsg,
        @ErrMsg,
        null,
        @ProcName,
        @ExecDateTime,
        @ProcName,
        @ExecDateTime ;
    print @RetMsg ;
    raiserror( @ErrMsg, 11, -1 ) ;
    return -1 ;
    goto EndSave ;

-- Print results & return success
QuitWithSuccess:
    print @RetMsg ;
    return 0 ;

EndSave:
end ;
go

if exists( select * 
            from sys.objects 
            where [object_id] = object_id( N'dbo.spInsertUpdateStageIntoProdDynamicSql' ) and 
                [type] in ( N'P' ) )
    print 'Create procedure dbo.spInsertUpdateStageIntoProdDynamicSql SUCCESSFULLY! At time ' + convert( varchar, getdate(), 126 ) ;
else
    print 'Create procedure dbo.spInsertUpdateStageIntoProdDynamicSql FAILED! At time ' + convert( varchar, getdate(), 126 ) ;
go

/*

exec dbo.spInsertUpdateStageIntoProdDynamicSql N'TestTable', '2015-09-02', null ;
select * from [dbo].[Log] ;


truncate table [dbo].[Log] ;

*/

Answer 3

I don't know how or if you can do this from SSIS, but one potential method might be to use the ALTER TABLE ... SWITCH functionality to quickly switch out the old table for with a newly created "temporary" copy.

A simple example may be helpful:

SET NOCOUNT ON;
USE tempdb;

-- Create a simple table

IF OBJECT_ID(N'dbo.T1', N'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.T1;
END

CREATE TABLE dbo.T1
(
    T1_ID int NOT NULL 
        CONSTRAINT PK_T1
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , SomeData varchar(255) NULL
);
GO

Populate some sample data into the table:

INSERT INTO dbo.T1 (SomeData)
VALUES ('asdfasdf');

GO

Use the SWITCH functionality to effectively "swap" the old table with the new table:

BEGIN TRANSACTION;
BEGIN TRY
    IF OBJECT_ID(N'dbo.T2', N'U') IS NOT NULL
    BEGIN
        DROP TABLE dbo.T2;
    END
    CREATE TABLE dbo.T2
    (
        T1_ID int NOT NULL 
            CONSTRAINT PK_T2
            PRIMARY KEY CLUSTERED
            IDENTITY(1,1)
        , SomeData varchar(255) NULL
    );
    INSERT INTO dbo.T2 (SomeData)
    VALUES ('asdfasdf1');
    TRUNCATE TABLE dbo.T1;
    ALTER TABLE dbo.T2 SWITCH TO dbo.T1;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
END CATCH
GO