Thursday, April 21, 2011

SSIS Query Parameters for ADO .NET Source

I am trying to retieve data from a MySQL table and insert into a SQL Server table using ADO .NET connections in SQL Server 2008 SSIS. In my data flow task I have an ADO .NET Source which queries the MySQL table (select all invoices) and an ADO .NET Destination which inserts the data in my SQL Server table. Now, I would like to add a parameter in my data source so that I only select the max(invoiceNumber) retrieved from my SQL Server table. I have performed a similar task using "OLE DB Command" but the problem is that I need to query a MySQL database. Any ideas how I can achieve this?

From stackoverflow
  • Set Data Access Mode in ADO.NET Source to SQL Command and write the query.

    Ali_Abadani : I already do that for my query. Where would I get the parameter from?
    Damir Sudarevic : Variable -- first query your local DB, put result into a variable and pass variable to the second query.
  • You shoudn't have to add a parameter:

    select * 
    from invoices
    where invoiceNumber = (select max(invoiceNumber) from invoices)
    

    The above works in SQL Server. I'm assuming that the same query will work in MySQL

    Ali_Abadani : I think you might have mis-understood me. the select max(invoiceNumber) has to be called against my local SQL Server table. Then, I can use that number to query the MySQL database which is external. So, eveyday the package would get the new invoices from the MySQL database and pushes them to the SQL Server.
  • Ali_Abadani, Did you get the solution for your problem, Even i am looking for the same. please let me know.

  • I have a MAX value in @MAXVal Variable. In Expression for ADO.NEt Source: SQLCommand , need to add value like below..? select *
    from invoices where invoiceNumber = @MaxVal.

    Please let me know.

0 comments:

Post a Comment