如何在 SSIS 2008 中创建动态 IN 查询? [英] How to create a dynamic IN query in SSIS 2008?

问题描述

我有一个变量@csv，它保存一个逗号分隔的值，例如:-一种-a,b-a,b,c

I have a variable @csv which hold a comma separated value such as: -a -a,b -a,b,c

我需要在数据流中的 OLE DB 源中的查询中传递它以创建查询，例如:

I need to pass it in a query in my OLE DB source in a data flow to create a query such as:

SELECT COUNT(1) FROM table WHERE col1 IN @csv

SELECT COUNT(1) FROM table WHERE col1 IN @csv

所以如果@csv="a,b" 那么在内部它应该解析为

So if @csv="a,b" then internally it should resolve into

SELECT COUNT(1) FROM table WHERE col1 IN 'a','b'

SELECT COUNT(1) FROM table WHERE col1 IN 'a','b'

如何在 SSIS 2008 中最好地实现这一点?我可以避免脚本组件创建动态查询并将其存储在变量中吗?

How can this be best achieved in SSIS 2008? Can I avoid the script component to create a dynamic query and storing it in a variable?

推荐答案

您可以创建一个变量来存储查询并使用表达式组合其值，例如:

You can create a variable to store the query and compose its value using an expression, like:

在此处输入图片描述

变量列表:

选项1:如果使用OLE DB，选择SQL Command from variable并绑定变量@sqlQuery:

Option 1: In case of using OLE DB, select SQL Command from variable and bind the variable @sqlQuery:

选项 2:如果使用 ADO.NET，转到数据流任务的属性并展开 Expressions 并将 ADO.NET Source > SqlCommand 与变量绑定@sqlQuery + 确保 ADO.NET Source >数据访问方式是一个SQL Command:

Option 2: In case of using ADO.NET, Go to properties of Data Flow Task and expand Expressions and bind the ADO.NET Source > SqlCommand with the variable @sqlQuery + Make sure that ADO.NET Source > Data access mode is a SQL Command:

选项 3:在使用 Execute SQL Task 的情况下，展开 Expressions 并将 SqlStatementSource 与变量 @sqlQuery 绑定 + 确保SQL Source Type 是一个 直接输入:

Option 3: In case of using Execute SQL Task, expand Expressions and bind the SqlStatementSource with the variable @sqlQuery + Make sure that SQL Source Type is a Direct Input:

这篇关于如何在 SSIS 2008 中创建动态 IN 查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！