SSIS Expressions: How do I use form a 'LIKE' conditional expression

Question

SSIS Expressions: How do I use form a 'LIKE' conditional expression

dave-dj

SSChampion

Points: 11297
More actions
March 14, 2011 at 11:10 am

#389040

Quite simply I have the following T-SQL
CASE
WHEN[to_location] like 'ROBOTIN%' THEN 'AVL-AVL Load'
ELSE'UNK-Unkown'
ENDasload_descr
.
I need to use the LIKE operator as there can be 1 --> 100 instances (i.e. ROBOTIN001, ROBOTIN002)
What I want to do is replicate this is an SSIS Derived Column expression. I can only really see operators that effectively give a yes/no answer.
How do I do create a conditional statement that uses a "LIKE" operator??
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

You can achieve this by using the Derived Column Component to add a blank column to your data set and then using a "Transformation Script Component" to populate the new column based on your conditional logic.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

dave-dj SSChampion Points: 11297 More actions · Answer 2

ok thanks, I'll look into that.

Seems stupid that MS introduced the SSIS expression language instead of using T-SQL or vb or c#. I guess they had there reasons, but it's frustrating to think that such a simple T-SQL CASE statement with a LIKE operator has to be so complicated.

Are there any good books / blog post that explain how to do some of these things.

_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

opc.three (3/14/2011)
You can achieve this by using the Derived Column Component to add a blank column to your data set and then using a "Transformation Script Component" to populate the new column based on your conditional logic.

You don't need the derived column to add a blank column. You can do this in the script component itself.

Regarding the original question, you can implement the functionality you want with the following expression:

SUBSTRING(to_location,1,7) == "ROBOTIN" ? "AVL-AVL Load" : "UNK-Unkown"

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

The expression builder SSIS offers are just abstractions of .NET code. SSIS expressions were not meant to handle everything...but they do handle most inline data manipulation needs. The Script Task component offers the full breadth of the .NET framework for you to work any magic necessary. In SSIS 2005 your only chioce is VB.NET for script language. I was very happy when MS decided to support C# as a script Task language in SSIS 2008.

SQL Share[/url] might be a good place for you to start since it is visual which lends itself well to learning SSIS. I like SQL Share for picking up tips since it has tons of videos with bite-size bits of useful techniques. Here is a page that should help you browsing SSIS: http://www.sqlshare.com/Search.aspx?terms=ssis

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

dave-dj SSChampion Points: 11297 More actions · Answer 5

thanks for the reference

_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

dave-dj SSChampion Points: 11297 More actions · Answer 6

opc.three (3/14/2011)
You can achieve this by using the Derived Column Component to add a blank column to your data set and then using a "Transformation Script Component" to populate the new column based on your conditional logic.

opc.three & Koen Verbeeck

thanks for your suggestions, I went down the route of a script task transform and found this to be bar far the simpler option, using an if else statement to simply add a column to the data row as it passes through the transformation. :w00t:

thanks

_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)