Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to repeatedly run DTS package with different arguments

Posted on 2002-03-28
3
Medium Priority
?
474 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 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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

722 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