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

[Guest]

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

 

[Guest]

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...

 

[Guest]

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.