SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Reply
Contributor
Posts: 21

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

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? 

Respected Advisor
Posts: 4,674

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

@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.

Contributor
Posts: 21

Re: How to update tables of a library using SAS Data integration Studio 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. 

Super User
Posts: 9,890

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 5,851

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

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).

Data never sleeps
Ask a Question
Discussion stats
  • 4 replies
  • 104 views
  • 0 likes
  • 4 in conversation