0

I create DTS Package and assign variables on it in VB6.0...I need to check if my connection to my database is successful using DTS package that I created..I got an error message "Argument not optional"...Please help me

Module 1

 Option Explicit
    Public goPackageOld As New DTS.Package
    Public goPackage As DTS.Package2

    Public Function executeEmployee2(dbUserID As String, dbCatalog As String,dbSource As String, dbPassword As String, _
    dbSourceIP As String, dbSourceUserID As String, dbSourceDatabase As String, _
    dbDestinationIP As String, dbDestinationceUserID As String, dbDestinationDatabase As String)

        Set goPackage = goPackageOld

        goPackage.Name = "New Package"
        goPackage.Description = "DTS package description"
        goPackage.WriteCompletionStatusToNTEventLog = False
        goPackage.FailOnError = False
        goPackage.PackagePriorityClass = 2
        goPackage.MaxConcurrentSteps = 4
        goPackage.LineageOptions = 0
        goPackage.UseTransaction = True
        goPackage.TransactionIsolationLevel = 4096
        goPackage.AutoCommitTransaction = True
        goPackage.RepositoryMetadataOptions = 0
        goPackage.UseOLEDBServiceComponents = True
        goPackage.LogToSQLServer = False
        goPackage.LogServerFlags = 0
        goPackage.FailPackageOnLogFailure = False
        goPackage.ExplicitGlobalVariables = False
        goPackage.PackageType = 0

    Dim oConnProperty As DTS.OleDBProperty
    Dim oConnection As DTS.Connection2

    Set oConnection = goPackage.Connections.New("SQLOLEDB")

        oConnection.ConnectionProperties("Persist Security Info") = True
        oConnection.ConnectionProperties("User ID") = "dbUserID"
        oConnection.ConnectionProperties("Initial Catalog") = "dbCatalog"
        oConnection.ConnectionProperties("Data Source") = "dbSource"
        oConnection.ConnectionProperties("Application Name") = "DTS   Import/Export Wizard"

        oConnection.Name = "Connection 1"
        oConnection.ID = 1
        oConnection.Reusable = True
        oConnection.ConnectImmediate = False
        oConnection.DataSource = "dbSourceIP"
        oConnection.userID = "dbSourceUserID"
        oConnection.ConnectionTimeout = 60
        oConnection.Catalog = "dbSourceDatabase"
        oConnection.UseTrustedConnection = False
        oConnection.UseDSL = False
        oConnection.Password = "dbPassword"

    goPackage.Connections.Add oConnection
    Set oConnection = Nothing

    Set oConnection = goPackage.Connections.New("SQLOLEDB")

        oConnection.ConnectionProperties("Persist Security Info") = True
        oConnection.ConnectionProperties("User ID") = "dbUserID"
        oConnection.ConnectionProperties("Initial Catalog") = "dbCatalog"
        oConnection.ConnectionProperties("Data Source") = "dbSource"
        oConnection.ConnectionProperties("Application Name") = "DTS  Import/Export Wizard"

        oConnection.Name = "Connection 2"
        oConnection.ID = 2
        oConnection.Reusable = True
        oConnection.ConnectImmediate = False
        oConnection.DataSource = "dbDestinationIP"
        oConnection.userID = "dbDestinationceUserID"
        oConnection.ConnectionTimeout = 60
        oConnection.Catalog = "dbDestinationDatabase"
        oConnection.UseTrustedConnection = False
        oConnection.UseDSL = False
        oConnection.Password = "dbPassword"

    goPackage.Connections.Add oConnection
    Set oConnection = Nothing

    Dim oStep As DTS.Step2
    Dim oPrecConstraint As DTS.PrecedenceConstraint

    Set oStep = goPackage.Steps.New

        oStep.Name = "Delete from Table [TrainingDb].[dbo].[employee] Step"
        oStep.Description = "Delete from Table [TrainingDb].[dbo].[employee]  Step"
        oStep.ExecutionStatus = 1
        oStep.TaskName = "Delete from Table [TrainingDb].[dbo].[employee] Task"
        oStep.CommitSuccess = False
        oStep.RollbackFailure = False
        oStep.ScriptLanguage = "VBScript"
        oStep.AddGlobalVariables = True
        oStep.RelativePriority = 3
        oStep.CloseConnection = False
        oStep.ExecuteInMainThread = False
        oStep.IsPackageDSORowset = False
        oStep.JoinTransactionIfPresent = False
        oStep.DisableStep = False
        oStep.FailPackageOnError = False

    goPackage.Steps.Add oStep
    Set oStep = Nothing

    Set oStep = goPackage.Steps.New

        oStep.Name = "Copy Data from employee to [TrainingDb].[dbo].[employee] Step"
        oStep.Description = "Copy Data from employee to [TrainingDb].[dbo].[employee] Step"
        oStep.ExecutionStatus = 1
        oStep.TaskName = "Copy Data from employee to [TrainingDb].[dbo].[employee] Task"
        oStep.CommitSuccess = False
        oStep.RollbackFailure = False
        oStep.ScriptLanguage = "VBScript"
        oStep.AddGlobalVariables = True
        oStep.RelativePriority = 3
        oStep.CloseConnection = False
        oStep.ExecuteInMainThread = False
        oStep.IsPackageDSORowset = False
        oStep.JoinTransactionIfPresent = False
        oStep.DisableStep = False
        oStep.FailPackageOnError = False

    goPackage.Steps.Add oStep
    Set oStep = Nothing

    Set oStep = goPackage.Steps("Copy Data from employee to [TrainingDb].[dbo].[employee] Step")
    Set oPrecConstraint = oStep.PrecedenceConstraints.New("Delete from Table [TrainingDb].[dbo].[employee] Step")
        oPrecConstraint.StepName = "Delete from Table [TrainingDb].[dbo].[employee] Step"
        oPrecConstraint.PrecedenceBasis = 1
        oPrecConstraint.Value = 0

    oStep.PrecedenceConstraints.Add oPrecConstraint
    Set oPrecConstraint = Nothing

    Call Task_Sub1(goPackage)
    Call Task_Sub2(goPackage)

    goPackage.Execute
    tracePackageError goPackage
    goPackage.UnInitialize
    Set goPackage = Nothing
    Set goPackageOld = Nothing

    End Function

Form1

Private Sub Form_Load()
    If executeEmployee2 >= 0 Then
    MsgBox ("Connected")
    Else
    MsgBox ("Not Connected")
    End If

Please help me I'm still new in programming especially VB6.0

2
Contributors
1
Reply
26
Views
10 Months
Discussion Span
Last Post by Lee_14
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.