Solved

How to repeatedly run DTS package with different arguments

Posted on 2002-03-28
3
468 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 2

Accepted Solution

by:
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
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

628 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