Executing dts from vb.net

ninel · August 16th, 2005, 03:45 PM

I have a dts package with a global variable(sCallDate) that I assigned in the package properties. The package executes a stored proc that requires the sCalldate variable.

In the Execute SQL Task of the dts package I have the following: EXEC dbo.uspMyStoredProc, but it really should look like this EXEC dbo.uspMyStoredProc @sCalldate

Here's my vb.net code:

Code:

Private Sub cmdSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSubmit.Click
        Dim bExport As Boolean
        Dim sErrorText As String
        Dim ErrorFlag As Boolean
        Dim sCalldate As String

        Try
        lblResult.Text = ""
        ErrorFlag = False
        sCalldate = txtCalldate.Text

        If Len(Trim(sCalldate)) = 0 Then
            lblResult.Text = "Please Select a Date"
            ErrorFlag = True
        End If

        If ErrorFlag = False Then
            bExport = ExecuteDTS()

            If Trim(sErrorText) <> "Success" Then
                lblResult.Text = sErrorText
            End If
        End If

        Catch ex As Exception
             lblResult.Text = ex.Message
        End Try
End Sub

Public Function ExecuteDTS() As Boolean 
        Dim oPkg As DTS.Package2
        Dim oStep As DTS.Step2
        Dim results As Boolean
        Dim sCalldate As String

        sCalldate = "20050811"
        results = True
        oPkg = New DTS.Package2

        oPkg.LoadFromSQLServer(ServerName:="ServerName", ServerUserName:="user", ServerPassword:="pass", PackageName:="MyPackage")

        oPkg.GlobalVariables.AddGlobalVariable("sCalldate", CType(sCalldate, System.String))

        For Each oStep In oPkg.Steps
            oStep.ExecuteInMainThread = True
        Next

        oPkg.FailOnError = True
        oPkg.Execute()

        For Each oStep In oPkg.Steps
            If oStep.ExecutionResult = DTS.DTSStepExecResult.DTSStepExecResult_Failure Then
                results = False
            End If
        Next

        oPkg.UnInitialize()
        oStep = Nothing
        oPkg = Nothing

        ExecuteDTS = results
End Function

The package executes with no errors, but I'm not getting any data in the text file that the package creates.

I just manually executed the package with the a date in the global variable and I didn't get any data.

It seems like the sCalldate variable is not being passed to the stored procedure.

How can I pass the sCalldate variable to the stored procedure?

Thanks,
Ninel