在 SSIS 执行 SQL 任务中传递上个月的第一天和最后一天 [英] Pass first and last day of the previous month in SSIS Execute SQL Task

问题描述

我需要在ssis中使用execute sql任务组件执行一个存储过程，SP参数应该是上个月的第一天和上个月的最后一天.请告诉我过程.

I need to execute a stored procedure using execute sql task component in ssis and the SP parameters should be first day of previous month and last date of previous month. Please let me know the process.

目前手动运行，下面是开发人员给我的Oracle SP，本月使用SSIS中的Execute SQL任务运行:

Currently running manually and below is the Oracle SP given by developer to me to run using Execute SQL task in SSIS for this month:

开始

SP_TEST('1-Jan-2021','31-Jan-2021');

SP_TEST('1-Jan-2021','31-Jan-2021');

结束；谢谢

推荐答案

我会用三个 DateTime

我需要知道的第一件事是现在是什么?".我可以使用 GetDate() 但我提倡使用系统变量 StartTime 因为该值在包的整个执行过程中是一致的，而每次检查时都会评估 getdate.假设您的包运行了 2 分钟，并于 2021-03-31 的 11:59 开始运行.套餐开始时，上个月是 2 月，但完成时是 3 月.使用 @[System::StartTime] 可以解决这个问题.

The first thing I need to know is "what is now?". I can use GetDate() but I advocate for using the System variable StartTime as the value is consistent for the entire execution of your package whereas getdate is evaluated every time you inspect it. Assume your package runs for 2 minutes and starts at 11:59 on 2021-03-31. When the package starts, the previous month is Feb but when it finishes, it's March. Using @[System::StartTime] solves that problem.

创建一个名为 PreviousMonth 的变量并使用以下表达式

Create a variable called PreviousMonth and use the following expression

DATEADD( "Month", -1, @[System::StartTime])

这将从我们当前的日期减去一个月，结果为 2021-01-17

That will subtract a month from our current date resulting in 2021-01-17

既然我们知道上个月是什么，那么这个月的第一个月是容易的"；虽然看起来会很乱.这个变量我称之为 PreviousMonthFirst

Now that we know what last month is, the first of the month is "easy" although it's going to look messy. This variable I call PreviousMonthFirst

(DT_DATE) ((DT_WSTR, 4) YEAR( @[User::PreviousMonth]) + "-" + (DT_WSTR, 2) MONTH( @[User::PreviousMonth]) + "-1")

让我们分片来看.第一部分从上个月中提取年份并转换为字符串.

Let's look at that in pieces. First piece extracts the year from the previous month and converts to a string.

(DT_WSTR, 4) YEAR( @[User::PreviousMonth])

既然我们有 2021 作为字符串，让我们添加一个破折号分隔符.字符串连接是 + 运算符

Now that we have 2021 as string, let's add in a dash delimiter. String concatenation is the + operator

+ "-" +

现在我们有 2021- 所以下一步是重复年份逻辑但对于月份

Now we have 2021- so the next step is to repeat the year logic but for month

(DT_WSTR, 2) MONTH( @[User::PreviousMonth])

我们现在已经构建了 2021-1，我们可以简单地将破折号 1 连接到字符串

We now have 2021-1 built out and we can simply concatenate the dash one to the string

+ "-1"

太好了，我们已经构建了 2021-1-1 的字符串，我们需要做的就是将所有字符串转换为 DT_DATE 类型，这就是为什么以上所有内容都用演员.

Excellent, we have built the string of 2021-1-1 all we need to do is cast all of that string to a type of DT_DATE which is why all of the above is wrapped with a cast.

(DT_DATE) (....)

另一种方法是使用日期数学减去一个月中的天数，使其成为一.使用 dateadd 减去 -1* 天.几天?不管我们现在有多少天，但加 1 以获得第一个

An alternative approach would be to use date math to substract away the days in the month to make it one. Use dateadd to subtract, -1*, days. How many days? However many days we currently have but add 1 back to get the first

(DT_DATE) DATEADD("DAY", -1 * DAY( @[User::PreviousMonth]) +1,  @[User::PreviousMonth])

我首先进行了字符串操作，但这个也可以.

I reached for the string manipulation first but this one works as well.

虽然我可以在每个月的几天内编写规则，但我会让图书馆来完成这项工作.将一个月的第一天添加到 2 月 1 日.然后减去一天，这就是我用于 PreviousMonthLast

While I could code in the rules for days of month, I'm going to let the libraries do the work. Add a month to the first of the month resulting in Feb 1. Then subtract a day and so that's the formula I use for PreviousMonthLast

DATEADD("Day", -1, DATEADD("Month", 1,  @[User::PreviousMonthFirst]))

在所有这些中需要考虑的一个重要事项是您的系统是否只需要日期或日期和时间.PreviousMonthLast 的值为2021-01-31"；但是，如果您的程序需要日期和时间，那么它会将日期和时间强制转换为 1 月 31 日午夜，这将使您损失 23 小时 59 分 59 秒以及您的系统以毫秒为单位的精确度.

An important thing to consider in all of this is whether your system expects date only or date and time. The value of PreviousMonthLast will be "2021-01-31" but if your procedure expects date and time, then it will coerce that to midnight of Jan 31 which will lose you 23 hours, 59 minutes, 59 seconds and however much precision your system has in milliseconds.

如果时间很重要，那么在本节中将 Day 换成 Second.

If time does matter, then swap out Day for Second in this section.

如果您的过程需要像1-jan-2021"这样的日期文本表示，您将不得不编写自己的逻辑，因为表达式语言不支持 DateName 之类的函数.或者只是使用一个脚本任务和几行 .NET 来推导上个月的第一天和最后一个月.

If your procedure expects a text representation of date like '1-jan-2021' you're going to have to write your own logic as the expression language does not support a DateName like function. Or just use a script task and a few lines of .NET to derive first and last of the prior month.

这篇关于在 SSIS 执行 SQL 任务中传递上个月的第一天和最后一天的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！