如何将参数传递给 ssis 中的 ado.net 源? [英] how to pass parameters to an ado.net source in ssis?

问题描述

这是原始查询，使用 ado.net 源和使用源中指定的 .net providers\odbc 数据提供者可以正常工作.

This is the original query, which works fine using ado.net source and using the .net providers\odbc data provider specified inside the source.

SELECT a.FA_CLNT_ID, a.FA_ACCT_NM, a.ACCT_E_DT, a.POL_PER_CURR_DT, a.POL_PER_NEXT_DT, a.FA_ACCT_NUM, a.GRP_SALES_OFFC_CD, a.ACCT_C_DT   
FROM  gyv2M.DDM_ACCT_STRUC a   
INNER JOIN  
(SELECT max(DDM_ACCT_STRUC_TP) as   DDM_ACCT_STRUC_TP, FA_CLNT_ID 
 FROM gyv2M.DDM_ACCT_STRUC  
 WHERE FA_DM_ROW_DT <= '6/30/2011' AND DM_ROW_E_DT <= '6/30/2011'
 GROUP BY  FA_CLNT_ID) b 
 ON a.DDM_ACCT_STRUC_TP = b.DDM_ACCT_STRUC_TP AND a.FA_CLNT_ID = b.FA_CLNT_ID AND FA_DM_ROW_DT <= '6/30/2011' AND a.DM_ROW_E_DT <= '6/30/2011'

但我需要使其自动化，以便从 sql server 中的表中获取值.我创建了一个变量来存储该日期值，但我想使用该变量作为参数传递给上述查询.不幸的是，我没有从 ado.net 源代码中的变量中找到数据访问模式:sql 命令.我搜索了一些其他网站，发现我们可以使用表达式构建器来使用一些表达式.rt 单击 preperties ..> 表达式 ..> 等.. 成功评估表达式.表达式如下所示，但我在那里被击中了.接下来是什么 ?如何在 ado.net source 中将计算的表达式作为源命令传递?请帮忙.

but I need to make it automated for that DATE getting a value from a table in sql server. I created a variable to store that date value, but i want to use that variable as parameter to pass in to the above query. Unfortunately I didn't find the data access mode : sql command from a variable in the ado.net source. I seached some other sites and found that we can use some expression using expression builder. rt click on preperties ..> expressions ..> etc.. evaluated the expressions successfully. the expresion looks like below but I got struck there. What is next ? How do I pass the evaluated expression as a source command in ado.net source ? Please help.

SELECT a.FA_CLNT_ID, a.FA_ACCT_NM, a.ACCT_E_DT, a.POL_PER_CURR_DT, a.POL_PER_NEXT_DT, a.FA_ACCT_NUM, a.GRP_SALES_OFFC_CD, a.ACCT_C_DT   
FROM  gyv2M.DDM_ACCT_STRUC a   
INNER JOIN  
(SELECT max(DDM_ACCT_STRUC_TP) as   DDM_ACCT_STRUC_TP, FA_CLNT_ID 
 FROM gyv2M.DDM_ACCT_STRUC  
 WHERE FA_DM_ROW_DT <=  '@[User::RepDate]'  AND DM_ROW_E_DT <=  '@[User::RepDate]'
 GROUP BY  FA_CLNT_ID) b 
 ON a.DDM_ACCT_STRUC_TP = b.DDM_ACCT_STRUC_TP AND a.FA_CLNT_ID = b.FA_CLNT_ID AND FA_DM_ROW_DT <=  '@[User::RepDate]'  AND a.DM_ROW_E_DT <=  '@[User::RepDate]'

更新

非常感谢您的回复.正如您指定的那样，我创建了一个用户变量 user:RepDate 和 datetime 以及一个将 datetime 分配给该变量 RepDate 的执行 sql 任务.而且是的，我能够评估表达式，并且能够看到在 ado.net souce sql 命令模式下构建的查询.这是查询:

Thank you very much for your response. As you specified i creeated a user variable user:RepDate with datetime and an execute sql task that assigns datetime to that variable RepDate. And also Yes, I am able to evaluate the expression and i was able to see the query builded in the ado.net souce sql command mode. here is the query:

SELECT a.FA_CLNT_ID, a.FA_ACCT_NM, a.ACCT_E_DT, a.POL_PER_CURR_DT, a.POL_PER_NEXT_DT, a.FA_ACCT_NUM, a.GRP_SALES_OFFC_CD, a.ACCT_C_DT   
FROM  gyv2M.DDM_ACCT_STRUC a   
INNER JOIN  
(SELECT max(DDM_ACCT_STRUC_TP) as   DDM_ACCT_STRUC_TP, FA_CLNT_ID 
 FROM gyv2M.DDM_ACCT_STRUC  
 WHERE FA_DM_ROW_DT <= @[User::RepDate]  AND DM_ROW_E_DT <=  @[User::RepDate] 
 GROUP BY  FA_CLNT_ID) b 
 ON a.DDM_ACCT_STRUC_TP = b.DDM_ACCT_STRUC_TP AND a.FA_CLNT_ID = b.FA_CLNT_ID AND FA_DM_ROW_DT <=  @[User::RepDate] AND a.DM_ROW_E_DT <=  @[User::RepDate]

