Devart Forums

Posted: **Sat 03 Mar 2012 10:20**

Hello,
I need to make an ssis to update a sql server database.

my first try works very well with simple insert off my mysql database in sqlserveur I use:

- Sql task
- Data flow

by cons I can not coment do an update request with a more complex query example:

Fiches is sql server database
got_pays is the mysql database

I need to update a database sql server with a request like this:

UPDATE Fiches
SET = Fiches.pays_nom = got_pays.pays_nom
FROM Sheets, got_pays
WHERE (got_pays.id = Fiches.id_pays)

how to do this in ssis j'm looking for examples but I have found nothing.
thank you for your help.

Posted: **Wed 07 Mar 2012 09:12**

If I understand you correctly, you have two tables. got_pays is a table from MySQL database and Fiches is a table from SQL Server database. You have already inserted data to the Fiches table from SQL Server database. Now you want to update the column Fiches.pays_nom from the column got_pays.pays_nom from the got_pays table, which is located in MySQL database?
In this case you should perform the following:
1. Run Business Intelligence Development Studio if it has not been run earlier;
2. Create Integration Services Project if it has not been created earlier;
3. In the Project menu, click SSIS Export and Import Wizard;
4. Choose Devart dotConnect for MySQL as a datasource;
5. Choose your SQL Server database as a destination;
6. Select 'Write a query to specify the data to transfer' on the next tab 'Specify Table Copy or Query';
7. Write the following code on the next tab:

Code: Select all

select * from got_pays

8. change a name for new table on 'Select Source Tables and Views'. For example, [dbo].[Query] -> [dbo].[got_pays];
9. Then click Next and Finish buttons. A new package will be created;
10. Drag Execute SQL Task from the toolbox on the designer;
11. Right-click it and chose Edit;
12. Change the following properties in the displayed dialog box:
12.1. Select ADO.NET for the ConnectionType property (if you use SQLClient as dataprovider for SQL Server database);
12.2. Select connection DestinationConnectionAdoNET for the Connection property;
12.3. Specify the following query for the SQLStatment property:

Code: Select all

UPDATE Fiches
SET database1.dbo.Fiches.pays_nom = database2.dbo.got_pays.pays_nom
FROM database1.dbo.Fiches, database2.dbo.got_pays
WHERE (database2.dbo.got_pays.id = database1.dbo.Fiches.id_pays);

drop table database2.dbo.got_pays;

database1 - it is database in SQL Server where the Fiches table is located.
database2 - it is database in SQL Server where the got_pays table is located.

13. Connect the Data Flow Task component with the Execute SQL Task component;
14. Execute the package and necessary will be updated.

Devart Forums

dotConnect Mysql and ssis multi query

dotConnect Mysql and ssis multi query