DTS from informix to MS SQL 2000

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

"reenz" <reenz.25iewz@no-mx.forums.yourdomain.com.au> wrote in message news:reenz.25iewz@no-mx.forums.yourdomain.com.au... > > 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