SSIS Tutorial: Introduction
SQL Server 2016 brings a number of new features to the freely-included Extract-Transform-Load (ETL) toolset, SQL Server Integration Services (SSIS). When I speak with my students about SSIS, I always recommend to keep packages small and modular. This shortens maintenance time and efforts. In this SSIS Tutorial, I also discuss ways to encourage reusability of content for multiple packages.
Since SSIS 2005, there have been a couple of techniques that have proved useful to keep things modular and reusable. We can have parent packages call child packages. We can also use package templates to copy content. New with SSIS 2016, we can now use Package Parts.
Before we start this SSIS Tutorial, to review, we can use the Execute Package Task to allow a parent package to call a child package. A child package can be reused by several parent packages. This is a great example of reusability and simplifies future maintenance. Child packages can also call child packages. Variables can be shared between packages. As shown below, you can even have child packages run in separate Windows processes, which can be a handy way to increase performance on older x86 (32-bit) SSIS servers. Yes – there are still x86 Windows servers being used at many companies.
Note: Microsoft’s extended support for SQL Server 2005 has just ended on April 12, 2016.
Create A Package Template
A developer can also create a package template. A package template is simply a .dtsx file that already includes a starting list of tasks (executables), annotations, variables, parameters, connection managers, and package properties. Instead of creating new packages from scratch, the developer can have a pattern of tasks or annotations predefined on the design surface. This can prove especially helpful when several similar packages need to be created efficiently and consistently. Never underestimate the value of consistent package design for future simplified maintenance.
It’s worth mentioning that a SSIS developer can also simply copy/paste an existing package right inside the SSIS Packages folder in the Solution Explorer window. Alternately, they can add existing packages already installed in three classic deployment locations, shown below in this SSIS Tutorial example.
When you put a package in the SSIS package template folder, it will show up in Visual Studio as an available starting package. Here is where they need to be placed:
C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems
The photo below shows Visual Studio 14.0 for Visual Studio 2015. Incidentally, this number increases by one for every new release of Visual Studio.
Note: Be sure to restart Visual Studio after copying your SSIS template packages here. Otherwise, your new templates will not appear in Visual Studio!
SSIS Tutorial: What Are Package Parts?
Starting with SSIS 2016, we have a new feature called Package Parts. It too can be used to encourage reusability while simplifying maintenance. Within a SSIS 2016 project, it permits you to define a set of Control Flow tasks in a .dtsxp single file and have one or more packages refer to it. The Package Part cannot be changed within the package that is using it.
Package Parts are composite controls that contain one or more tasks and/or containers. They act as portable, reusable containers for a series of related tasks. For experienced .NET developers, they are similar to User Controls used in ASP.NET Web Forms, Windows Forms, and WPF. They can also contain the connection managers, annotations, logging providers, properties, and variables. However, they cannot contain parameters, event handlers, or nested package parts. Notice in the picture below that there are only two tabs shown available (Control Flow and Data Flow) and the connection managers while working on a Package Part.
One important rule
A Package Part can only contain one Control Flow Task or container. Usually developers will want it to contain two or more tasks that work together to perform an important piece of reusable functionality. Thus, two or more tasks must place be placed inside of a Sequence container. A Group is not good enough. It must be a Sequence or a looping (For/Foreach) container.
What To Do With A Package Part
A Package Part is first added to the project in the Solution Explorer window. This is a designated folder for creating and storing them. You can either add a new Package part or import a copy or reference an existing Package Part somewhere else. They are defined with the .dtsxp file extension. Although they are placed in a subfolder called \Control Flow, there can be no other folder (such as Data Flow) used to stored them. Perhaps future updates/versions of SSDT will offer us more. If you want to add a Package part, you must right-click the \Control Flow folder and choose an option.
Once a Package Part appears in the Control Flow folder, it will be available in the SSIS Toolbox. However, Package Parts will only appear in the SSIS Toolbox if you open a regular SSIS Package. This is to prevent confusion with developers who may try to create a nested Package Part, which are not allowed. As well, when a SSIS package is opened, the Package Part can only be added by dragging it from the SSIS Toolbox – not the \Control Flow folder in the Solution Explorer window.
A Package Part is mostly “Read only” when placed on the Designer surface of a SSIS package. A capital “P” appear in the upper right corner. Package Parts should be treated like read-only Sequence containers.
You cannot connect precedent constraints to tasks inside the Package Part. As well, Package Part tasks cannot connect to other tasks outside of it. Precedent constraints can only connect to a Package Part and from it. You cannot add more tasks to a Package Part, although the designer inadvertently let me do that a couple of times. I suspect there’s still a few designer bugs in SSDT. When I cleared everything, re-added a Package Part and tried to add a task to it, I got the correct error message.
Package Parts can be placed inside of a container such as a Sequence container or a looping container. Multiple Package Parts can be placed in the same SSIS package. You can even place multiple copies of the same Package Part inside a SSIS package. When added, SSDT will ensure that each new Package Part is assigned a new unique GUID for an ID.
Even though you cannot add/remove tasks to a Package Part, you can modify properties, variables, and connection managers for it once added to a SSIS package. Simply right-click the Package Part and click Edit… A separate dialog box appears that will allow to tweak the its settings.
When working with variables in a Package Part, they must be defined at the Executable (Task/Container) level. I tried to move a variable to the Package Part scope and found a clear error.
Package Parts And Parameters
Interestingly, even though there isn’t a Parameters tab while editing a package part, I could still right-click an Executable or open area of the Package Part and click Parameterize…
The question is, if I create a parameter at the Package Part level, the Sequence container level, or the Task level – can I access (get/set) them from the package that is using this Package Part? Interestingly, the only parameters I will have access to are those defined at the Sequence container level.
I suspect this is another bug, having access to these parameters. You cannot use parameters defined in Package Parts or their contained Executables. This is seen when you use the Package Part in a package and try finding it using the Package Explorer. The variables are available but the parameters simply are not. This shouldn’t be a problem for SSIS developers since they can use variables or parameters interchangeably in most cases.
Here is a view of the Package Explorer showing the contents of a package that uses our Package part that had three parameters defined at all three levels within it. I found it interesting that the Package Part’s top-level Sequence container did not appear at all in the Package Explorer.
Through variables and properties, packages can pass information into a Package Part.
If you ever change the Package Part in the future, the SSIS packages that use them will automatically receive those updates. I decided to update my Package Part with an additional Script Task. When reopening the SSIS package, the updated Package Part looked a little screwy. As well, because Package Parts are read-only here in the SSIS package, I couldn’t fix it here. Another bug, albeit minor.
It’s best to have the packages open when editing the Package Parts. It will prompt you about updating the packages in the designer and the problem above will not occur.
There – now it was inserted properly!
I hope this SSIS Tutorial has been helpful for you getting started using SSIS 2016 Package Parts. They can be a great way to reuse a group of tasks across multiple packages.
Best wishes and happy ETL-ing!
