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:
For demo purpose I will be using the master database here.
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 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: