How to repeatedly run DTS package with different arguments

Posted on 2002-03-28
Last Modified: 2007-11-27
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
Question by:raykata2ddotcom

Accepted Solution

JamesT earned 200 total points
ID: 6903735
I have the following code in a VB application. It does the following:

Opens a connection to the database
Does a select for a record set
Loops on the recordset result
Executes a DTS passing in a global variable
Gets the execute status

You could loop on a counter OR you could execute it and loop until NOT(oStep.ExecutionResult = TSStepExecResult_Failure)
That way you don't have to use a counter.

Post back with questions if you like. I had a form that tracked progress so that is why I have the tbStatus.Text bit going on.

    Dim RS_TableListing As New ADODB.Recordset
    Dim strSQLSourceCode As String
    Dim oPKG As New DTS.Package
    Dim lErr As Long, sSource As String, sDesc As String
    Dim intErrFlag As Integer

    intErrFlag = 0
    tbStatus.Text = Format(Time, "hh:mm:ss AMPM") + " PROCESSING ---> Begin Processing"

    Set Conn = CreateObject("ADODB.Connection")
    Conn.ConnectionTimeout = 0
    Set RS_TableListing = Conn.Execute("SELECT * FROM TBL")
    Do While Not RS_TableListing.EOF
        tbStatus.Text = tbStatus.Text + vbCrLf + Format(Time, "hh:mm:ss AMPM") + " PROCESSING TABLE ---> " + RS_TableListing.Fields(0)
        oPKG.LoadFromSQLServer "SERVER", , , DTSSQLStgFlag_UseTrustedConnection, , , , "DTS_LOAD_DEVELOPMENT"
        oPKG.GlobalVariables("strTABLE_NAME").Value = RS_TableListing.Fields(0)
        For Each oStep In oPKG.Steps
            If oStep.DisableStep = False And oStep.Description = RS_TableListing.Fields(0) Then
                If oStep.ExecutionResult = DTSStepExecResult_Failure Then
                    oStep.GetExecutionErrorInfo lErr, sSource, sDesc
                    sMessage = sMessage & " Step """ & oStep.Name & """ Failed" & vbCrLf & vbTab & "Error: " & lErr & vbCrLf & vbTab & "Source: " & sSource & vbCrLf & vbTab & "Description: " & sDesc & vbCrLf & vbCrLf
                    intErrFlag = 1
                    sMessage = sMessage & " Step """ & oStep.Name & """ Succeeded"
                End If
                tbStatus.Text = tbStatus.Text + vbCrLf + Format(Time, "hh:mm:ss AMPM") + sMessage
            End If
        If intErrFlag = 1 Then
            Exit Do
        End If

        Set oPKG = Nothing

    Set Conn = Nothing
    If intErrFlag = 1 Then
        tbStatus.Text = tbStatus.Text + vbCrLf + Format(Time, "hh:mm:ss AMPM") + " PROCESSING ---> Finished with Errors"
        tbStatus.Text = tbStatus.Text + vbCrLf + Format(Time, "hh:mm:ss AMPM") + " PROCESSING ---> Finished Successful"
    End If
End Sub

Private Sub ThereBeTrouble()
    foo = MsgBox("An Error Was Encountered - See log for details", vbCritical, "TROUBLE!")
End Sub

Expert Comment

ID: 6905159
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.

Author Comment

ID: 6910222
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.

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now