raykata2ddotcom
asked on
How to repeatedly run DTS package with different arguments
I am using a DTS packdage to move data from a SQL Server database to an Oracle database. My SQL statment is essentially:
select * from view
where id between idfr and idto
How can I repeatedly call this package from VB (or DTS itself) in a loop along the lines of:
for i = 1 to 100
idfr = (i-1)*100000+1
idto = i*100000
call package(idfr,idto)
next i
I need this loop for 2 reasons:
- the package always fails after about 150,000 to 170,000 inserts no matter what values I use for InsertCommitSize (currently set to 2000) and MaximumErrorCount (currently set to 999999)
- in case of failure, I'd like to be able to just continue from the point of failure, because I have millions of rows to transfer
select * from view
where id between idfr and idto
How can I repeatedly call this package from VB (or DTS itself) in a loop along the lines of:
for i = 1 to 100
idfr = (i-1)*100000+1
idto = i*100000
call package(idfr,idto)
next i
I need this loop for 2 reasons:
- the package always fails after about 150,000 to 170,000 inserts no matter what values I use for InsertCommitSize (currently set to 2000) and MaximumErrorCount (currently set to 999999)
- in case of failure, I'd like to be able to just continue from the point of failure, because I have millions of rows to transfer
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
one tool I found useful, if you are using SQL 2000 and are familar with VB, you can save the DTS package as a visual basic file. This file can be added to a project and can be modified to include some parameters.
ASKER
Thanks a million! Your framework gave me exactly what I needed to get the job done. I used DTS packages on server A that used views on a linked server B to retrieve the data from a SQL Server 2000 database and insert it to an Oracle 8i database on server C. I managed to load one table with 2.5 million rows in about 3 hours by repeatedly executing a package 123 times with different parameters. I then loaded another table with 38.4 million rows in about 43 hours by repeatedly executing a package 1230 times with different parameters. Each execution of the package inserted anywhere from 0 to 150,000 rows. BTW, I bought a book called Professiona SQL Server 2000 DTS published by WROX to help me sort out some of the details. I'm becoming more and more impressed with DTS. Thanks again for your help.