If I understand what you're asking:
You want to be able to manipulate a Date global Variable to where you can enter in the date (if the date isn't coming from the database). Just set up a global variable named "gv_Date" and you can do an ActiveX Script for that with the following:
a=inputbox("What is the date?")
if a <> "" then DTSGlobalVariables("gv_Date").Value=a end if Main = DTSTaskExecResult_SuccessEnd Function
hi dude, thanks 4 your answer.
but i need to do something like this:
select * from table where fieldvalue= <dtsglobalvar1>
u got it?
i need to use an external parameter to find data on my db.
thanks a lot.
put the SQL you want to execute into a stored procedure.
In the DTS Execute SQL step put something like:
EXEC mystoredprocedure @myglobalvar = ?
Then hit the "Parameters" button. Select the global variable you want to put in the parameter.
For some reason, the Execute SQL steps will only see the parameter marker (a question mark) when you are executing a sproc.
I wish it was easier!
One way is to use the xp_cmdshell to call the DTS command line command. For instance, for the command line command...
DTS /Usa /Ppassword /Sserver /Npackage_name /Mpackage_password /A"SQLCatalog:8=dist" /A"SQLTable:8=dist.dbo.co" '
which uses two global variables that are char type (SQLCatalog and SQLTable), you would wrap the above statement in
xp_cmdshell 'DTS /Usa ...etc.'
You can also query a package using...
SELECT * FROM OPENROWSET('DTSPackageDSO','/Usa /Ppassword /Sserver /Npackage_name /Mpackage_password /A"SQLCatalog:8=dist" /A"SQLTable:8=dist.dbo.co", 'Select * from DTSStep_DTSDataPumpTask_1')
where the first argument ('DTSPackageDSO') is the provider info for DTS packages. It seems like performance is not great, but it may be due to the fact my packages refer to a non-sql database that is large.