Re: DTS from informix to MS SQL 2000
- From: "Doug Lawry" <lawry@xxxxxxxxxxxxx>
- Date: Fri, 31 Mar 2006 11:35:55 +0100
"reenz" <reenz.25iewz@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:reenz.25iewz@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
am trying to import data from an Informix database every midnight. Each
import will take in ~2million records from a table and i have like 3
DTS package to import data from 3 different tables every night.
As the Informix database server is a in-production server, I want
prevent "locking" of the informix db. Is there anywhere i can set
isolation in DTS?
Also, i would like to import data at the fastest speed possible so as
to not hinder with other operations, would dts import/export wizard be
good enough? As i am relatively new to ms sql, are there other ways to
import data?
Thirdly, i am currently using the dts wizard for import. I noticed
should Informix throw any error, the dts will stop and return an
informix sqlerr error and no rows will be inserted into ms sql, is
there any way to implement a commit after x rows / note the errror row
but skip the error row and continue / set a flag, restart e dts and
continue?
kindly point me in the correct direction
tbanks a lot
--
reenz
------------------------------------------------------------------------
reenz's Profile: http://www.dbtalk.net/m90
View this thread: http://www.dbtalk.net/t296274
You should look into defining a Linked Server in Enterprise Manager. With the
same ODBC or OLEDB connection details you are currently using for DTS, you can
make it possible to create a cursor in a SQL Server stored procedure such as:
DECLARE cursor_name CURSOR FOR
SELECT * FROM OPENQUERY (
linked_server_name,
'EXECUTE PROCEDURE procedure_name(parameters)'
)
In your Informix procedure, you can then have statements such as:
SET ISOLATION TO DIRTY READ
SET LOCK MODE TO WAIT 60
You would return multiple rows using:
RETURN ... WITH RESUME
In your SQL Server procedure, you can control how often you commit rows, etc.
--
Regards,
Doug Lawry
www.douglawry.webhop.org
.
- References:
- DTS from informix to MS SQL 2000
- From: reenz
- DTS from informix to MS SQL 2000
- Prev by Date: Re: SELECT * FROM <sourcetable> INTO <newtable>
- Next by Date: Re: Stored Procedures Limitation
- Previous by thread: DTS from informix to MS SQL 2000
- Next by thread: IDS 9.4 or IDS 10 Linux and VMware
- Index(es):
Relevant Pages
|
|
