Error executing DTS package created from Object Model

Error executing DTS package created from Object Model

Post by Greg Jense » Wed, 06 Dec 2000 04:00:00



I am trying to create a package to copy the data from a table in SQL Server
7 to an Identical table in Oracle.
If I create the DTS task with DTS designer it works correctly.

When I create the DTS package using the object model in VB6 the step that
copies the data fails. If I save my created package and run it from SQL
server, I get an oracle error message "Cannot insert NULL int <Field>

In the VB project I load the package I created with DTS designer and compare
to the package I am building with VB and the only difference I see is the
Copy step in the DTS designer created package has Columns listed in the
SourceColumns and DestinationColumns collections. These columns match the
columns in the table I am trying to copy. However, Iam trying to create a
generic copy routine that can copy any table therefore I don't know what the
columns will be. Here is a code sample of how I am creating the package:

Public Function DoDTSTableCopy(srcService As String, _
                                SrcUserID As String, _
                                SrcPassword As String, _
                                srcDatabase As String, _
                                DestService As String, _
                                DestUserID As String, _
                                DestPassword As String, _
                                sTableName As String) As Boolean
'This function moves the data in a table from a SQL server database to a
table
'with identical name and structure in an Oracle database.
'This function uses OLEDB to connect to the databases, and DTS to move the
data

'Parameters
'   SrcUserID       String      User in SQL Server
'   SrcPassword     String      Password in SQL Server
'   SrcDatabase     String      Database in SQL Server
'   DestService     String      Destination Oracle service
'   DestUserID      String      UserID in the Oracle database
'   DestPassword    String      Password for the user in Oracle
'   sTableName      String      Table to move
'
'
'Returns
'   Boolean         True if sucessful

Dim bError As Boolean
Dim i As Long
Dim oPack1 As New DTS.Package
    'oPack1.LoadFromSQLServer "(local)", , ,
DTSSQLStgFlag_UseTrustedConnection, , , , "Transfer_WinregObjects"

    Set oPackage = New DTS.Package
    oPackage.Name = "CopyRegObject"
    Set oConnection = oPackage.Connections.New("SQLOLEDB.1")

    oConnection.ID = 1
    oConnection.Name = "SQL Connection"
    oConnection.UseTrustedConnection = False
    oConnection.DataSource = srcService
    oConnection.Catalog = srcDatabase
    oConnection.UserID = SrcUserID
    oConnection.Password = SrcPassword
    oConnection.Reusable = True

    oPackage.Connections.Add oConnection
    Set oConnection = Nothing

    Set oConnection = oPackage.Connections.New("ORAOLEDB.Oracle")

    oConnection.ID = 2
    oConnection.Name = "Oracle Connection"
    oConnection.UseTrustedConnection = False
    oConnection.DataSource = DestService
    oConnection.UserID = DestUserID
    oConnection.Password = DestPassword
    oConnection.Reusable = True

    oPackage.Connections.Add oConnection
    Set oConnection = Nothing

    Set oTask = oPackage.Tasks.New("DTSExecuteSQLTask")
    oTask.Name = "DeleteFromTable"

    Set oExecuteSQLTask = oTask.CustomTask
    oExecuteSQLTask.ConnectionID = 2
    oExecuteSQLTask.SQLStatement = "DELETE FROM " & sTableName

    oPackage.Tasks.Add oTask
    Set oTask = Nothing
    Set oStep = oPackage.Steps.New
    oStep.Name = "Step01"
    oStep.TaskName = "DeleteFromTable"
    oPackage.Steps.Add oStep
    Set oStep = Nothing

    Set oTask = oPackage.Tasks.New("DTSDataPumpTask")
    oTask.Name = "CopyTable"

    Set oDataPumpTask = oTask.CustomTask

    oDataPumpTask.SourceConnectionID = 1
    'oDataPumpTask.SourceObjectName = sTableName
    oDataPumpTask.SourceSQLStatement = "SELECT * FROM " & sTableName
    oDataPumpTask.DestinationConnectionID = 2
    oDataPumpTask.DestinationObjectName = """" & UCase(DestUserID) & """."""
& sTableName & """"
    oDataPumpTask.ExceptionFileName = App.Path & "\err.txt"
    Set oTransform =
oDataPumpTask.Transformations.New("DTS.DataPumpTransformCopy")
    oTransform.Name = "Copy" & sTableName
    oTransform.TransformFlags = 63
    oDataPumpTask.Transformations.Add oTransform
    oPackage.Tasks.Add oTask
    Set oTask = Nothing
    Set oStep = oPackage.Steps.New
    oStep.Name = "Step02"
    oStep.TaskName = "CopyTable"
    oPackage.Steps.Add oStep

    Set oPrecedence = oStep.PrecedenceConstraints.New("Step01")
    oPrecedence.PrecedenceBasis = DTSStepPrecedenceBasis_ExecResult
    oPrecedence.Value = DTSStepExecResult_Success
    oStep.PrecedenceConstraints.Add oPrecedence

    Set oPrecedence = Nothing
    Set oStep = Nothing
    'Set oPackage = Nothing

    'oPackage.SaveToSQLServer "(local)", , ,
DTSSQLStgFlag_UseTrustedConnection

    'Exit Function
    oPackage.Execute
    bError = False

    For i = 1 To oPackage.Steps.Count
        Debug.Print oPackage.Steps(i).Name
        If oPackage.Steps(i).ExecutionResult = DTSStepExecResult_Failure
Then
            bError = True
            MsgBox "Error on step " & CStr(i)
        End If
    Next i

    If bError = False Then
        MsgBox "Copy completed sucessfully"
    End If

End Function

 
 
 

1. Error executing a DTS package from another DTS package

I'm trying to execute one package from another using the following ActiveX
script code:

Function Main()

Dim oPkq

Set oPkq = CreateObject("DTS.Package")

oPkq.LoadFromSQLServer "Server","myuser","mypass",,,,,"Old HBdb to New HBdb
for 4 Tables"

'oPkg.GlobalVariables("DatabaseName").Value = "mssdet"

MsgBox oPkg.Name

oPkg.Execute

Set oPkg = Nothing

Main = DTSTaskExecResult_Success

End Function

I get an error at line (MsgBox oPkg.Name) on SQL 2000:

VBS Runtime Error 0

Object required 'oPkg'

Any ideas on what is wrong?

Thanks!

2. Creating an ADO connection to a MS SQL Server DB

3. I want to execute a DTS package from another DTS package

4. Enterprise Manager error: "Internal error, not fully constructed."

5. Execute DTS Package within another DTS Package

6. Can't open previous version?????

7. DTS package not executing an inside DTS package

8. SQL 2000 Analysis Manager

9. Executing DTS packages inside another DTS package

10. Object Model for DTS packages

11. Scheduled job errors with MAPI Logon Error but DTS package can be executed fine

12. DTS Error - Could Not Create Instance of DTS Package