每月使用 SQL 计划执行 SSIS

Question

我有一个SQL 2005 SSIS包，它每月向政府办公室报告，该包在每个月的最后一个星期四到期。我将 SQL Server 设置为在正确的日期运行包。计划报表不是问题。

现在，SSIS 包会创建一个年初至今的报告。我使用了两个变量。 一个 BeginDate，它使用表达式来确定该月的第一天，并将其转换为类似于"5/1/2012"的字符串。同样，还有 EndDate 会吐出今天的日期，例如"5/3/2012"。

有没有办法将 BeginDate 变量设置为上次运行报告的第二天？有没有更好的方法来找出上个月最后一个星期四的日期？

Answer 1

有多种方法可以在我脑海中解决这个问题。

选项 1

由于使用的是 SQL 代理，因此请使用 SQL 代理。创建作业时，单击该框以确保保存历史记录。假设数据库维护策略不会删除过去一个月的作业历史记录，您应该能够编写查询来确定作业步骤上次成功完成的时间。在执行 SQL 步骤中运行此类查询将生成上次成功运行 SSIS 步骤的时间。您需要做的就是将第三个元素的值分配给 EndDate 变量

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

选项 2

创建自定义表，并让您的作业记录该表的最后处理日期。该过程在处理开始时查找该表，并使用最后一个日期作为结束日期。

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;

选项 3

用你喜欢的语言计算一个月的最后一个星期四(.NET、TSQL，甚至可能 SSIS 表达式语言都可以工作，但我不会尝试(

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

选项 4

与选项 1 类似，但使用 SSIS 日志记录，登录到 SQL Server，然后查找上次成功执行日期并将其用作结束日期。

-- 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';