Running DTS Package from VB.Net but need to pass parameters

1 Feb 2006 5:50 PM

John

I can execute the DTS Package no problem. The thing I can't get working is
that there is a stored procedure within the DTS package that need 2
parameters passed to it. I thought that if I used the code to add a global
variables to the DTS package those would be passed to the stored procedure
but that did not work (Put this code in *** below). Any help would be
appreciated.

Thanks,
John

Here is the code I have so far:

        Dim oPkg As DTS.Package2
        oPkg = New DTS.Package2

        oPkg.LoadFromSQLServer("JCSDEVSERV", "sa", "2techy4u!",
DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection _
            , , , , _
            "WTSCGC Export File")

        ***oPkg.GlobalVariables.Item("Param1").Value = 2
        ***oPkg.GlobalVariables.Item("Param2").Value = "01/22/2006"

        oPkg.Execute()
        oPkg.UnInitialize()

        oPkg = Nothing

1 Feb 2006 8:17 PM

Brad Roberts

One idea is to create a table called DTSPackageParameters which has
PackageName, ParameterName, ParameterValue, parhaps all string fields, that
is populated by the Stored Proc. The DTS package could delete the parameters
once used...just a thought...

Show quote

"John" wrote:

> I can execute the DTS Package no problem. The thing I can't get working is
> that there is a stored procedure within the DTS package that need 2
> parameters passed to it. I thought that if I used the code to add a global
> variables to the DTS package those would be passed to the stored procedure
> but that did not work (Put this code in *** below). Any help would be
> appreciated.
>
> Thanks,
> John
>
>
> Here is the code I have so far:
>
>
>         Dim oPkg As DTS.Package2
>         oPkg = New DTS.Package2
>
>         oPkg.LoadFromSQLServer("JCSDEVSERV", "sa", "2techy4u!",
> DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection _
>             , , , , _
>             "WTSCGC Export File")
>
>
>         ***oPkg.GlobalVariables.Item("Param1").Value = 2
>         ***oPkg.GlobalVariables.Item("Param2").Value = "01/22/2006"
>
>
>         oPkg.Execute()
>         oPkg.UnInitialize()
>
>         oPkg = Nothing

1 Feb 2006 8:25 PM

Brad Roberts

Sorry, after re-reading all this I think I may have been off-base a little.
However, your idea of global variables used a parameters to the stored
procedure seems like it is do-able.