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?
"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.
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.
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.
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).
What settings does the final table written back to a database need to have write to the actual database?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.