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
raykata2ddotcomAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
JamesTConnect With a Mentor Commented:
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
Loops

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
    Conn.Open "SERVER=SERVERNAME;DRIVER={SQL Server};DATABASE=DATABASENAME"
    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)
        oPKG.Execute
        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
                Else
                    sMessage = sMessage & " Step """ & oStep.Name & """ Succeeded"
                End If
                tbStatus.Text = tbStatus.Text + vbCrLf + Format(Time, "hh:mm:ss AMPM") + sMessage
            End If
        Next
        If intErrFlag = 1 Then
            ThereBeTrouble
            Exit Do
        End If

        oPKG.UnInitialize
        Set oPKG = Nothing
        RS_TableListing.MoveNext
    Loop

    Conn.Close
    Set Conn = Nothing
    If intErrFlag = 1 Then
        tbStatus.Text = tbStatus.Text + vbCrLf + Format(Time, "hh:mm:ss AMPM") + " PROCESSING ---> Finished with Errors"
    Else
        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
0
 
troutaCommented:
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.
0
 
raykata2ddotcomAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.