MY THOUGHTS ON SSIS 2016 – SSIS PACKAGE PARTS-2

This is the second part of SSIS Package Parts blog post, I would recommend you to read other part first!

In the previous post we saw basics of SSIS Control Flow Package Parts, now let’s look a bit more in detail:

Package Part in Code Mode:

In design mode, an empty package part looks like regular SSIS package apart from Package Part has only Control flow and data flow tab. When we compare the source code between a new SSIS package(.dtsx) and a new control flow package part (.dtsxp), both looks pretty similar with obvious package type, refId and CreationName as different. So using BIML to create package parts won’t be much different compared to SSIS Packages.

One of the first use cases that came into my mind when I first saw control flow template was to have the extensibility of reusing the code. Whenever I create an SSIS package to populate a Star Schema, I usually use a separate framework like many others; I try to capture a lot of metadata related to SSIS packages and execution. So I can monitor how many rows got inserted, updated or some other information related to executions and performances. However, one of the tedious thing with SSIS is minimal support with code reusability. Whenever you copy an SSIS Task to a new package or a new solution it loses all the existing connections and sometimes parsing too, you need to remap the connections again, but SSIS Control Flow Package Part directly addresses this issue.

Control flow Package Part for SSIS Audit:

So I want to create a package part to monitor SSIS Audit Begin and Audit End events for each execution. For this purpose, I want to use some system variables and user variables. Once I created the first variable in Control Flow Part, the first thing I noticed was all the variables were task scoped.

I did not understand the purpose of not having the flexibility of changing the variables scope in SSIS Control Flow Package Part Designer. Next thing, I tried was adding another task by assuming I can choose which task to execute during the execution based on the task enable/disable property. Immediately I got an error saying “Control flow package part need to contain exactly one executable.” This error explains why variables were task scoped only. As only one executable allowed in package parts, having task scoped variables will not lead to any confusion when the same name variables already exist in the SSIS Packages.

Well for my purpose I need two Execute SQL Tasks, one at the begin and one at the End. I thought I can just add two Execute SQL Tasks but by looking at the limitations the only way I can achieve this is by creating two different package parts. Hence, I created two SSIS Package Parts. Then I was curious to see why entire SSIS toolbox available in the package part designer when you can not add more than one executable? E.g., Containers. Later I placed a Sequence Container, and surprisingly I can add more than one Execute SQL tasks, and it executes perfectly fine. So if you want to use more than one task in a package part then use Sequence Container. It nicely differentiates each executable in Package Part configuration.

I thought I will anyway create two separate Execute SQL Tasks because package part clearly defines what it meant to do. Both Package Parts I created has task scoped variables and SQL Statement Source as an expression.

I can see my two SSIS Package Parts in the SSIS Tool Box. I can just drag and drop these two SSIS Package Parts into Control Flow Template I can quickly identify SSIS Package part by looking at little P icon on the top right, and also I can see it uses the expression with little fx icon on top left. Initially, I just want to see how my package works with new package parts.

And basic execution worked perfectly fine. I needed another SSIS Audit End package part with failed Execution result, as I can use package parts inside SSIS packages as many times as I want and I was using user variables for execution result value, so I just changed the User Variable Execution Result value to get the desired result. It is better to use variables whenever possible to make Package parts more dynamic.

 Next the tricky bit; how I can pass the Row count? I have package scoped variables which hold the row count. The only way I can think of doing this was manually changing the scope of the variables. ( I am so glad it was not restricted 🙂 )

Once I manually changed the value, I was able to use these two variables in the Data Flow Task. And the job was done. My package worked just like the way I want.

If I want I can change the scope of the variables of all control flow parts, if same variable value already exists in the package SSIS automatically renames the newly moved variable or we can just delete the control flow part variables which we already have under Package scope

Well, it works, with few workarounds, I did manage to achieve what I want.  But there were few hiccups while I was developing this small solution and imagine creating a robust solution.

Blank Screen:

The first and most annoying thing were the blank screens. I don’t know it was my laptop or installation, but a blank screen with no error information, Really !?!?

Well, I first got this error when I opened a package part which used as a reference in another SSIS Project. Not only that there were several other occasions, I saw this blank screen, and I really hope it was my PC issue than SSIS. Every time I got this blank screen error, I deleted the package part from solution and recreated.

Package Parts Execution:

Another unbelievable thing was there is no execution or debugging option for package Parts. Really? We can see brake points option available in SSIS parts. However, there is no use. There is no way I can execute a package part with in Package Part designer, the only way I can execute by placing it in an SSIS package.

I am sure everyone want to test their package part before they put it in the SSIS Package, there are so many other scenarios where you need to execute your task or executable before you use it somewhere…

Reusability:

As I mentioned earlier, the primary use case of package parts is code reusability. But if there is no track of where it has been used then it is a difficult job to control changes. If I make changes to package part of a solution, and the package which uses this package part was still open, then I get a notification about reference, apart from that there was no other way I can see where the package part has been used.

When I add an existing package part as a reference to the project and make changes accidently. The other packages which are using the package part will get the blank screen (At least on my PC!). When the package part added as a reference and if you change or rename it, another package where it has been used or created will have errors. But you can modify the name once it is in SSIS package, should not change the file name!

Expressions:

Package part does allow expressions, and you can see package part using expressions, but these expressions are allowed at an executable level only. For, e.g., if you have a sequence container with two SQL Tasks in package part. You can see expressions used at sequence container level but not the expression used at Execute SQL Task level however if your package part has only one executable then you can see all underlined expressions (It is better not to change expressions when using package part as a reference or added as a reference to the project)

Package part with Sequence Container and multiple Execute SQL tasks with Expressions

Package Part with single Execute SQL Task using Expressions

Connection Strings:

Package Part brings all connection string along with it. These connection strings are not visible in connection managers window, but you can see them under any other tasks connection managers or in configuring/ execution window of SSISDB Catalog. All package parts connections were prefixed by Package Part. To avoid confusion, it is better to name all packages with relevant name

Visual Studio Crashes:

Visual Studio crashes and when I reopened the project I lost all my references with package part. None of my package parts has P symbol,  after crash all package parts look and behave just like regular tasks. In a way, it is nice because my package still works but I lost all the references, though. (If package documentation says, to edit package part go to x location and make changes. That wouldn’t work! )

Some points:

1. No option to change the connection string

   

2. The only warning you get is

   

3. We can not use a package part inside a package part.
4. The deployment will not deploy Package Parts to SSISDB.
5. No debugging options were available for package parts in the package part designer.
6. Each Package part should contain exactly one executable – We can not create a control flow package part with two executable, SSIS will not allow saving the package part. But we can create a package part with no executable, but SSIS will not enable you to place it in Control Flow. In both scenarios, it raises same error.

Summary:

Do I like it? Don’t know. Will I use it? Depends.
It can be a great addition to any SSIS package but version 1. Needs a lot of improvements. If you are thinking of using it, I believe that you need to have strict standards in place. I would like to create a solution with all the package parts and make sure to put them under source control and have strict rules related to change control and annotations will probably make it easy.  Well it’s just my opinion after spending couple of days with Control Flow Package Parts, would love to hear what you think.

Next blog post in the series will be Logging…

Till next time,

Prathy 🙂