3 Replies Latest reply on Sep 10, 2018 9:37 AM by Michael Hesser

    Scheduling data extract refreshes in Server for specific dates

    Claire Marshall

      Hi everyone,

       

      I am trying to automate the refreshes for my data extracts by publishing them to server and setting a schedule. I need to choose specific dates to refresh on rather than every Monday, or every 25th, or 26th etc. I need to schedule in line with a 4-4-5 fiscal calendar format - so the first month is 4 weeks long, then the 2nd is 4 weeks and the 3rd month is 5 weeks, causing the first date of each fiscal month to be different.

       

      So month 1 started this year on the 1st April, month 2: 29th April, month 3: 27th May, month 4: 1st June and so on.

       

      And ideas? Ideally I'd like to schedule them so that they refresh at the start of the new fiscal month - however as of all the different days, this makes it incredibly tricky. Currently I can only see in the version I have a way to set a schedule on set days of the week, or on set days of the month.

       

      Many thanks for any help!

        • 1. Re: Scheduling data extract refreshes in Server for specific dates
          Michael Hesser

          Hi Claire;

          I'm not a Server expert, but I think Server limits you to set schedules (weekly, monthly, daily, etc).

          There's a way you might be able to cheat this-- though you'll need to do a little programming.

           

          Start by creating a simple BAT file that will refresh your extract via TABCMD.

           

          Next, use a platform like good ole Access-- along with a table of your refresh dates-- to call the BAT file and check off that the refresh has been completed for that date.

           

          Last, use Task Manager to open and execute the Access query each day.

           

          It's an admittedly messy solution (and the moving parts give multiple points of potential failure) , but it may work for you.

           

          It might be easier to skip the Access part all together and instead poll the current date and use branching IF/OR statements within the BAT file itself, then execute the BAT file daily using Task Manager.

           

          One limitation of Task Manager is that (I believe) it requires your computer to be on. If you shut it down over the weekend, your extract may not be refreshed

           

          Others may have better bullet-proof ideas... but I hope this gives some inspiration! Let me know if you have questions and good luck to you! --Michael

          • 2. Re: Scheduling data extract refreshes in Server for specific dates
            Claire Marshall

            Hi Michael,

             

            Thanks for this - it is super helpful! This could be a way round the restrictions on Server's scheduling.

             

            So - using Task Manager to schedule, no problems, I can do that. Creating a BAT file for the refresh - done and working all fine.

             

            What I'm struggling with still is the date part - if you could help me, that would be greatly appreciated!

             

            You mention two ways - via access or using an IF/OR statement. I'm not familiar with using Access queries, but I will look them up now, or adding in the IF statement, I'm not sure how to write it in the CMD. Any suggestions or articles you could point me in the right direction of?

             

            Many thanks!

            • 3. Re: Scheduling data extract refreshes in Server for specific dates
              Michael Hesser

              I was able to find details on getting the current date here...

               

              Windows Batch File (.bat): Get Current Date as MMDDYYYY

               

              IF statements are detailed here:

               

              https://commandwindows.com/batchfiles-branching.htm

               

              While I've not used it, it seems like if you have the MMDDYYYY one could set up a number of IF statements to trigger a refresh using just a BAT statement.

               

              I created an Access program to automatically copy key programs. While this required A LOT more programming, I *was* able to include a "save success" flag which means even if my computer is shut off, it recognizes the file wasn't saved at the correct time and saves it ASAP. This, however, requires lots of programming.

               

              If this solves your issue or is helpful, please mark it as so

               

              Good luck and let me know if I can assist more. --Michael