使用 WHERE 子句“IN"创建 SSIS

使用带有语句的 SQL 命令:SELECT * FROM Oracle.Table1 WHERE Col1 IN (?) - where ?是一个对象变量(来自 SQL Server 语句的结果集 1 col)当我尝试选择参数"时，我收到一条错误消息.

Used SQL Command with statement: SELECT * FROM Oracle.Table1 WHERE Col1 IN (?) - where ? is an object variable (resultset 1 col from a SQL Server statement) When I try to select "parameter", I get an error message.

使用来自变量的 SQL 命令和变量中的表达式:"SELECT * FROM Oracle.Table1 WHERE Col1 IN (" + @[User::Obj_values] + ")"

Used SQL Command from Variable with an expression in a variable: "SELECT * FROM Oracle.Table1 WHERE Col1 IN (" + @[User::Obj_values] + ")"

仅供参考:我只有 Oracle DB 的读取权限 :(

FYI: I only have read permission to the Oracle DB :(

热门问题

不，如果 SSIS 变量的类型为 Object，则您无法在表达式语言中执行任何操作

No, you can do nothing in the expression language if the SSIS variable is of type Object

是的，foreach 方法需要对数组中的每个元素进行单独的查询.

Yes, a foreach approach would entail a separate query per element in the array.

您尝试过的问题

参数替换不适用于值列表 - 正如您所观察到的

Parameter substitution does not work with a list of values - as you've observed

在中支持Oracle 因此这种方法可以工作，但您必须 a) 使用字符串数据类型 b) 滚动您自己的逻辑来构建分隔列表.在 C# 任务中，它可能像 string.Join(",", Dts.Variables["User::Obj_values"].Value.ToArray()); 一样微不足道，但可能不取决于您如何填充对象数组的基础巫术

In is supported in Oracle so this approach will work but you must a) use a string data type b) roll your own logic to build the delimited list. In a C# task, it could be as trivial as string.Join(",", Dts.Variables["User::Obj_values"].Value.ToArray()); but may not depending on the underlying voodoo of how you've populated the object array

其他方法

根据数量以及 SQL Server 中的键是否可以在 Oracle 中匹配(0 到 1)或(0 到多个)，您可以将数据流编写为

Other approaches

Depending on volume and whether a key in SQL Server could match (0 to 1) or (0 to many) in Oracle, you could write your Data flow as

• OLE DB 源(SQL Server 查询)
• 查找组件 (Oracle)
• 无论目的地

在这种方法中，您从过滤的值列表开始，然后将其与 Oracle 中的参考表进行比较.如果参考表是自成立以来纽约证券交易所的所有交易"是的，这行不通，但如果它是我的所有客户"那么您可能可以将该数据带入查找组件.

In this approach, you start with a filtered list of values and then compare it to your reference table in Oracle. If the reference table is "All transactions on the NYSE since inception" yeah, that's not going to work but if it's "all my customers" then you can probably bring that data into the lookup component.

您可以尝试通过在部分缓存模式下使用查找来拆分差异.在这种方法中，它不是将整个目标表拉入内存，而是为每个元素触发查询，除非它已经看到了键，在这种情况下它会重新使用本地缓存值.在这种方法和 foreach 枚举器方法之间，我倾向于通过查找组件触发查询，因为您不必为数据流任务的每次启动支付验证开销.鉴于这是 Oracle，这假定您有可用的 OLE DB 连接管理器.否则，您将不得不使用缓存连接管理器，顾名思义，它只能缓存预定义的数据.

You could try and split the difference by using the Lookup in a Partial cache mode. In this approach, instead of pulling the entire target table into memory, it fires a query for each element unless it has already seen the keys in which case it re-uses a local cached value. Between this approach and a foreach Enumerator approach, I'd favor firing the queries off via the lookup component as you would not have to pay the validation overhead for each start of the data flow task. Given this is Oracle, this presumes you have an OLE DB connection manager available. Otherwise, you'd have to work with the Cache Connection Manager and that, as the name implies, can only cache pre-defined data.