Case structures in SSIS

Every time I need to do a Case-iish thing in SSIS I can’t remember the exact syntax although I already used it several times. So I end up grabbing a manual, looking trough my old notes or projects or need to google with the right keywords to have that “Now I will never forget” thought again. But alas last week I was again struggling with the syntax.

I was already thinking a while about starting a blog, but kept asking myself questions  like “Do I have anything useful to post?” and “Why should I post variations on articles already on the web?”. And my answer is: “I will blog just for myself to better understand what I am doing and to find back the stuff that matters to me.” So everybody is welcome to read, comment, share and if you don’t find it interesting …

So here I go, my first blog post:

I start with a table containing some values and we want to put the data into another table where the values are replaced with other information.

CREATE TABLE CaseSource (
ID int IDENTITY (1,1)
,Value int
);
GO

 I fill the table with an id and a value from 0 to 10.

DECLARE @i INT = 0
WHILE @i < 11
BEGIN
INSERT INTO CaseSource (Value) VALUES (@i)
SET @i = @i + 1
END

 Suggest I need to “visualise” the numbers where under 3 it’s code red, between 3 and 5 is orange and above 5 is green we can do this easily in T-SQL with a case statement into a destination table.

SELECT ID
,(CASE
WHEN Value < 3 THEN 'red'
WHEN Value >2 and Value < 6 THEN 'orange'
ELSE 'green'
END) AS Value
INTO CaseDest
FROM CaseSource

Off course I can rewrite the query that get’s the data or use a SQL Task to perform this simple action but I want to do this in SSIS because I come across scenario’s where I need to do this. So I open a project, create a package, add a Data Flow Task and  use a Data Flow Source to get my data as is from the table.

The destination table is created with the following script:

CREATE TABLE [dbo].[CaseDest](
[ID] [int],
[Value] [varchar](25)
)

A Derived Column Transformation and a Data Flow Destination pointing to the destination table is also dropped in the Data Flow Task. The Data Flow Task now looks like this:

Off course if I run the package now, there will be no transformations so it’s time to edit the Derived column Transformation. I choose ValueStr as Derived Column Name and will because I’m changing the Data Type as well and start editing the Expression. Note that in SSIS it’s important to use the correct case and that text needs to be in “double quotes”. So when possible I drag and drop Column names and functions into the Expression box. Validating the expression is easy, when I select something else in the Editor the expression turns red in case of problems.



The syntax for the expression is:

({Boolean Expression}?{True Part}:{False Part})

So for my Case scenario I do first a check if the value is below 3, if True I change to value to “red”, If False I need to create a new Case to see if the value is under 6, if True I can display “orange” and if False it will be “green”. So my Expression looks like this:

[Value] < 3?"red" : ( [Value] < 6 ? "orange" : "green" )

The Data Type is changed to string [DT_STR] with a Length of 25. The Derived Column Transformation Editor looks like this:

So far so good, but when I run the package now I will still get the numbers instead of the new values in my table. The last thing to do is to edit my Destination and change the mapping to use the ValueStr Input column instead of the Value column. My Destination Editor now looks like this:

And finally my package is ready to run and when I do a SELECT * on my table I get the following result set:

 

Advertisements
%d bloggers like this: