Send a email if a file has not loaded 7 days afetr the 24 of the month
-
Every month on the 24th we receive a invoice for the months purchases. It is always generated for the 25th through 24th. It is not actually sent till the next business day after the 24th which can be up to 7 days depending on holidays and weekends. What I need to do is send a notification email if by the end of the 7th day the Invoice is not loaded. The load is a SSIS package. This is what I have but I am stuck. It used to always run on the 28th and send the email if file wasn't found but now they want me load ASAP and to do this 7 day check. Thank for your help.
-
OK, but what is your question?
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Sorry I need to figure out the logic, "How do I determine if it is the 7th day after the billing cycle and we have not loaded a invoice file." 7 days after the 24th in February is March 3rd but in January it is the 31th so I cant use the current month. Every time I think I have a solution I am faced with a that wont work in all situations. For instance If I figure out the date of the last loaded invoice add 37 days that wont work for all months. I was just looking for ideas of how I would do the 7 day test in SSIS. Maybe someone has done this and can give me a few pointers.
-
Presumably you can write the query to get 'date last invoice loaded'.
Here is a query to get 'last billing date':SELECT
LastBillingDate = IIF(DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 24) < GETDATE()
, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 24)
, DATEFROMPARTS(YEAR(DATEADD(MONTH, -1, GETDATE())), MONTH(DATEADD(MONTH, -1, GETDATE())), 24));
So now you can write a query to determine the number of days from 'last billing date' to 'date last invoice loaded'.
If this number is > 7, send the e-mail.The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
The query didn't work TSQL didn't like the DATEFROMPARS. I think see what you are going for there. That gives me an idea. This is probably not the easiest way but, if I took the last invoice date on the Invoice table and added one month. Say the date is 6/24/2017 add one month that makes it 7/24/2017. Then add 7 days to that and it return 7/31/2017. If todays date is greater than that date it is time to send the email. The nice thing is I could run the load every day it will never hit unless the invoice is 1 month and 7 days past due. The query below returns 8/31/2017 which is right because I have already loaded this months Invoice. Now the question is what is the best set up the branch in SSIS.
SELECT DATEADD(day, +7, DueDate) as PastDueDate
FROM
(
SELECT DATEADD(Month, +1,MAX(File_Date)) AS DueDate
FROM ACS_InvoiceHeader
) as a -
One problem with my solution is if it runs everyday. It will send a email everyday until the invoice is loaded. So if it takes three days everyone will be emailing me everyday asking why its taking so long. I guess that's one way to get attention , lol
- wburke 85918 - Wednesday, July 26, 2017 3:21 PM
Very true and possibly a good thing, especially if you can get them to e-mail the person responsible for producing the data, rather than you!
Sorry about DATEFROMPARTS, that's for 2012 onwards.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply