SSIS Basics: Conditional splits

SSIS is probably one of the most non-intuitive GUIs I've ever seen from Microsoft, and it appears to be broken in many interesting ways. Despite this frustration, the functionality it provides is indispensable. This page will document how to perform a simple conditional split.

Prerequisites

You need to install SQL Server business Intellegence Development Studio in order to develop SSIS packages. There is no support at this time in Visual Studio 2010 for SSIS package development. This tutorial will assume you are familiar enough with Visual Studio to be able to set up a solution and add a new package to that solution. I will also assume that you are smart enough to figure out how to create connection strings for your SSIS objects.

How to Perform a Conditional Split

In this example, I wanted to check a log table I created for my application in the database for a certain log entry that occurred overnight. Generalized, I want to use SQL to test something in the database and return a value of TRUE or FALSE so I can take some kind of action. This process consists of the following steps:

1. Set up an 'Execute SQL Task' that will return a boolean value. This is the decision that has to be made.
2. Pass that value to an SSIS variable
3. Set up some actions to occur based on the variable's value
4. Set up the conditional statements to test the value and execute our actions

Set up the Decision object

1. Write a Boolean SQL query that will return a conditional result, such as:
   

   declare @date char(12), @SuccessState bit
   set @date=(select convert(datetime, convert(char(12), getdate()) ))
   
   if exists(
   select *
   from [DatabaseName].dbo.eventlog 
   where [Timestamp] > @date
   and EventStr='Users table filled successfully'
   )
   set @SuccessState=1
   else
   select @SuccessState=0
   
   select @SuccessState SuccessState

   Pay special attention to the bolded lines. The most important part of getting this right is to name your result column. Unless you name this column, SSIS will not be able to find your result.

2. Drag an 'Execute SQL task' object from the toolbox onto your design workspace.
3. Right-Click and Click 'edit'.
4. Find the 'SQLStatement' and 'ResultSet' fields under the 'General' category.
5. Change the 'ResultSet' from None to Single Row
6. Copy your SQL query into 'SQLStatement'
7. Click 'OK' and make sure your task is working so far.

Passing variables from SQL to SSIS

OK, so now you've got your SQL query executing and returning a TRUE/FALSE to SSIS. Now we need to capture that value and use it to make a conditional split.

1. On the 'Variables' tab of the Visual Studio Workspace, add a new variable of datatype 'boolean'. The scope MUST be set to the entire package. if you see a scope such as 'Execute SQL Task', delete the variable, unselect the EST object and re-add the variable.
2. Right-click on your decision object and click 'edit'
3. In the left-hand column, Click on 'Result Set.' This should change the right-hand pane to an empty window with two column headings.
4. Click the 'Add' button at the bottom.
5. In your SQL query earlier, I cautioned you to name your result column. Copy your result column name into the field 'Result Name' here.
6. Under 'variable name', select the variable you created in Step 1.
7. Click 'OK'

You should now be able to execute the task. If you've made a mistake the task execution will fail. Typically the issue will be a mis-matched datatype or a mis-matched result column / result name pair.

Set up some actions to occur based on the variable's value

I know this sounds stupid, but you have to write the actions to be taken BEFORE you can write your conditional split. This is because you 'connect' these actions to your decision object using the design surface. Once you have at least two of these set up, your ready to create your conditional split.

Set up the conditional split

1. Connect your decision object to your actions using the design surface
2. Right-click on the arrow and click 'edit'
3. change 'Evaluation operation' to Expression
4. In the expression field, use the following syntax:
   @[variable_name]==[condition] (@Success==TRUE for example)
5. Click 'OK'

Troubleshooting

If your package is failing execution or not giving you the right execution path:

• Check the scope of the SSIS variable. The scope should match the package name
• Check to make sure you've included a column name for your result
• Check that the column name matches the result name
• Check the syntax of your conditional statement
• Check that your datatypes match from SSIS to SQL