SSIS Package with User Variable in Where Clause

Question

Simply put, I'm trying to take user input, store it in a variable, and then use that variable in a query.

First, I have a Script Task that opens a window with a simple input text box and a button. On click, the text gets put into a user variable and a message box pops up to display what is in the user variable. Clicking Ok on the message box closes both the message box and original window. This seems to work fine.

Next, I have an Execute SQL Task. Settings are as follows:

General

• ResultSet = None
• ConectionType = OLE DB
• Connection = localhost.DB
• SQLSourceType = Direct Input
• SQLStatement = [SQL Code displayed below]

Parameter Mapping

• Variable Name = User::VarName
• Direction = Input
• DataType = VARCHAR
• Parameter Name = 0
• Parameter Size = 65535

Result Set - N/A

Expressions - N/A

[SQL CODE]

if object_id('TEST.dbo.TEST','U') is not null
    drop table TEST.dbo.TEST;


SELECT
    coalesce(FSC.a, format(cast(SVY.b as int),'000')) as ab,
    SVY.c,
    PP.d,
    SV1.e,
    PP.f,
    PP.g,
    cast(PP.g as float) AS g,
    SV1.h,
    SV1.i,
    SVY.j,
    SVY.k,
    format(cast(SVY.l as int), '00000') as l,
    CAST(SVY.m AS float) AS m,
    SVY.n,
    SV1.o,
    SVY.p,

    cast(PID.q as float) as q,
    cast(PID.r as float) as r,
    cast(PID.s as float) as s,
    cast(PID.t as float) as t,

    PID.u as u,
    PID.v as v,

    PP.w,

    CAL_B.x as x,
    CAL_B.y as y,
    CAL_B.z as z,

    CAL_R.aa as aa,
    CAL_R.bb as bb,
    CAL_R.cc as cc

into TEST.dbo.TEST

FROM AAA.dbo.AAA PP

INNER JOIN BBB.dbo.BBB PPC
    ON PP.x = PPC.x
    AND cast(PP.x as date) = cast(PPC.x as date)

RIGHT OUTER JOIN CCC.dbo.CCC SVY
    ON PPC.x = SVY.x
    AND cast(PPC.x as date) = cast(SVY.x as date)

LEFT OUTER JOIN DDD.dbo.DDD FSC
    ON SVY.x = FSC.x
    AND cast(SVY.x as date) = cast(FSC.x as date)

LEFT OUTER JOIN EEE.dbo.EEE SV1
    ON SVY.x = SV1.x
    AND SVY.x = SV1.x
    AND SVY.x = SV1.x
    AND SVY.x = SV1.x
    AND cast(SVY.x as date) = cast(SV1.x as date)
    AND PPC.x = SV1.x
    AND cast(PPC.x as date) = cast(SV1.x as date)

INNER JOIN FFF.dbo.FFF RLS
    ON SV1.x = RLS.x
    AND SV1.x = RLS.x
    AND cast(SV1.x as date) = cast(RLS.x as date)
    AND SVY.x = RLS.x
    AND SVY.x = RLS.x
    AND SVY.x = RLS.x
    AND cast(SVY.x as date) = cast(RLS.x as date)

LEFT OUTER JOIN GGG.dbo.GGG PID
    ON PP.x = PID.x
    AND coalesce(FSC.x, format(cast(SVY.x as int),'000')) = PID.x

LEFT OUTER JOIN HHH.dbo.HHH CAL_B
    ON cast('20' + SUBSTRING(RLS.x,4,2) + '-' + SUBSTRING(RLS.x,6,2) + '-' + SUBSTRING(RLS.x,8,2) as date) = CAL_B.x
    AND CAL_B.x = 1

LEFT OUTER JOIN III.dbo.III CAL_R
    ON cast('20' + SUBSTRING(RLS.x,4,2) + '-' + SUBSTRING(RLS.x,6,2) + '-' + SUBSTRING(RLS.x,8,2) as date) = CAL_R.x
    AND CAL_R.x = 1

where
    cast(SVY.x as date) = (select cast(max(x) as date) from JJJ.dbo.JJJ)
    and
    PP.x is not null
    and
    SVY.x in ( ? )

THE ISSUE: I run the package. Text input box opens. I put in my text, " 'CN05','CN06' " (without double quotes), I click OK. Box pops up showing me my input. I click OK. Workflow moves on to Execute SQL Task. In 1 second the task completes with a green check, no errors. I verify that TEST.dbo.TEST has been created but it is empty. Now, if I run the above code in SSMS hardcoding the last bit [...SVY.x in ('CN05','CN06')], I pull back over 5 million records in about 4 minutes. I am stumped as to why this isn't working in SSIS. Any ideas out there?

Answer 1

In general, parameterized WHERE IN clauses don't play nice. They never work the way we think they should. Instead, you can use SSIS' version of dynamic SQL to achieve the same thing. Create a string variable to store the concatenation of your larger SQL statement with your user input. Then, wire your Execute SQL Task to use this variable.

For example, please create a new SSIS string variable called SqlStatement. In the properties window for the variable, open the expression builder window by clicking the ellipses. Use this expression builder to concatenate your SQL with the user input. Sample below ...

Be sure to set the EvaluateAsExpression property of the variable to True.

Lastly, change your Execute SQL Task over from Direct Input to Variable and select this variable as the source.

All set. Your Execute SQL Task should now be wired to use the dynamic SQL you built inside of the expression builder. Good luck!