How to Perform a "Like" Statement in a Ssis Expression

Is it possible to perform a LIKE statement in a SSIS Expression?

I believe you'll want to use the FINDSTRING function.

FINDSTRING(character_expression, searchstring, occurrence)

...

FINDSTRING returns null if either character_expression or searchstring
are null.

Using SSIS Parameters in LIKE Query

We can use ? and map it to string data type .

IE
SELECT [TeamMembers] FROM tblTeams WHERE [TeamMembers]like ? + '%'

As ? is automatically converted in proper data format.

Can I use a 'LIKE' statement in a SSIS job to check if filename exists?

Looks like your code is not expanding the value of @[User::FileNameCheck]. Try:

"DECLARE @FileName VARCHAR (100)
SET @FileName =  REPLACE(REPLACE('" + @[User::FileNameCheck] + "','.txt',''),'.dat','')  +'_flat.txt' 

IF EXISTS (SELECT 1
            FROM [TOC].[PA_STAGE]
            WHERE FileName = @FileName)
    BEGIN
        SELECT 0 AS LoadStatusFlag
END
ELSE
BEGIN
SELECT 1 AS LoadStatusFlag
END"

SSIS expression similar to case statement

Give this a shot:

FINDSTRING(Status,"Open",1) > 0 ? 0 : (FINDSTRING(Status,"Won",1) > 0 ? 1 : (FINDSTRING(Status,"Lost",1) > 0 ? 2 : 3))

Complex Case Statement in SSIS Expression

Sure, it's just ugly.

(ISNULL( @adate) ==TRUE && @bdate > DATEADD( "DAY", 120, @cdate ))? "ABC" : (ISNULL( @adate) ==FALSE && @bdate > @adate)? "CVA" : ""

I declared the column names as variables to test the syntax. Remove the @ symbols for your version.

Here is the relevant MS documentation: http://msdn.microsoft.com/en-us/library/ms141680.aspx

And many examples:http://www.sqlchick.com/entries/2011/6/8/nested-conditional-operators-in-an-ssis-derived-column.html

witing a case statement in SSIS

You have to use FINDSTRING() function with nested conditional operators to achieve that:

FINDSTRING([Report Title],"Norwich",1) > 0 ? "Norwich" : (
FINDSTRING([Report Title],"Ipswich",1) > 0 ? "Ipswich" : (
FINDSTRING([Report Title],"Cambridge",1) > 0 ? "Cambridge" : "NA"))

References

• Nested Conditional Operators in an SSIS Derived Column
• FINDSTRING (SSIS Expression)

---

Related Topics