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?
A:
Set Data Access Mode in ADO.NET Source to SQL Command and write the query.
Damir Sudarevic
2009-12-21 21:00:34
I already do that for my query. Where would I get the parameter from?
Ali_Abadani
2009-12-21 21:04:32
Variable -- first query your local DB, put result into a variable and pass variable to the second query.
Damir Sudarevic
2009-12-26 20:29:24
A:
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
James Wiseman
2009-12-22 11:14:28
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
2009-12-22 19:23:38
A:
Ali_Abadani, Did you get the solution for your problem, Even i am looking for the same. please let me know.
Praveen
2010-01-18 10:54:49
A:
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.
Praveen
2010-01-18 11:02:54