Executing an SSIS Package Based on a Variable

On my current client, I’m using Microsoft SQL Server 2008 R2 and SSIS, Integration Services. I’m updating the data warehouse fact tables every hour, but there is a fact table that only needs to be updated once per day. This is mainly because

it has a lot of records, and the client does not need an hourly update, so we decided to update this table every night at midnight. I could have just scheduled this package in the SQL Agent as a new job, but I wanted to retain the Batch and Log information that I am using when I run the normal hourly packages. I have a master package that is executing children packages, using parent variable and a batch start time and end time, I want to use this same information when executing the new daily package. I’m sure there are several different ways of doing this, but here’s my approach: Basically, you are executing a package task based on a constraint which is a boolean that is returned from a stored procedure.  

1. Create a SSIS Configuration entry. New variable named “HourToExecuteDailyPackage”. (Silly name, but just go with it.)
2. The value in the configuration table is the hour of the day to execute this package on a 24 hour scale, so 0 is midnite, 3 is 3AM, and 15 is 3pm.
3. Create a stored procedure that receives the variable and returns TRUE/FALSE if the current time is within 15 minutes of the variable. (The 15 minutes buffer is just a way to make sure that your package is getting executed within 15 minutes of when the master package was started.)

  It can be shorter or longer process based on when this package gets executed. Here’s what the stored proc looks like: /*********************************************************************** ********************************/ CREATE proc [dbo].[sp_TimeToExecute] ( @HourOfTheDay int , @MergeBoolean int OUTPUT ) AS DECLARE @NbrOfMinutes int – Error checking. Any integer outside the 24 hour range gets converted to midnight (aka 0 hour) IF @HourOfTheDay > 24 OR @HourOfTheDay < 0 BEGIN SET @HourOfTheDay = 0 END – Determine the number of minutes between the parameter and current timestamp SET @NbrOfMinutes = datediff(mi ,dateadd(ss,(@HourOfTheDay*3600)+(0*60)+0,0) ,dateadd(day, -datediff(day, 0, GETDATE()), GETDATE())) – If the number of minutes is less than a set interval, then set the boolean flag to true SET @MergeBoolean = 0 – This is a buffer. You can make it more or less. It all depends on when this package is getting executed and how long your entire master package process takes. IF @NbrOfMinutes <= 15 BEGIN SET @MergeBoolean = 1 END / *******************************************************************************************************/

4. Set up a SQL Task that has a constraint on it, pointing to a Execute Package Task.

Example:     Your SQL Task will call the stored procedure above, sending in the HourOfTheDay variable, which is the Configuration variable HourToExecuteDailyPackage. It contains an output variable “MergeBoolean”, which is then used in the constraint.    

5. You should now be able to manipulate the Configuration table with an integer and execute this stored procedure on demand.

  Thanks for reading and look for my next post!