May 22, 2002 at 3:51 pm
I would like to create a process that uses the current month (Month(GetDate()) as criteria to first remove records from a table going back 3 months from present and then use this same month criteria to extract the most recent data from an external source (IBM-DB2). This process would be scheduled to run at the beginning of a month and mid-month (thus twice a month). Using the function to get the month would eliminate the need to edit the SP or DTS everytime the process needs to be run. Is there a way to use maybe a SP to output the current month to be used in a DTS, or maybe an ActiveX script within a DTS? Could anyone provide some guidance with this procedure?
Thanks,
Brian
May 23, 2002 at 2:57 am
Create a global variable within the DTS package called month. The following ActiveX script will assign the current month to the variable which can then be referenced throughout the package.
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
DTSGlobalVariables("Month").Value = month(now)
Main = DTSTaskExecResult_Success
End Function
Andy
.
May 23, 2002 at 7:30 am
Can this global variable be passed to a stored procedure?
May 23, 2002 at 8:59 am
Yes, in an 'Execute SQL' task specify your sql statement to be something like :-
exec sp_Name ?
Then click the parameters button to assign the global varaible to the ? parameter.
Andy Jones
.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply