使用SSIS查询ADO .NET源查询从SQL Server传入参数 [英] Using SSIS to query ADO .NET source query passing in parameters from SQL Server

问题描述

我想使用ado.net作为查询源将参数传递给MySQL查询.

I would like to pass parameters to a MySQL query using ado.net as the source of the query.

SELECT *  
FROM   userinfo
WHERE  load_date > '2012-01-07' 
AND    load_date < '2012-01-14'

我在sql server中有一个设置表，该表具有我的值:

I have a settings table in sql server that has my values:

SELECT  startdate, enddate 
FROM   tblsetting

我设法将这些值传递给SSIS.如果我对sql服务器表运行查询，则可以使用此命令:

I have managed to pass these values into SSIS. If I run a query against my sql server tables I am able to use this:

SELECT *  
FROM    userinfo
WHERE   load_date > ? 
AND     load_date < ?

但是当我将源更改为ado.net并连接到mysql服务器时，它不喜欢这种语法.谁能解释我在想什么?

But when I change my source to ado.net, connecting to mysql server , it does not like that syntax. Can anyone explain what am I missing?

推荐答案

您可以编写SQL表达式并将其存储为类型为String

You can write a SQL expression and store it a variable of type String

"SELECT *  
 FROM    userinfo
 WHERE   load_date > '" +  (DT_WSTR,20) @[User::YourDateTimeVar]  + "''
 AND     load_date < '" +   (DT_WSTR,20)@[User::YourDateTimeVar]  + " ' "

，然后将此变量用于Data Flow Task表达式中存在的属性.写单击DFT并选择Properties.单击表达式，然后从对话框的属性中选择Ado.NET sql command

and then use this variable for a property which is present in the expression of Data Flow Task .Write click on DFT and select Properties . Click on Expression and select Ado.NET sql command from the properties in the dialogue box

为上述属性选择变量.此处的ADO_SRC_Orale[SqlCommand]属性将替换为ADO.NET source

Select the variable for the above property.Here ADO_SRC_Orale[SqlCommand] property will be replaced by the name of your ADO.NET source

这篇关于使用SSIS查询ADO .NET源查询从SQL Server传入参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！