Post reply

Need to run Pacakge on 10th of every month

  • May 24, 2018 at 8:20 am

    #353595

    hi,
    I have a requirement to run the package only on 10th of every month , but 10th should not fall on weekends and holidays. If so , we need to run the following weekday .
    Example if 10th is sunday , saturday or monday we need to run on tuesday. 
    or if 10th is holiday like july 4th,memoral day etc...then run on following working/business day.

    So , here is my code but need to check holidays too . Any suggestions please?
    Declare @Rundate date
    SET @Rundate ='12/10/2017'

    SELECT CASE WHEN (DATEPART(dd,@rundate)=10) AND (DATEPART(dw,@Rundate ) not in (7,2,1)) THEN 'Y'
        WHEN (DATEPART(dd,@Rundate )=10) AND (DATEPART(dw,@Rundate)  in (3)) THEN 'Y'
    ELSE 'N' END

    i added the above logic in my package to check not in sunday,saturday and monday ( Monday because our process do not run on monday's)  then"Y" then it will run the next step, if not check for 10 if falls on tuesday then 'y' ( run the process).

    Please suggest if there is better way to check weekends and holidays?

  • May 24, 2018 at 8:25 am

    #1991793

    komal145 - Thursday, May 24, 2018 8:20 AM

    hi,
    I have a requirement to run the package only on 10th of every month , but 10th should not fall on weekends and holidays. If so , we need to run the following weekday .
    Example if 10th is sunday , saturday or monday we need to run on tuesday. 
    or if 10th is holiday like july 4th,memoral day etc...then run on following working/business day.

    So , here is my code but need to check holidays too . Any suggestions please?
    Declare @Rundate date
    SET @Rundate ='12/10/2017'

    SELECT CASE WHEN (DATEPART(dd,@rundate)=10) AND (DATEPART(dw,@Rundate ) not in (7,2,1)) THEN 'Y'
        WHEN (DATEPART(dd,@Rundate )=10) AND (DATEPART(dw,@Rundate)  in (3)) THEN 'Y'
    ELSE 'N' END

    i added the above logic in my package to check not in sunday,saturday and monday ( Monday because our process do not run on monday's)  then"Y" then it will run the next step, if not check for 10 if falls on tuesday then 'y' ( run the process).

    Please suggest if there is better way to check weekends and holidays?

    Haven't checked the code, but assuming it works, how are you going to configure the SQL Agent scheduler?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • May 24, 2018 at 9:03 am

    #1991803

    Phil Parkin - Thursday, May 24, 2018 8:25 AM

    komal145 - Thursday, May 24, 2018 8:20 AM

    hi,
    I have a requirement to run the package only on 10th of every month , but 10th should not fall on weekends and holidays. If so , we need to run the following weekday .
    Example if 10th is sunday , saturday or monday we need to run on tuesday. 
    or if 10th is holiday like july 4th,memoral day etc...then run on following working/business day.

    So , here is my code but need to check holidays too . Any suggestions please?
    Declare @Rundate date
    SET @Rundate ='12/10/2017'

    SELECT CASE WHEN (DATEPART(dd,@rundate)=10) AND (DATEPART(dw,@Rundate ) not in (7,2,1)) THEN 'Y'
        WHEN (DATEPART(dd,@Rundate )=10) AND (DATEPART(dw,@Rundate)  in (3)) THEN 'Y'
    ELSE 'N' END

    i added the above logic in my package to check not in sunday,saturday and monday ( Monday because our process do not run on monday's)  then"Y" then it will run the next step, if not check for 10 if falls on tuesday then 'y' ( run the process).

    Please suggest if there is better way to check weekends and holidays?

    Haven't checked the code, but assuming it works, how are you going to configure the SQL Agent scheduler?

    Hmm ..my logic only checks the weekends , what is i need to check holidays? That was my question 🙂

  • May 24, 2018 at 10:10 am

    #1991839

    You need a calendar table. Build one, stick it in a dba database, query it to run. You could even add flags for days and holidays. Makes life much simpler.
    http://qa.sqlservercentral.com/articles/T-SQL/70482/

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • May 24, 2018 at 10:31 am

    #1991845

    komal145 - Thursday, May 24, 2018 9:03 AM

    Phil Parkin - Thursday, May 24, 2018 8:25 AM

    komal145 - Thursday, May 24, 2018 8:20 AM

    hi,
    I have a requirement to run the package only on 10th of every month , but 10th should not fall on weekends and holidays. If so , we need to run the following weekday .
    Example if 10th is sunday , saturday or monday we need to run on tuesday. 
    or if 10th is holiday like july 4th,memoral day etc...then run on following working/business day.

    So , here is my code but need to check holidays too . Any suggestions please?
    Declare @Rundate date
    SET @Rundate ='12/10/2017'

    SELECT CASE WHEN (DATEPART(dd,@rundate)=10) AND (DATEPART(dw,@Rundate ) not in (7,2,1)) THEN 'Y'
        WHEN (DATEPART(dd,@Rundate )=10) AND (DATEPART(dw,@Rundate)  in (3)) THEN 'Y'
    ELSE 'N' END

    i added the above logic in my package to check not in sunday,saturday and monday ( Monday because our process do not run on monday's)  then"Y" then it will run the next step, if not check for 10 if falls on tuesday then 'y' ( run the process).

    Please suggest if there is better way to check weekends and holidays?

    Haven't checked the code, but assuming it works, how are you going to configure the SQL Agent scheduler?

    Hmm ..my logic only checks the weekends , what is i need to check holidays? That was my question 🙂

    Lacking a table that has all the holiday dates for your company in it, you'd be hard pressed to do that check.   SQL Server does NOT have any internal holiday knowledge or tables, and even if it did, not all holidays are observed by all companies.   If you don't have a table with your company's holiday dates, you'll have to create one, along with a business process to keep it up to date.

    Steve?(aka sgmunson)?:) 🙂 :)?
    Health & Nutrition
    Rent Servers for Income (picks and shovels strategy)

  • May 24, 2018 at 10:36 am

    #1991852

    sgmunson - Thursday, May 24, 2018 10:31 AM

    komal145 - Thursday, May 24, 2018 9:03 AM

    Phil Parkin - Thursday, May 24, 2018 8:25 AM

    komal145 - Thursday, May 24, 2018 8:20 AM

    hi,
    I have a requirement to run the package only on 10th of every month , but 10th should not fall on weekends and holidays. If so , we need to run the following weekday .
    Example if 10th is sunday , saturday or monday we need to run on tuesday. 
    or if 10th is holiday like july 4th,memoral day etc...then run on following working/business day.

    So , here is my code but need to check holidays too . Any suggestions please?
    Declare @Rundate date
    SET @Rundate ='12/10/2017'

    SELECT CASE WHEN (DATEPART(dd,@rundate)=10) AND (DATEPART(dw,@Rundate ) not in (7,2,1)) THEN 'Y'
        WHEN (DATEPART(dd,@Rundate )=10) AND (DATEPART(dw,@Rundate)  in (3)) THEN 'Y'
    ELSE 'N' END

    i added the above logic in my package to check not in sunday,saturday and monday ( Monday because our process do not run on monday's)  then"Y" then it will run the next step, if not check for 10 if falls on tuesday then 'y' ( run the process).

    Please suggest if there is better way to check weekends and holidays?

    Haven't checked the code, but assuming it works, how are you going to configure the SQL Agent scheduler?

    Hmm ..my logic only checks the weekends , what is i need to check holidays? That was my question 🙂

    Lacking a table that has all the holiday dates for your company in it, you'd be hard pressed to do that check.   SQL Server does NOT have any internal holiday knowledge or tables, and even if it did, not all holidays are observed by all companies.   If you don't have a table with your company's holiday dates, you'll have to create one, along with a business process to keep it up to date.

    Ok Got it. Thank you.

  • May 24, 2018 at 10:36 am

    #1991853

    komal145 - Thursday, May 24, 2018 9:03 AM

    Phil Parkin - Thursday, May 24, 2018 8:25 AM

    komal145 - Thursday, May 24, 2018 8:20 AM

    hi,
    I have a requirement to run the package only on 10th of every month , but 10th should not fall on weekends and holidays. If so , we need to run the following weekday .
    Example if 10th is sunday , saturday or monday we need to run on tuesday. 
    or if 10th is holiday like july 4th,memoral day etc...then run on following working/business day.

    So , here is my code but need to check holidays too . Any suggestions please?
    Declare @Rundate date
    SET @Rundate ='12/10/2017'

    SELECT CASE WHEN (DATEPART(dd,@rundate)=10) AND (DATEPART(dw,@Rundate ) not in (7,2,1)) THEN 'Y'
        WHEN (DATEPART(dd,@Rundate )=10) AND (DATEPART(dw,@Rundate)  in (3)) THEN 'Y'
    ELSE 'N' END

    i added the above logic in my package to check not in sunday,saturday and monday ( Monday because our process do not run on monday's)  then"Y" then it will run the next step, if not check for 10 if falls on tuesday then 'y' ( run the process).

    Please suggest if there is better way to check weekends and holidays?

    Haven't checked the code, but assuming it works, how are you going to configure the SQL Agent scheduler?

    Hmm ..my logic only checks the weekends , what is i need to check holidays? That was my question 🙂

    I understood your question, but I was trying to make you think ahead to understand the possible pitfalls of what you are proposing, assuming you are going to run the package using SQL Agent.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply