There are some things that we do in one SQL Server tool, but can't seem to figure out how to do in another. One of those for you might be the LIKE keyword in T-SQL. LIKE is fairly powerful in T-SQL - it does rudimentary regular expressions. How do you do that in SSIS? Well, you can't get too fancy, but here are some basics.
Columns That Contain a Value
This applies to cases where your T-SQL query might include a clause like this:
col LIKE '%value%'
The SSIS expression equivalent involves the function FINDSTRING. FINDSTRING looks in any string expression (including a column) for an occurrence of a particular substring, and which occurrence to look for.
The equivalent expression in SSIS to the example above is:
FINDSTRING(col, "value", 1) > 0
Columns That Start With a Value
This applies to cases where your T-SQL query might include a clause like this (where X is a number):
col LIKE 'value%'
or
LEFT(col, X) = 'value'
This is a fairly rudimentary case - and there are multiple methods for developing an equivalent SSIS expression with FINDSTRING, or LEFT, such as:
FINDSTRING(col, "value", 1) == 1
or
LEFT(col, X) == "value"
Columns That End With a Value
This one's a little trickier (in SSIS2008R2 and below), and has prompted a few questions on the MSDN SSIS forums. The T-SQL case would look something like:
RIGHT(col, X) = 'value'
There's a LEFT in SSIS' expression language... but no RIGHT prior to SSIS 2012, so what can you do? How about using REVERSE and LEFT, like this:
REVERSE(LEFT(REVERSE(col), X)) == "value"
More Complicated Demands?
T-SQL's LIKE can service more complicated pattern searching - look for future posts where I'll try to investigate some. If you have any particular requests, post a comment...