locked
Pass Variable to ADO.Net Data Source For Use in Query RRS feed

  • Question

  • I have a situation where I need to take data from a SQL Server query and pass it to an ADO.Net data source, which uses a query to return records from a MySQL database.

    I need to retrieve the maximum date from a table in SQL Server, then filter the MySQL table on that date.

    I currently have a variable created in SQL Server called @MaxProcessedDate which is a datetime. I also have a variable called MaxProcessedQuery, which is a string, and looks like this:

    "SELECT MAX(processedDate) FROM FactActivity"

    My ADO.Net source query is this:

    SELECT id,
           consumerid,
           keyword,
           receiveddate,
           processeddate,
           createdappid,
           field,
           unit,
           value,
           insertdate,
           enabled,
           persistid,
           lastupdated
    FROM  mnemonic

    I need to pass the following to the source query:

    SELECT id,
           consumerid,
           keyword,
           receiveddate,
           processeddate,
           createdappid,
           field,
           unit,
           value,
           insertdate,
           enabled,
           persistid,
           lastupdated
    FROM  mnemonic

    WHERE processeddate > @MaxProcessedDate

    How does one accomplish this with the ADO.Net data source. I've been looking everywhere but can't seem to find anything. Any help would be appreciated!!


    A. M. Robinson

    Monday, September 22, 2014 7:44 PM

Answers

  • I have a situation where I need to take data from a SQL Server query and pass it to an ADO.Net data source, which uses a query to return records from a MySQL database.

    I need to retrieve the maximum date from a table in SQL Server, then filter the MySQL table on that date.

    I currently have a variable created in SQL Server called @MaxProcessedDate which is a datetime. I also have a variable called MaxProcessedQuery, which is a string, and looks like this:

    "SELECT MAX(processedDate) FROM FactActivity"

    My ADO.Net source query is this:

    SELECT id,
           consumerid,
           keyword,
           receiveddate,
           processeddate,
           createdappid,
           field,
           unit,
           value,
           insertdate,
           enabled,
           persistid,
           lastupdated
    FROM  mnemonic

    I need to pass the following to the source query:

    SELECT id,
           consumerid,
           keyword,
           receiveddate,
           processeddate,
           createdappid,
           field,
           unit,
           value,
           insertdate,
           enabled,
           persistid,
           lastupdated
    FROM  mnemonic

    WHERE processeddate > @MaxProcessedDate

    How does one accomplish this with the ADO.Net data source. I've been looking everywhere but can't seem to find anything. Any help would be appreciated!!


    A. M. Robinson


    Go to the data flow task (in the control flow), highlight and press F4 (properties). Go the expressions property and hit the ellipsis. The property you need to use is the "[ADO NET Source].[Sql Command]".

    Thanks, hsbal

    • Proposed as answer by cnk_gr Monday, September 22, 2014 9:03 PM
    • Marked as answer by ansonee Tuesday, September 23, 2014 9:07 PM
    Monday, September 22, 2014 8:58 PM

