Using SSIS to query ADO .NET source query passing

问题:

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'

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

SELECT  startdate, enddate 
FROM   tblsetting

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 < ?

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?

回答1:

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]  + " ' "

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

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