sorry, i was thinking you want to extract data from informix, anyway to
extract data from sqlserver you can use the same approach,
1) create linkserver using odbc source for informix db and write dts package
in sqlserver to extract the data from sqlserver and load in the informix
database
2) use bcp utility of sqlserver to extradct data from sqlserver

-----Original Message-----
From: Savio Pinto (s)
Sent: Tuesday, May 04, 2004 9:50 AM
To: 'Neil Truby'; informix-list@iiug.org
Subject: RE: Extracting data from a SQL Server database


here are couple of option that i could think of

1) sqlserver has something called as linked servers where you can link other
database like db2, oracle,i think they don't have anything for informix, but
you can create a linked server to access an ODBC database using an ODBC data
source to connect to the informix database, this way you can extract data
from informix database and load it to the sqlserver database using sqlserver
routines or DTS packages to pull the data.

2)you can do the extract from the informix 4gl programs and unload to a
delimited file, then use the sql server bcp utility to load the delimited
file in the sql server database.


-----Original Message-----
From: owner-informix-list@iiug.org
[mailtowner-informix-list@iiug.org]On Behalf Of Neil Truby
Sent: Tuesday, May 04, 2004 8:44 AM
To: informix-list@iiug.org
Subject: Extracting data from a SQL Server database


My client has a series of 4GL programs that do ETL of data from various
Informix sources into a "data mart".
It works like this: a 4GL program uses synonyms to extract data from remote
live Informix databases and loads the data into a staging database. Another
set of 4GLs extract this "staging" data and insert into into the data mart.

Now they want to add a new database to the first part: that is, the
extraction of the live data. Trouble is, this new database is a SQL Server
one.

Any opinions on the easiest way to achieve this?

thanks
Neil Truby t:01932 724027
Director m:07798 811708
Ardenta Limited e:neil.truby@ardenta.com

sending to informix-list

"Savio Pinto (s)" <spinto@cap.org> wrote in message
news:c78dfc$k1r$1@terabinaries.xmission.com...
>
> sorry, i was thinking you want to extract data from informix, anyway to
> extract data from sqlserver you can use the same approach,
> 1) create linkserver using odbc source for informix db and write dts
package
> in sqlserver to extract the data from sqlserver and load in the informix
> database
> 2) use bcp utility of sqlserver to extradct data from sqlserver

Thanks to Savio, Rob, Venkatesh and everyone else who suggested various
options. I like the sound of the Enterprise Gateway: it's £6.5k retail here
so I guess it will come down to how much the client wants to spend on the
project ....

regards
Neil

"Neil Truby" <neil.truby@ardenta.com> wrote in message news:<c78h2b$u2s7$1@ID-162943.news.uni-berlin.de>...
> "Savio Pinto (s)" <spinto@cap.org> wrote in message
> news:c78dfc$k1r$1@terabinaries.xmission.com...
> >
> > sorry, i was thinking you want to extract data from informix, anyway to
> > extract data from sqlserver you can use the same approach,
> > 1) create linkserver using odbc source for informix db and write dts
> package
> > in sqlserver to extract the data from sqlserver and load in the informix
> > database
> > 2) use bcp utility of sqlserver to extradct data from sqlserver
>
> Thanks to Savio, Rob, Venkatesh and everyone else who suggested various
> options. I like the sound of the Enterprise Gateway: it's £6.5k retail here
> so I guess it will come down to how much the client wants to spend on the
> project ....
>
> regards
> Neil
You might want to take a look at our new product, Super*SQL. It
allows you to connect to any database via JDBC and extract data into
an ASCII file that can be immediately loaded into another database.
The whole process can run in true "batch" mode so you can make it a
scheduled process.

Super*SQL will sell for $US 20 but if you want to become part of our
Phase II Beta testing program you would get a copy for free.

For more information visit www.sqlmagic.com.