但我收到以下错误.我认为这是因为内部查询需要我在原始查询中指定的日期格式 'mm/dd/yyyy'..但我们从用户变量提供日期时间.你能告诉我如何使用内置函数将用户变量的日期时间格式转换为指定的格式吗?我试过 DT_Date 但没有运气.

But I am receiving the following error given below. I think it's because the inside query is expecting date format 'mm/dd/yyyy' as I specified in the original query..but we are providing datetime from the user variable. Could you tell me how to convert the datetime format of user variable to the specified format using the inbuilt functions. I tried DT_DAte but no luck.

Error at Data Flow Task 4 [ADO NET Source [1]]: The component "ADO NET Source" (1) was unable to retrieve column information for the SQL command. The following error occurred: ERROR [42601] [IBM][CLI Driver][DB2] SQL0104N  An unexpected token "[" was found following "".  Expected tokens may include:  "CONCAT || / MICROSECONDS MICROSECOND SECONDS SECOND MINUTES".  SQLSTATE=42601
ADDITIONAL INFORMATION:

Pipeline component has returned HRESULT error code 0xC02020FF from a method call. (Microsoft.SqlServer.DTSPipelineWrap)
BUTTONS:
OK

***Update2***

数据流任务 [ADO NET 源 [1]] 中的错误:组件ADO NET 源"(1) 无法检索 SQL 命令的列信息.发生以下错误:ERROR [22007] [IBM][CLI Driver][DB2] SQL0180N 日期时间值的字符串表示的语法不正确.SQLSTATE=22007

Error at Data Flow Task [ADO NET Source [1]]: The component "ADO NET Source" (1) was unable to retrieve column information for the SQL command. The following error occurred: ERROR [22007] [IBM][CLI Driver][DB2] SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007

附加信息:

管道组件从方法调用返回了 HRESULT 错误代码 0xC02020FF.(Microsoft.SqlServer.DTSPipelineWrap)

Pipeline component has returned HRESULT error code 0xC02020FF from a method call. (Microsoft.SqlServer.DTSPipelineWrap)

按钮:

推荐答案

您可以使用以下步骤:

1. 在 ADO.NET 源中使用您的工作查询.[您无法映射ADO.NET 源代码的参数]
2. 选择您的 Dataflow 并点击 Properties.
3. 在属性窗格中，查找名为Expressions"的属性.展开它并单击..."图标以打开属性表达式编辑器(下面的屏幕截图).为您的数据源选择属性 SqlCommand 并使用表达式构建器使用 SSIS 变量(在您的情况下为日期时间变量)准备您的查询.

1. Use your working query into the ADO.NET source.[You could not map the parameters for the ADO.NET source]
2. Select your Dataflow and click Properties.
3. In the properties pane, look for the property named "Expressions". Expand it and click the "..." icon to open the Property Expressions Editor (screenshot below). Select the property SqlCommand for your Data Source and using the Expression builder prepare your query using SSIS variables (datetime variable in your case).

在你的表达式构建器中试试这个——注意你的变量必须是一个字符串，如果不是，你使用 (DT_WSTR,30) 来转换它.由于您使用的是日期，因此您的 SQL 将需要处理字符串格式的日期，即在 Oracle 中使用 to_date().

Try this in your expression builder -- note that your variable must be a string, if it's not, you use (DT_WSTR,30) to cast it. Since you're using dates, your SQL will need to handle a string-formatted date, i.e. use to_date() in Oracle.

 "SELECT a.FA_CLNT_ID, a.FA_ACCT_NM, a.ACCT_E_DT, a.POL_PER_CURR_DT, a.POL_PER_NEXT_DT, a.FA_ACCT_NUM, a.GRP_SALES_OFFC_CD, a.ACCT_C_DT   
FROM  gyv2M.DDM_ACCT_STRUC a   
INNER JOIN  
(SELECT max(DDM_ACCT_STRUC_TP) as   DDM_ACCT_STRUC_TP, FA_CLNT_ID 
 FROM gyv2M.DDM_ACCT_STRUC 
WHERE FA_DM_ROW_DT <= '"+ (DT_WSTR,30)@[User::RepDate] +"'  AND DM_ROW_E_DT <= '"+ (DT_WSTR,30)@[User::RepDate] +"' 
 GROUP BY  FA_CLNT_ID) b 
 ON a.DDM_ACCT_STRUC_TP = b.DDM_ACCT_STRUC_TP AND a.FA_CLNT_ID = b.FA_CLNT_ID AND FA_DM_ROW_DT <= '" +(DT_WSTR,30)@[User::RepDate] +"' AND a.DM_ROW_E_DT <='"+(DT_WSTR,30)@[User::RepDate]+"'"

希望这会有所帮助！

这篇关于如何将参数传递给 ssis 中的 ado.net 源?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！