TimeXtender scheduling is designed to work by day or by week.  While it is very flexible and powerful in that context, sometimes certain jobs need to be run at larger time intervals, such as monthly or yearly. An example of this kind of package would be a monthly full reload of the BI environment.

While TimeXtender does not natively support this kind of scheduling, it's possible to get the same functionality through the use of project variables and execution package conditions. This method works by scheduling the execution package to run every day, but only give it permission to run at the desired interval.

Setting up the Project Variable

To set up a project variable, you will need to determine what you want your schedule to look like.  A common requirement might be to run an execution package on the first day of the month or the last day of the month.  We will then need to design a query that will return a fixed value we can use to determine when the run condition is met.

The first day of the month is relatively simple to find, as SQL Server has a function that returns the day of the month, and the first day of the month is always numbered "1."  If we know the current day of the month, then, we can set a condition to run the package when "day of the month" = 1.  We can find that using the following query:

SELECT DAY(GETDATE())

Trying to run an execution package on the last day of the month is a bit trickier, because the last day of the month is not a consistent value.  SQL Server's date functions help us a little, as the EOMONTH function will return what the last day of the month is for a given day.  However, we can't use that value in a condition, since the conditional value must be fixed.

In this case, we can use a slightly more complicated query to tell us whether or not a given date is the last date of its month:

SELECT CASE WHEN CAST(GETDATE() AS DATE) =  EOMONTH(GETDATE()) THEN 1 ELSE 0 END

This query will return only one of two possible values: 1 at the end of the month, and 0 for all other days.  This result can easily be used as a condition.

For instructions on how to set up a dynamic variable, please see the guide here.

Setting up the condition

Once you have set up your execution package and scheduled it to run every day at the time you'd like it to run, we'll need to add a condition that allows it to run on the day we want.  For both of these example queries, we will want to set the condition to allow the package to run when the variable value equals 1.

For instructions on how to set up an execution package usage condition, please see the guide here.

Other considerations

If you are using this method to do a full load or other large load, you may want to disable your normal nightly load while your full load is scheduled to run.  This will prevent an incremental or partial load from running at the same time that your full or complete load is supposed to be executing.  In these cases, allowing the package to run when the variable does not equal 1 would do the trick.

Also keep in mind the TimeXtender Scheduler service only runs one package per project per starting time.  To avoid possible conflicts, be sure stagger the scheduled start times of each of your packages by at least five minutes from one another.  For more information on how the schedule works, please see the guide here.