简单来说,我正在尝试获取用户输入,将其存储在变量中,然后在查询中使用该变量。
首先,我有一个脚本任务,它打开一个带有简单输入文本框和按钮的窗口。单击后,文本会被放入用户变量中,并且一个消息框弹出显示用户变量中的内容。单击消息框上的“确定”按钮会关闭消息框和原始窗口。这似乎很好用。
接下来,我有一个执行SQL任务,设置如下:
常规
表达式-N/A
[SQL代码]
问题:我运行包,文本输入框打开。我输入我的文本,“'CN05','CN06'”(不含双引号),然后点击确定。弹出框显示我的输入,我点击确定。工作流继续执行SQL任务。1秒钟后,任务以绿色勾号的形式完成,没有错误。我验证了TEST.dbo.TEST已经被创建,但是它是空的。 现在,如果我在SSMS中硬编码上述代码的最后部分[...,SVY.x in ('CN05','CN06')],我在约4分钟内检回超过500万条记录。我困惑为什么这在SSIS中不能正常工作。有任何想法吗?
首先,我有一个脚本任务,它打开一个带有简单输入文本框和按钮的窗口。单击后,文本会被放入用户变量中,并且一个消息框弹出显示用户变量中的内容。单击消息框上的“确定”按钮会关闭消息框和原始窗口。这似乎很好用。
接下来,我有一个执行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
[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中不能正常工作。有任何想法吗?
'
或;
字符会更好。 - Troy Witthoeft