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.
dotConnect Mysql and ssis multi query
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
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:
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:
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.
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
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;
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.