Implement conditional (IF THEN ELSE) logic based on a SQL Server Store Procedure result in SSIS 2012

There are many ways you could implement conditional (IF THEN ELSE) logic based on a SQL Server Store Procedure result in SSIS, here is just one way:

Note

For demo purpose I will be using the master database here.

Stored Procedure

Create sproc on master database

if  object_id('dbo.ColumnExists') is not null
begin
    drop procedure dbo.ColumnExists
end
go

-- Determines if the given column exists in the given table.
create procedure dbo.ColumnExists
    @TableName varchar(128), -- Full table name, like dbo.MyTable
    @ColumnName varchar(128)
as
begin
    set nocount on
    
    declare @ColumnExistsResult bit = 0

    -- Check if table exists.
    if exists (select top 1 1 from sys.objects where Object_ID = Object_ID(@TableName)) 
    begin 
        -- Check if column exists in table.
        if exists (select top 1 1 from sys.columns where name = @ColumnName and Object_ID = Object_ID(@TableName)) 
        begin 
            set @ColumnExistsResult = 1
        end
    end

    -- Return single row to SSIS.
    select @ColumnExistsResult as ColumnExistsResult -- This name should be used as ResultSet name in 
end
go

Add connection

Add package variables

For demo purposes I entered a default value for TableName (“dbo.spt_monitor”) and ColumnName (“lastrun”).

Add Execute SQL Task

Change Connection to the name of connection created in the previous step.

Change ResultSet to “Singel row”

Change SQLSourceType to “Direct input”, so the task will use the t-sql query in the property “SQL Statement”

Change SQLStatement to “exec dbo.ColunExists ?, ?”

Note multiple parameters should be separated by “,”.

Add parameter mapping

De parameters are linked to the question marks in the SQL Statement.

The first “?” in exec dbo.ColumnExists will be linked to Parameter Name “0” on the Parameter Mapping page.

Note on Date Type (data type “DATE” is datetime in t-sql, data type VARIANT_BOOL” is t-sql data type bit).

Setting Result Set

On the Result Set page you can map the column names from the stored procedure call to SSIS package variables.

Setting the values for the precedence constraints

If you want a data flow to be executed, when the “Execute SQL Task” fails or the given column does not exists, you should set the precedence constraint as follow:

If you want a data flow to be executed, when the “Execute SQL Task” succeeds and the given column exists, you should set the precedence constraint as follow:

The end result should look like

When we execute the package and the column exists the debugger should be paused on the left data flow:

When the column does not exist the debugger should be paused at the right dataflow:

When the “execute sql task” fails the debugger will be paused at the left dataflow:

Leave a Reply