November 28, 2011 at 4:58 am
Hello All.
I have been tasked with building a SSIS packeage that will generate month end extract showing the last working date for that month or -4.
Any help will be appreciated with the expression or solution
Thanks
November 28, 2011 at 5:18 am
Can u plz explain a bit in detail 🙂 ....
Need help? Help us help you.
November 28, 2011 at 5:22 am
The best way would be a calendar table.
http://qa.sqlservercentral.com/articles/T-SQL/70482/
http://qa.sqlservercentral.com/articles/T-SQL/70743/
http://qa.sqlservercentral.com/articles/Test-Driven+Development/71075/
Otherwise you're left with a nice big formula that can only return the last week-day of the month but doesn't adjust for holidays.
DECLARE @theDate DATETIME
SET @theDate = GETDATE()
SELECT DATEADD(DAY, - DATEDIFF(DAY, 0, DATEADD(MONTH, 1 + DATEDIFF(MONTH, 0, @theDate), - 1)) % 7 / 5 - DATEDIFF(DAY, 0, DATEADD(MONTH, 1 + DATEDIFF(MONTH, 0, @theDate), - 1)) % 7 / 6, DATEADD(MONTH, 1 + DATEDIFF(MONTH, 0, @theDate), - 1)) AS lastWeekDay
November 28, 2011 at 6:20 am
It will include the bank holidays , the extract just needs to show the last working day of the month
Many Thanks
November 28, 2011 at 6:40 am
Hello Should i make this a sql task after or before the data flow task
November 28, 2011 at 1:10 pm
Here are some options. All should use a calendar table.
1-Create a stored procedure that does the date logic and then conditionally executes a package via DTEXEC. Run it every day.
2-Do the logic in the SQL server job itself. http://qa.sqlservercentral.com/Forums/Topic695226-146-1.aspx. Again run it every day.
3-Do the logic in the package before the data flow task. See http://msdn.microsoft.com/en-us/library/ms140153(v=SQL.90).aspx or search 'precedence contraints ssis'
4-Run the package every day to build up a testing record. It may fail on the day you want it to run. You may have been able to detect/fix this failure if it was running every day. Then conditionally deliver the output to your users on the day of your choosing.
November 28, 2011 at 1:33 pm
Working with dates is always a challenge, and even simple questions like this don't have ready-made solutions available.
The answer, as the other respondents have stated, is to create a calendar table. Your organization may already have a calendar table somewhere. If it does, then it is almost certain to have a column that indicates whether or not a specific day is a working day.
If not, create a calendar table. Keep it really simple. Just have the date, a day-of-week column and a bit column that is TRUE if a day is a work day and FALSE if a day is not a work day. As you work with the calendar table, I am sure that you will find other columns to add. Publish the calendar table so that others in your company can use it, and make sure everyone's copy of the table stays in sync with a master copy.
The problem is that you always have exceptions when working with date and time data. For example, in my company, we have well established rules for when paid holidays are observed when those holidays fall on a weekend. (If it falls on Saturday, observe it on Friday. If it falls on Sunday, observe it on Monday.) This works great, except that HR decreed that this year, we will observe New Year's day on Friday, even though the holiday falls on Sunday and would normally be observed on Monday. (They want the holiday to fall in 2011 instead of 2012 for accounting reasons.)
This kind of exception is almost impossible to handle in a T-SQL expression, but easy to handle in a calendar table. You just update the two records in question.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply