SSIS Solution Framework
In the field, I have seen very many projects simply thrown together without consideration for design patterns, and being extremely poor both for development processes as soon as the team grows beyond 1 person, and also becoming a maintenance nightmare when they are deployed to production.
What I will take you through in this series of 5 articles is an SSIS Solutions Framework pattern that I have developed over the last few years, which has been tuned to allow for multiple members of a team to work in parallel during development, and also designed to assist in the deployment process and ongoing maintenance.
The series contents are as follows:
(If you're wondering, I developed my layout in a product by MindJet called Mind Manager - awesome product, I do all my planning in this format)
1 Prerequisites
This article presupposes that you’ve read Articles 1 and 2. In addition, reading up on "Expressions" and precedence constraints will be helpful, as they are covered in the article, but not in depth.
2 Layer of Abstraction
In Business Intelligence, as in application development, we often want to have an abstraction layer. There are very many theoretical reasons why, but in practise, one of the biggest is the concept of reusability.
We may have a piece of code that is reused across multiple packages, for instance the orphan handling of a particular dimension. While it is eminently possible to copy and paste, we would much rather have a single place to maintain the code.
2.1 SSIS Execute Package Task
This leads us to the SSIS Execute Package Task. This task allows us to execute another package, which package is specified by a connection.
OK, so let us add a new Execute Package Task.
Copy the package from the previous article, and name it "ExecutePackageAbstraction"
Drag an "Execute Package Task" onto the design surface, and name it "EPT_File"
Double click on the task to edit it
Select "Package" on the left, and then choose "File System" from the drop down that says "SQL Server", as we are going to start off by building a package that executes a package in the file system.
Select "New Connection" from the connection drop down.
Click "Browse" on the screen to open a file explorer.
Now, and this is not essential, but will make for much less confusion later : do not browse to the folder where you designed your packages, instead browse to your build folder.
At this point, select the "Template Package" - it doesn't currently do anything, so is a good choice.
Now, you'll have 3 connections set up : A SQL Server connection to MSDB (this will be important later) , a SQL Server connection set up to DWOps, and a new connection, "Template Package.dtsx".
In addition, you have a variable called "ExecutePackageName" (If you are missing ANY of these, visit Article 2 to create)
To start off, rename "Template Package.dtsx" to "Execute File"
2.2 Variables
Now, edit the variable "ExecutePackageName" (View-->Other Windows-->Variables if it's not visible), and change the value to "Template Package"
Next, click "Add", and add a new variable. Call it "BuildFolder", make it a string, and give it the value that is your build folder
2.3 Expressions
Expressions will be a new topic to many of you, and are, to some extent, beyond the scope of this article. Brian Knight has an excellent whitepaper on them, so I suggest some reading.
Suffice it to say, SSIS Expressions are an embedded language that allow you to programmatically alter values within an SSIS package.
To begin, click on the "ExecutePackage" connection. If the properties window is not visible, right click and select "Properties"
Click on the "Expressions" box, and then the ellipse (...) .
This brings up a box where you can choose which property to edit, and what value to assign it.
Select "Connection String" from the "Property" box
Now, click on the ellipse next to "Expression"
This Expression editor allows you to define how the connection string should be defined. Open up the variables tree, and first drag "User::BuildFolder" down to the "Expression" box.
Add + "\\" + to add a trailing backslash, and then drag down "User::ExecutePackageName" to fully define the expression as File path + package name. Add a + ".dtsx" so that the extension doesn't need to be specified.
You can now click "Evaluate Expression" to ensure your syntax is correct.
In a production solution, I would always add code to check if a backslash or extension exists before adding it.
But we have a solution. I want you to now add a new “Execute Package Task”
3 Package Configuration
3.1 Setting the location and package dynamically
Up to this point, we haven't actually made this package "dynamic" in any sense. So, let’s set it up to have a configurable location. We'll use the "Package Configurations" we learnt in the previous article. Right click on the design surface, and choose "Package Configuration"
Add a new configuration, and choose "SQL Server" as the configuration type. Choose the "DWOps" connection defined previously, and [dbo].[SSIS Configurations] as the table.
We want to create a new Configuration filter, so type in "BuildFolder". Press Next.
Scroll up to the "Variables --> BuildFolder" section, and make the "Value" configurable.
Press "Next", name the configuration "BuildFolder", and finish. Now we've made the folder that the package resides in configurable.
3.2 Variable "ExecutePackageName"
Next step is to make the package that is executed dynamic. In my previous example, we had done this already, but as this is a very important point, I am going to cover it again. If you already have a Package Configuration for "ExecutePackageName", you can either skip these steps, or delete the
Right click on the design surface, select "Package Configurations", and create a new configuration.
Choose "Parent package variable", and the name is "ExecutePackageName". THIS NAME IS VERY IMPORTANT. When you are calling from another package, this is the name of the variable in the parent package will need to set to pass values to this package.
Click next
Choose to set the "ExecutePackageName" value. Also, please note that you can only set ONE value here. Click next, and set the configuration name to "ExecutePackageName"
4 SSIS Packages in MSDB
4.1 Deploying to MSDB
OK, so at this point, let us say that we have a complete package. All that it does at this point is execute a package: this package is in a folder, and the name of the package is in a variable.
So far, so good. Now we want to deploy these packages to MSDB, as we go through a UAT process. Start off by building your project.
Open up SQL Server Management Studio, and connect to your integration services instance.
Connect to integration services, and open up the "Stored Packages" and "MSDB" folders.
Right click on "MSDB", and choose import.
Choose to import from "File System". Click on the ellipse next to the "Package Path".
Browse to your build folder, and select the package we just built.
Click OK.
Now you will see a package in the root folder of your MSDB folder. For the purposes of this article, that's fine, but in a production environment, each project would have its own folder.
Now repeat this process, but for the template package as well.
4.2 Adding a connection to an MSDB package
Return to BIDS, and open up the "ExecutePackage" connection manager
First off, you'll notice that it can ONLY refer to a file, not an MSDB connection.
So, next up, close this window, and open up the EPT_File task.
So we want to set the location, right? Change it from File System to MSDB by using an expression?
Not so quick
This property is NOT controlled by an expression
What we're going to do then, is have one of each of a file system or an MSDB package
4.3 Creating an EPT for MSDB
To start, add a new variable, boolExecutefromMSDB. When this value is true, we are going to execute the package in MSDB, and when it’s false, we’re going to execute the package from the filesystem.
Right click on the design surface, and configure this value in the Ops configuration table.
The intent is that, in the development environment, the value will be false, allowing you to execute from your build folder, and in test and production, the value will be true, executing from the MSDB.
At this point, we wish to drag a new "Execute Package Task" onto the design surface, and name it "EPT_MSDB"
Edit the task, and set the connection properties to use the MSDB SQL Server connection.
Finally, choose the "Template Package" We do this just to create the connection. We are actually going to use the variable to choose the package to execute.
Go to "Expressions", choose PackageName for the property, and open the expression editor. Click OK.
5 Precedence Constraint
At this point, we wish to ONLY execute one of the packages. To start off with, drop a “Script Task” onto the design surface. This script task isn’t going to do anything, it’s just a place holder. Add an annotation to allow others to know this when they see it!
Drag a connector from the script task to each of the “Execute Package Tasks”. To do this, click on the Script task, and you'll see a green arrow. This is the precedence constraint.
Drag the connector to each "Execute Package Task"
Right click on the connector to the “EPT_File_Task” task, and edit the expression.
Change the "Evaluation operation" to expression
Edit the expression to reflect the following to “@boolExecuteFromMSDB == False”
Right click on the connector to the “EPT_MSDB” task, and edit the expression to “@boolExecuteFromMSDB == True”
Now, when the package is executed, it will either execute a package in the file system, or the one in MSDB, depending on the configuration.
Save your package, and build the project.
6 Using the package
My recommendation, even in dev, is that you deploy the “Execute Package” to MSDB, and consistently use it from there. The alternative is to use the file system JUST for this package, and that is inconsistent.
So, firstly, to deploy the package manually. Start off by deleting the existing package. While it won't throw an error, I often find that redeploying does not overwrite the package.
Import your package
Now, adding a new “Execute Package Task” to a package is exactly the same as above.
Once you’ve added it, and browsed to the package in MSDB, you have the final step.
Click on the “Execute Package Task”, and add a variable, with the same name as that of the Package Configuration we previously added. Ensure that it is scoped to the “Execute Package Task”
This variable will now be passed through to the “Execute Package”, and a package of that name executed either from MSDB or the file-system.
This means that when deploying from development, to test, to prod, no little slip ups of forgetting to change a package connection can happen.
In the next article in the series, we will use this generic version of an execute package task to build a dynamic Master Controller package.