Enabling SSIS Packages On Demand
In work with a current client, they required that the execution and processing of certain packages to be configurable via a web interface. In my case, I wanted to proactively enable/disable the importing of certain data sources. If a source system goes down or is in maintenance mode, I need to be able to shut off the import process for that one and still updated the data warehouse. By using SSIS Configurations, Variables, SSIS package expressions, I was able to allow an administrator to control which packages (i.e. the data source importing) were going to be processed.
Trial Run: Using the Precedence Constraint
One possible solution that I considered was using the precedence constraint to determine if the package should be executed or branch to another path (or ignore all together). Something like this:
Ignore the Script components, but think of them as Packages calling other Packages
However, this didn’t really meet my requirements. I just needed to read from SSIS Config table and then enable/disable the child package.
The Solution
Here’s my solution:
1. Create the SSIS package variables
2. Your SSISConfiguration table will be updated with the variables.
3. Update your SSIS package to contain sequence containers and any dependencies. In my case, I needed run SalesForce updates after the other stuff.
* Notice the pink triangle in the upper left corner. This is the notification from BIDS Helper to let the developer know that there is an expression used in the property section of the package
4. Update the Package Component Expressions section to Enable/Disable the package when reading from the Variable. I need to use the “!” expression to reverse out the boolean value. I am using the DISABLE property of the component, so if it is TRUE, then that means DISABLE it, but I want the reverse.
5. Now update your SSIS Configurations with the property boolean values and run the package. You will see that the variable will enable/disable the execution of the package when you need it.
I hope you’ve found this helpful. Feel free to contact me at info@statslice.com if you have any questions and look forward to my next post.
Justin Taylor
Justin Taylor has been architecting, building and managing business intelligence projects since 1999, including solutions for several Fortune 500 companies. When Justin joined StatSlice, he was looking for an opportunity to make a major contribution to the company and utilize his technology experience to help clients solve their business problems. Justin has broad experience in data warehousing, business analytics, reporting and dashboards, and data architectures. He enjoys opportunities to speak at technology events, conferences and training. He holds several Microsoft certifications include MCT (trainer), MCTS and MCITP certifications in SQL Server BI, and Certified Scrum Master through ScrumAlliance.org.