在Where子句中使用用户变量的SSIS包

Question

在Where子句中使用用户变量的SSIS包

sql-serverssisscript-taskuser-variables

3

简单来说，我正在尝试获取用户输入，将其存储在变量中，然后在查询中使用该变量。

首先，我有一个脚本任务，它打开一个带有简单输入文本框和按钮的窗口。单击后，文本会被放入用户变量中，并且一个消息框弹出显示用户变量中的内容。单击消息框上的“确定”按钮会关闭消息框和原始窗口。这似乎很好用。

接下来，我有一个执行SQL任务，设置如下：

常规

ResultSet = None
ConectionType = OLE DB
Connection = localhost.DB
SQLSourceType = Direct Input
SQLStatement = [SQL代码如下]

参数映射

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

结果集-N/A

表达式-N/A

[SQL代码]

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 ( ? )

问题：我运行包，文本输入框打开。我输入我的文本，“'CN05'，'CN06'”（不含双引号），然后点击确定。弹出框显示我的输入，我点击确定。工作流继续执行SQL任务。1秒钟后，任务以绿色勾号的形式完成，没有错误。我验证了TEST.dbo.TEST已经被创建，但是它是空的。现在，如果我在SSMS中硬编码上述代码的最后部分[...,SVY.x in ('CN05'，'CN06')]，我在约4分钟内检回超过500万条记录。我困惑为什么这在SSIS中不能正常工作。有任何想法吗？

- Mark Griggs

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Troy Witthoeft · Accepted Answer

一般来说，带参数的WHERE IN子句不够灵活，往往不能按照我们预期的工作方式进行。相反地，您可以使用SSIS的动态SQL版本来实现同样的效果。创建一个字符串变量，用于存储您的大型SQL语句和用户输入的串联。然后，将Execute SQL任务连接到此变量。

例如，请创建一个名为SqlStatement的新SSIS字符串变量。在变量的属性窗口中，通过单击省略号打开表达式构建器窗口。使用此表达式构建器将您的SQL与用户输入连接起来。如下所示...

请确保将变量的属性设置为。

最后，将Execute SQL任务从直接输入更改为变量，并选择此变量作为源。

至此，您的Execute SQL任务应该已经准备好使用表达式构建器中构建的动态SQL。祝您好运！