Copying many tables from Sybase to SQL Server

sbass1 · Post by **sbass1** » Wed Apr 22, 2009 3:38 am

Hi,

We need to copy many tables (say 50) from Sybase to SQL Server. The tables have IDENTICAL schema. It's just a straight copy across databases.

Do I need to create 50 jobs, or can I have a "template" job with the name of the tables passed in as a parameter?

This would need to support a select * type of approach.

P.S.: I know this isn't a SAS forum, but in SAS it would be SO easy:

Code: Select all

libname in <connection details to sybase>;
libname out <connection details to sql server>;

proc copy in=in out=out;
   select desired tables to copy;
run;

Done...

chulett · Post by **chulett** » Wed Apr 22, 2009 7:46 am

Anything stopping you from actually using SAS for this?

Server does not support the "select * approach" you mentioned, however a PX job would. For whatever that is worth. So if you really felt the need to do this in DataStage Server, yes you would need as many jobs as you have tables. Now, some people have built job generators that can take table metadata and automatically build all of the jobs in question, but that doesn't change the need for one job per table.

Me, without access to something like SAS, would probably script all this. That or check and see if the DBAs could help at all, see if there was any way to dump the information from one db and import it all into the other, leveraging any command line utilities that come with each.

sbass1 · Post by **sbass1** » Wed Apr 22, 2009 5:04 pm

chulett wrote:Anything stopping you from actually using SAS for this?

Yep, two things:

1. While we do have SAS, it's on a restricted license, supporting a single application.

2. Sybase ASE --> SQL Server is fine, but Sybase IQ --> SQL Server would require the ODBC drivers, which unfortunately we didn't license. However, see #1

The only reason I mention it is the hope that IBM either monitor this forum, or others might get the message to IBM. When a competitive ETL product is able to do something so much more easily than DataStage, I hope the DS marketeers and engineers would consider this for future enhancement. Something that would take 10 minutes to code and test in SAS will take us about 5-8 days using DS (7.x Server jobs).

I look forward to upgrading to 8.1 and hopefully PX as well.

Thanks for the reply...

chulett · Post by **chulett** » Wed Apr 22, 2009 5:21 pm

Funny, but I don't really think "ETL" when I see "SAS" but hitting their website reveals that it is one of their offerings: SAS Enterprise Data Integration Server is what I assume you are referring to, yes?

And there is an "Enhancement Wish List" forum which I've been told gets over to Ardent / Ascential / IBM / whomever, so couldn't hurt to write up something there.

sbass1 · Post by **sbass1** » Mon Apr 27, 2009 9:32 pm

Hi Craig,

As an ETL language, I really really like SAS. IMO, I can do much more complex data transformations using SAS than DataStage, especially OOTB. So as a language it's rock solid, and has been used in major, large datawarehousing projects around the world.

However, there's the old saying "If all you've got in your toolbox is a hammer, everything starts to look like a nail". Since I have much more experience with SAS, I'm trying to be patient with DataStage. But DS is growing on me

, and this forum is fantastic. Thank you to all the Premium Posters, and other gurus...

As for SAS' ETL tool, I don't really like it. I find I'm always fighting with it to get it to generate the code I want, code that I could knock out much quicker since 1) I know the language, and 2) I've got a lot of reusable code in my "kitbag" over the years.

I'm sure the SAS ETL tool will continue to improve, but I think it's 2-3 years from maturity.

The big difference between the SAS ETL tool (Data Integration Studio) and DS is that DI Studio is a code generator, analogous to Visual Studio or Frontpage. If you could write the code "by hand" (and wanted to), there is NO difference between doing that and what the tool provides. And there are a lot of hooks in DI Studio to augment or replace the generated code with hand written code. AFAIK, this functionality is more limited in DS (except for sequence jobs). But, I'm only familiar with DS 7.5 Server; perhaps things are more customizable in 8.x or Parallel.

Regards,
Scott

chulett · Post by **chulett** » Mon Apr 27, 2009 10:43 pm

Thanks for the information Scott.