How to update tables of a library using SAS Data integration Studio job

[Azeem112]

Dear Community,

I've two servers. first one is has SQL server and second one has Mysql database server. I want to move and update data from SQL server database to Mysql database daily basis using SAS data integration studio. Both databases have same schema. For this purpose

1) I created an ODBC connection.

2) Added an ODBC server in SAS Management Console.

3) Created a ODBC library attached with my server.

4) Registered the tables in my ODBC library

Now I can access data of first server at second server. Now I want to update mysql database. For this I've to write a job who gets data from my ODBC library and update mysql database tables. I'm having issues that how can I do this? 

[Patrick]

@Azeem112

"Now I can access data of first server at second server" 

If using SAS then the path would be SQL Server -> SAS .> MySQL and you would need two ODBC connections/Servers defined in SAS Metadata - one for the source and one for the target.

Not sure what "Now I can access data of first server at second server" means. You can access the SQL tables from MySQL via an ODBC connection? If so then SAS is not involved and whatever you've defined in SAS Metadata doesn't come into play. ....or you now can access the SQL Server from SAS? If so then you need to do the same game for the target server.

If you want to use DIS then define both servers and table metadata for source and target and then a simple table loader should do the job.

[Azeem112]

I can access SQL data in SAS studio and in SAS Management Console but I can't simply write a table loader because there are 30 tables whose data i want to transfer. First I want to copy all data from SQL to Mysql then update mysql tables from SQL with recent data of daily basis. 

[Kurt_Bremser]

If you want to do it in DIS, then I guess the rule is 1 table -> 1 loader, so the data path can be maintained in the metadata.

[LinusH]

If you need a solid design, you need an appointed resource to help you.

We can just give answers which are quite limited to the specific question at hand, which in your case was quite wide.

If you go with a direct connection between the databases you can still maintain the metadata in SAS, but it becomes a bit more awkward, especially for the job (since you probably need explicit pass-through logic to define the load logic).

[DavidPhillips2]

What settings does the final table written back to a database need to have write to the actual database?