Monthly Execution of an SSIS Using SQL Schedule

I've got an SQL 2005 SSIS package that does monthly reporting to a government office that is due on the last Thursday of every month. I set up the SQL Server to run the package on the right day. Scheduling the report isn't a problem.

Right now the SSIS package creates a month to date report. I used two variables. A BeginDate which uses an expression to determine the first day of the month and converts it to a string like "5/1/2012". Likewise, there's and EndDate which spits out todays date like "5/3/2012".

Is there a way to set the BeginDate variable to the day after the last time the report was run? Is there a better way to find out the date for the last Thursday of the prior month?

Solution

There are a variety of ways to settle this in my mind.

Option 1

Since you are using SQL Agent, use SQL Agent. When you create your job, click the box to ensure you are saving history. Assuming your database maintenance policies don't remove job history of the past month, you should be able to write a query to determine when the job step last successfully completed. Running a query like this in an Execute SQL Step will yield the last time the SSIS step ran successfully. All you'd need to do is assign the value of the third element to your EndDate variable

-- this query will find the most recent, successful execution of a job
-- named 'Last Thursday Of the Month job' with a job step of
-- 'The SSIS Step'
SELECT
    J.name AS job_name
,   JH.step_name AS job_step_name
,   MAX(msdb.dbo.agent_datetime(JH.run_date, JH.run_time)) AS execution_datetime
FROM 
    msdb.dbo.sysjobhistory JH
    INNER JOIN
        msdb.dbo.sysjobs J
        ON J.job_id = JH.job_id
    INNER JOIN
        msdb.dbo.sysjobsteps JS
        ON JS.job_id = J.job_id
            AND JS.step_id = JH.step_id
WHERE
    JH.run_status = 1
    AND J.name = 'Last Thursday Of the Month job'
    AND JH.step_name = 'The SSIS Step'
GROUP BY
    J.name
,   JH.step_name;

Option 2

Create a custom table and have your job record the last processing date to that table. The process looks to that table at the onset of processing and uses the last date as the end date.

CREATE TABLE dbo.AlmostEndOfTheMonth
(
    -- Can't use date as you're on 2005
    execution_date datetime
);

SELECT 
    MAX(AEOM.execution_date) AS most_recent_execution_date 
FROM 
    dbo.AlmostEndOfTheMonth AEOM;

Option 3

Compute the last Thursday of a month in your favorite language (.NET, TSQL, probably even the SSIS expression language would work but I wouldn't try)

DECLARE
    @daysInWeek int
,   @dayOfWeek int
SELECT
    @daysInWeek = 7
,   @dayOfWeek = 5;

; WITH LAST_DAY_OF_PREVIOUS_MONTH (last_day_month) AS
(
    --http://blog.sqlauthority.com/2007/08/18/sql-server-find-last-day-of-any-month-current-previous-next/
    -- SQL 2012 makes this much easier with EOM and/or datefromparts functions
    SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
)
,   LAST_THURSDAY_REFERENCE (last_thursday, last_day_month) AS
(
    SELECT CAST('2012-01-26' AS datetime), cast('2012-01-31' AS datetime)
    UNION ALL SELECT CAST('2012-02-23' AS datetime), cast('2012-02-29' AS datetime)
    UNION ALL SELECT CAST('2012-03-29' AS datetime), cast('2012-03-31' AS datetime)
    UNION ALL SELECT CAST('2012-04-26' AS datetime), cast('2012-04-30' AS datetime)
    UNION ALL SELECT CAST('2012-05-31' AS datetime), cast('2012-05-31' AS datetime)
    UNION ALL SELECT CAST('2012-06-28' AS datetime), cast('2012-06-30' AS datetime)
    UNION ALL SELECT CAST('2012-07-26' AS datetime), cast('2012-07-31' AS datetime)
    UNION ALL SELECT CAST('2012-08-30' AS datetime), cast('2012-08-31' AS datetime)
    UNION ALL SELECT CAST('2012-09-27' AS datetime), cast('2012-09-30' AS datetime)
    UNION ALL SELECT CAST('2012-10-25' AS datetime), cast('2012-10-31' AS datetime)
    UNION ALL SELECT CAST('2012-11-29' AS datetime), cast('2012-11-30' AS datetime)
    UNION ALL SELECT CAST('2012-12-27' AS datetime), cast('2012-12-31' AS datetime)
)
SELECT 
    *
    -- Thursday is the 5th day of the week, assuming you haven't messed with calendar's start of week
    -- We need to subtract up to 6 days from the end of the month to find the
    -- last Thursday. We can use the mod operator on ensure our dateadd function doesn't modify the
    -- date if the end of the month is actually Thursday, otherwise we want to back it off N days
    -- Examples might be easier to understand
    --  Last day    DayWeek     WeekdayNumber   DaysToSubtract
    --  2012-01-31  Tuesday     3               -5
    --  2012-02-29  Wednesday   4               -6
    --  2012-03-31  Saturday    7               -2
    --  2012-04-30  Monday      2               -4
    --  2012-05-31  Thursday    5               0
    --  2012-06-30  Saturday    7               -2
    --  2012-07-31  Tuesday     3               -5
    --  2012-08-31  Friday      6               -1
    --  2012-09-30  Sunday      1               -3
    --  2012-10-31  Wednesday   4               -6
    --  2012-11-30  Friday      6               -1
    --  2012-12-31  Monday      2               -4
,   dateadd(d, -((@daysInWeek - @dayOfWeek) + DATEPART(dw, LDM.last_day_month)) % @daysInWeek, LDM.last_day_month) AS last_thursday_of_month
FROM 
    LAST_DAY_OF_PREVIOUS_MONTH LDM
    -- Comment the above and uncomment the below to 
    -- evaluate all the dates in the 2012
    -- LAST_THURSDAY_REFERENCE LDM

Option 4

Similar to option 1, but use SSIS logging, log to SQL Server and then look for the last successful execution date and use that as your enddate.

-- this code is approximate, I don't have a 2005 instance about
-- if you've logged to a different database, change the msdb reference
SELECT
    max(starttime) AS execution_datetime
FROM
    msdb.dbo.sysdtslog90 L
WHERE
    L.event = 'PackageStart'
    AND L.source = 'MyPackage';