Using current month as input to scheduled DTS

  • 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?



  • 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



  • Can this global variable be passed to a stored procedure?

  • 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