Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to repeatedly run DTS package with different arguments

Posted on 2002-03-28
3
Medium Priority
?
480 Views
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
0
Comment
Question by:raykata2ddotcom
3 Comments
 
LVL 2

Accepted Solution

by:
JamesT earned 800 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
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
 
LVL 3

Expert Comment

by:trouta
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.
0
 

Author Comment

by:raykata2ddotcom
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.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

927 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