[dba-SQLServer] Get my data Informix-SQLServer

Robert L. Stewart rl_stewart at highstream.net
Tue Jul 18 12:27:35 CDT 2006


Mark,

In your DTS package, go to the last tab, Options, of your transform.  Set the
insert batch size to 100 and set the always commit last batch.  This will allow
you to commit each 100 records.  It runs a little slower, but you will loose
less records that way.

If you have an alpha field like customer name, you could also set up a package
for each letter of the alphabet and run all 27 of them at once based on the
first letter of the company name.  The 27th one being any that did not start
with a letter.

Robert L. Stewart
The Dyson Group International
Software for the Non-profit Enterprise
Expanding your Sphere of Knowledge


Quoting dba-sqlserver-request at databaseadvisors.com:

> Date: Mon, 17 Jul 2006 20:28:28 +0000
> From: "Mark A Matte" <markamatte at hotmail.com>
> Subject: [dba-SQLServer]  Get my data Informix-SQLServer
> To: dba-sqlserver at databaseadvisors.com
> Message-ID: <BAY121-F733473F1B811CE16485A8D2620 at phx.gbl>
> Content-Type: text/plain; format=flowed
>
> Hello All,
>
> I have SQLServer 7 and I have a link to an Informix db via ODBC.  There are
> over 6 million rows.  I don't have a 24hour connection( and what I do have
> is slower than dirt).  The only reason to move it to SQL is to do wildcards
> searches on a text field.  If I use the DTS wizard to pull this in...I'm not
> sure it would run in the 10 hour window I have...and second...if I cancel
> half way through...I don't get to keep the first half.
>
> When I was pulling this same information into access...I used a counter, ran
> my SQL for ID 1 to 100,000, looped back to beginning, added 100,000 to my
> criteria, and ran my SQL again.  At anytime I lost my connection I only lost
> the current 100K group it was pulling.
>
> I have a fairly strong background in SQL, Access, and VBA...but SQL Server
> is new to me.  Any suggestions would be great...so is solution above used in
> Access a likely candidate for the scenario, and if so...where is SQLServer
> would I create/execute it from (Stored Proc)?...and if not what should I do.
>
> Summary, I need to bring 6mil records from Informix to SQL to do wild card
> searches I have an ODBC connection to Informix...and SQLServer is local.
>
> Many thanks for any ideas/direction.
>
> Thanks,
>
> Mark A. Matte





More information about the dba-SQLServer mailing list