SSIS - 使用 Attunity Oracle 数据源在 Oracle 查询中使用参数 [英] SSIS - Using parameters in Oracle Query using Attunity Oracle Datasource

问题描述

我在 SSIS 中使用 Attunity Oracle 连接器连接到远程 Oracle 服务器.

I am using the Attunity Oracle connector in SSIS to connect to a remote Oracle Server.

在我的 SSIS 包中，我需要连接到 Oracle 数据库以根据日期时间参数获取数据.

In my SSIS package, I need to connect to an Oracle database to fetch the data based on datetime parameters.

我按照此处的建议编写了SELECT 带参数的查询:

I followed the suggestions here to write a SELECT query with a parameter:

1. 创建了一个包变量
2. 将变量设置为表达式真
3. 将查询和参数一起放入表达式中作为不同的包变量
4. 将[Oracle Source].[SqlCommand]在Data Flow中的表达式设置为打包变量(包含查询作为表达式)

1. Created a package variable
2. Set the variable to evaluate as expression true
3. Put the query in expression along with parameter as a different package variable
4. Set the expression for [Oracle Source].[SqlCommand] at Data Flow to package variable (containing the query as expression)

到这里我已经很好了，但是如果您在数据流中为 [Oracle Source].[SqlCommand] 设置表达式，那么我该怎么做 Query在数据流任务中的Oracle Source"中设置?如何获取输出列并执行转换?

I am good up to here, but if you are setting an expression for [Oracle Source].[SqlCommand] at the Data Flow, then what Query do I set in the "Oracle Source" inside the Data Flow task? How do I get the output columns and perform transformations?

在设置有效的 Oracle 数据源之前，我无法执行包.

I am not able to execute the package until I set a valid Oracle data source.

每个建议都说在数据流中设置 [Oracle Source].[SqlCommand] 属性，但没有人提到如何配置 Oracle 源.我在这里遗漏了什么吗?

Every recommendation says to set the [Oracle Source].[SqlCommand] property at the Data Flow, but nobody mentions how to configure the Oracle source. Am I missing something here?

更新 (2014/02/18) -

根据@billinkc 的评论，我用非参数查询创建了数据源，并在数据流中添加了表达式.当我执行包时，数据源内的查询更改为包变量表达式中的任何内容，但它引发错误:

Based on comments by @billinkc, I created the data source with non-parameter query and added the expression at the data flow. When I execute the package, the query inside the data source changed to whatever is there in my package variable expression but it throws an error:

遇到 OCI 错误.ORA-00936: 缺少表达式

OCI error encountered. ORA-00936: missing expression

这是我的查询的 WHERE 子句，带有变量时间戳 -

Here is my WHERE clause of the query, with the variable timestamp -

其中 SL.RECEIVED_DATE = TO_DATE( @[User::Last_Run_Timestamp] , 'dd/mon/yyyy HH24:MI:SS')

推荐答案

要使用 Attunity Oracle 数据源进行参数化，您需要先设置元数据.这通常是通过仅使用未参数化的查询作为源来完成的.然后，在控制流中，在数据流的表达式上，您将在 SSIS 变量中作为源.

To parameterize with Attunity Oracle data source, you need to get your metadata set first. This is usually done by just using the unparameterized query as the source. Then, in the Control Flow, on the Data Flow's Expressions you will sub in the SSIS Variable as a source.

重要的是将您的 SSIS 变量设置为评估为表达式"设置为 true，然后您的公式必须正确创建.与 PowerShell 不同，令牌不会在字符串中替换.相反，您需要使用经典的字符串连接技术.以下演示将变量 @[User::Last_Run_Timestamp] 转换为一个字符串，该字符串允许我通过 + 与过滤器的其余部分连接.

It is important that your SSIS Variable be set with Evaluate as Expression set to true and then your formula must be created correctly. Unlike a PowerShell, the tokens are not replaced within in a string. Instead, you'll need to use classic string concatenation techniques. The following demonstrates casting the Variable @[User::Last_Run_Timestamp] to a string which allows me to concatenate, via +, with the rest of my filter.

"SELECT * FROM Table SL Where SL.RECEIVED_DATE = TO_DATE( " 
+ (DT_WSTR, 24)@[User::Last_Run_Timestamp] 
+ " , 'dd/mon/yyyy HH24:MI:SS')"

这篇关于SSIS - 使用 Attunity Oracle 数据源在 Oracle 查询中使用参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！