All replies

  • Add the FactActivity table to your mySQL database and put the max date there. Then change your query to:

    SELECT id, 
           consumerid, 
           keyword, 
           receiveddate, 
           processeddate, 
           createdappid, 
           field, 
           unit, 
           value, 
           insertdate, 
           enabled, 
           persistid, 
           lastupdated 
    FROM  mnemonic
    
    WHERE processeddate > (SELECT MAX(ProcessedDate) FROM FactActivity)



    • Edited by cnk_gr Monday, September 22, 2014 8:02 PM
    Monday, September 22, 2014 8:00 PM
  • To make an oledb source with a variable you write your query as:

    SELECT id, 
           consumerid, 
           keyword, 
           receiveddate, 
           processeddate, 
           createdappid, 
           field, 
           unit, 
           value, 
           insertdate, 
           enabled, 
           persistid, 
           lastupdated 
    FROM  mnemonic
    
    WHERE processeddate > ?

    and at the parameters you put the user variable you filled in a previous sql task

    Monday, September 22, 2014 8:03 PM
  • Yeah...that's not gonna work. We can't just go moving tables around between different database servers to make things easier.

    And again...an OLEDB source is not an option as I explained in my initial post. The source is an ADO.Net connection.


    A. M. Robinson

    Monday, September 22, 2014 8:07 PM
  • You can just move ONE table to the other database with one field holding one value, the max value, instead of using a variable.


    • Edited by cnk_gr Monday, September 22, 2014 8:14 PM
    Monday, September 22, 2014 8:13 PM
  • These are production systems. Moving around tables is just not even an option. Even if these weren't production systems, moving a table - even just one column - from one database to another makes no sense.

    It's not some static value. The max data is ALWAYS changing.


    A. M. Robinson

    Monday, September 22, 2014 8:51 PM
  • I have a situation where I need to take data from a SQL Server query and pass it to an ADO.Net data source, which uses a query to return records from a MySQL database.

    I need to retrieve the maximum date from a table in SQL Server, then filter the MySQL table on that date.

    I currently have a variable created in SQL Server called @MaxProcessedDate which is a datetime. I also have a variable called MaxProcessedQuery, which is a string, and looks like this:

    "SELECT MAX(processedDate) FROM FactActivity"

    My ADO.Net source query is this:

    SELECT id,
           consumerid,
           keyword,
           receiveddate,
           processeddate,
           createdappid,
           field,
           unit,
           value,
           insertdate,
           enabled,
           persistid,
           lastupdated
    FROM  mnemonic

    I need to pass the following to the source query:

    SELECT id,
           consumerid,
           keyword,
           receiveddate,
           processeddate,
           createdappid,
           field,
           unit,
           value,
           insertdate,
           enabled,
           persistid,
           lastupdated
    FROM  mnemonic

    WHERE processeddate > @MaxProcessedDate

    How does one accomplish this with the ADO.Net data source. I've been looking everywhere but can't seem to find anything. Any help would be appreciated!!


    A. M. Robinson


    Go to the data flow task (in the control flow), highlight and press F4 (properties). Go the expressions property and hit the ellipsis. The property you need to use is the "[ADO NET Source].[Sql Command]".

    Thanks, hsbal

    • Proposed as answer by cnk_gr Monday, September 22, 2014 9:03 PM
    • Marked as answer by ansonee Tuesday, September 23, 2014 9:07 PM
    Monday, September 22, 2014 8:58 PM
  • I think I didn't explain this enough. To summarize.

    1. Create a table on mySQL (once).

    2. Put a Execute SQL Task on mySQL that does "TRUNCATE helptable" 

    3. Create dataflow task that does select from SQL DB and destination mySQL that puts the current max value to the helptable.

    4. Use the helper table to filter your SELECT.

    You can't have a variable from SSIS on ADO.NET source.

    Apparently you can using @Harry's way!

    You either change to ODBC/OLE.DB or settle for this. Otherwise you will have to filter after SELECT which IMO is much much ... much worse that having a little inconvenient table.

    The next time your job runs I suppose the processed date will be correct.... 


    • Edited by cnk_gr Monday, September 22, 2014 9:05 PM
    Monday, September 22, 2014 9:01 PM
  • Great solution @Harry.
    Monday, September 22, 2014 9:04 PM
  • You can't have a variable from SSIS on ADO.NET source.


    Yes, we can have. It is not exposed on the ADO.NET source editor. I have mentioned above, how we can use use the SQL command from a variable for ADO NET source.

    Thanks, hsbal

    Monday, September 22, 2014 9:06 PM
  • I think the next (small) obstacle comes to when you have to evaluate the parameter you need to have a 'default' value for it so you can to get the columns for your data source.

    This is actually replacing the query instead of using a parameter for it. So it is a little harder to maintain when you need to make changes.

    I'd still go my way (looks cleaner to me) and add a helper table but it's a fresh way of looking at things !


    Monday, September 22, 2014 9:29 PM
  • So I've followed your steps and still can't seem to get this to work. I've created a variable called @MaxProcessedDate and it gets populated by an Execute SQL task that does a simple SELECT MAX from the table I'm interested in.

    I then went into the properties of the data flow task and edited the properties as you instructed. I put the following query in the Expression box:

    "SELECT id,
           consumerid,
           keyword,
           receiveddate,
           processeddate,
           createdappid,
           field,
           unit,
           value,
           insertdate,
           enabled,
           persistid,
           lastupdated
    FROM  mnemonic
    WHERE processeddate >   @MaxProcessedDate"

    I click Evaluate Expression and it appears to evaluate just fine.

    When I got to run the package, however, it fails with the following error:

    Is there something in the way my query is formatted?

    I just don't know why MS didn't expose parameters in ADO like they do for OLE DB...

    Apparently I'm not the only one who has had to struggle with this issue before!

    Here are the properties of the task:


    A. M. Robinson


    • Edited by ansonee Tuesday, September 23, 2014 3:52 PM picture
    Tuesday, September 23, 2014 3:49 PM
  • You need to set 'Delay Validation' to true for this to work.

    The option is on Control Flow -> Properties -> Delay validation -> True


    "If there's nothing wrong with me, maybe there's something wrong with the universe!"


    • Edited by cnk_gr Tuesday, September 23, 2014 4:07 PM
    Tuesday, September 23, 2014 4:06 PM
  • I was actually finally able to get this to work...the expression was formed incorrectly.

    Sadly, Microsoft doesn't make it "easy", but was able to pretty much establish a standard operating procedure when having to deal with ADO.Net connections - of which we have a great many packages that utilize an ADO.Net connection.  Creating one column, one row helper tables - and then syntax to have to populate those every single time - is just an overly complicated inefficient solution.

    But thanks for the input anyway!


    A. M. Robinson

    Tuesday, September 23, 2014 9:06 PM