OLE DB 源中Where 子句中参数乘以负值的问题
我在 OLE DB 源中有此查询，使用其中的参数：
select *
from A
where A.A_DATE >= DATEADD(d,-1*(CAST(? as int)),GETDATE())
使用参数 X。对于示例案例，我使用 X = 1。如果我在 SQL Server Management Studio 中运行上述查询，它会返回一行（正确的行）。但是当我在 SSIS 包内运行它时，它不会返回任何行（这是不正确的）。当删除 -1*xxx 并直接使用 CAST(? as int) （查询如下所示:)
select *
from A
where A.A_DATE >= DATEADD(d,CAST(? as int),GETDATE())
并将 X 值设置为 -1 时，它会显示正确的结果（返回一行）。 OLE DB 源查询中参数乘以负值是否有问题？
更新1： 第二个查询似乎还有另一个问题，就好像我将值更改为 1 一样，我仍然得到行结果（当它不应该得到时）。有什么解决办法吗？？
更新2： 看来使用的强制转换解决方案也有缺陷，因为它也没有返回正确的值。当我应该只返回 1 行时，我不断返回 2 行
似乎上面的查询在 SSIS 中也有缺陷，所以我决定使用这个查询，所以我不必使用 CAST:
"select *
from A
where A.A_DATE >= DATEADD(d," + @[User::Y] + ",GETDATE())"
并将其放入变量中（让我们将其称为 Src_Query），然后将上面的字符串计算为表达式。我还使用了 String 数据类型的变量 Y，而不是之前使用的 Int32 数据类型的 X，并将其转换为负值，我只是使用 Script Task 来处理它。然后在 OLE DB 源中我使用了“来自变量的 SQL 命令”选项。我运行该包，查询返回了正确的结果。它对于我在子查询中使用的变量也很有用。
上述解决方案的问题是：在我的项目中，我有一些包含超过 4000 个字符的源查询，而 SSIS 不允许在表达式生成器中处理超过 4000 个字符。仍在寻找解决此问题的方法。
更新 1：我已经解决了超过 4000 个字符长的查询的问题，方法是在数据检索后将 where 子句放在单独的条件拆分中，并且我仍然可以使用 Int32 类型的变量。
这是条件分割使用的表达式，OLE DB Source 中的查询不再具有与日期相关的 where 子句：
A_DATE >= DATEADD("d",@[User::X],(DT_DBDATE)GETDATE())
不过想知道它会如何影响性能，这很重要吗？
I've this query in my OLE DB Source, using a parameter inside it:
select *
from A
where A.A_DATE >= DATEADD(d,-1*(CAST(? as int)),GETDATE())
with parameter X used. For sample case, I use X = 1. If I run the above query in the SQL Server Management Studio, it returns a row (the correct one). But when I run it inside the SSIS package it doesn't return any row (which is incorrect). When remove the -1*xxx and go straight with the CAST(? as int) (the query looks like this:)
select *
from A
where A.A_DATE >= DATEADD(d,CAST(? as int),GETDATE())
and set the X value to -1, it shows the correct result (a row returned). Is there something wrong with the parameter multiplication with a negative value inside an OLE DB Source query?
update 1:
It seems there is another problem with the 2nd query, as if I change the value to 1, I still get row results (when it shouldn't). Any solutions??
update 2:
it seems the cast solution is used is also flawed, since it doesn't return the correct value either. I kept getting 2 rows returned when I should have only 1 row returned
Seems the query above is also flawed in SSIS, so I decided to use this query, so I don't have to use CAST:
"select *
from A
where A.A_DATE >= DATEADD(d," + @[User::Y] + ",GETDATE())"
and put it inside a variable (let's called it Src_Query) and then evaluate the string above as an Expression. I also used variable Y with String data type instead of X with data type Int32 I used previously, and to turn it to negative value I just used Script Task to deal with it. Then in the OLE DB Source I used the "SQL Command from variable" option. I run the package, and the query returned the correct result. It's also useful for variable that I used inside sub-queries.
The problem with the above solution: In my project I have some source query that has more than 4000 chars, and SSIS doesn't allow more than 4000 chars processed in the Expression Builder. Still searching a way to go around this problem.
Update 1: I've workaround my problem for more than 4000 chars-long query, by putting the where clause in a separate Conditional Split after the data retrieval, and I still can use my Int32-typed variable.
This is the expression that the conditional split used, with the query in OLE DB Source no longer has the where clause related to the date:
A_DATE >= DATEADD("d",@[User::X],(DT_DBDATE)GETDATE())
Wonder how it will affect the performance though, is it significant?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论