Home Tags Posts tagged with "SSIS 2016"

SSIS 2016

This blog post has become longer than I thought, so decided to post as two parts. First part covers introduction and second is bit more in detail – hope it makes it easy to read!

Control flow Package Parts, one of the SSIS 2016 features which excited me most when Matt Masson did a session about imagewhat’s new in SSIS and MDS at Pass Summit 2016.Whenever there is something new, it is quite common to get positive and negative comments. But it seems like SSIS Control Flow Templates or the new name SSIS Control Flow Package Parts ( Does it mean, we can expect other types of package parts in the future?) received more negative comments than positive. I have used Control Flow template briefly with my previous blog post example; however, this blog post gives me a chance to look in detail to learn and understand what are missing from my expectations.

Creating an SSIS Control Flow Package Part:

For me the only thing which is straight forward with Package Parts is creating a Package Part. Creating a new package part is very easy. You can simply go to Package Parts section under the project where you wanted to create one, right-click and when you click on the new control flow Package Part, it creates a new .dtsxp file. You can not only create one but also can import existing one. Once it is created it looks just exactly same as SSIS package with same SSIS Tool Box.

image

Package Part in Solution Explorer

image

Creating a New Control Flow Package Part

Adding an Existing Control Flow Package Part:

You can add existing package part two ways: as a reference or without reference

image

When you add as a reference, package part still reference to the original location of the file, and when you add it as a non-reference, it copy’s the file to the new project location. We can see the difference by looking at the source file path or icon of the package part; it is a tiny icon, though.

image

Obviously, when there is a reference, then all the underlined changes will get affected. Which option to choose is entirely based on the use case? I will explain bit more about this in the next blog post, because it has it’s own pro’s and con’s.

 Using Package Part:

Once the Package Part created or added, you can see them in SSIS Tool Box. We can simply drag and drop them into Control Flow as any other SSIS Control Flow Tasks.

Configuring Control Flow Package Part:

In the configuration window of package part, you can see two tabs one to configure connection manager and one for properties. If you want to change the value of a property then, you can click on the property and change the property path. If the underline package part using any expressions, it brings back those expressions too, it is just same as copying and pasting existing task, but here it brings the connection with itself also better GUI to change the properties. Package part allows only one executable, but if you want to add more than one executable, then you can use Sequence Container. It is useful in certain cases, like when you don’t want to create multiple package parts; by using a sequence container you can add more than one executable and use enable/disable property of executable for execution.

image

Package Part Properties configurations window

image

Package Part Connection Managers configurations window

image

Package Part configurations for Sequence Container with multiple executables

That’s all you can just drag and drop this pre-created SSIS Control Flow Package Parts in any SSIS package you want. If require, you can configure the properties of package part. Easy 🙂

But unfortunately it is not as straight forward. Variables doesn’t work the way you expected, you get unexpected blank screen as an error and many more. I will address all these in the next blog post…

Till Then,

Prathy

2 comments
0 FacebookTwitterPinterestEmail

SSIS has always been my favourite among all MSBI tools. I think it is ETL which interests me than specific SSIS. As an SSIS fan girl, SQL Server 2012 release was my favourite. It was a huge release with numerous improvements (Don’t forget rounded corners ). If we compare SQL Server 2012 and 2016 releases, there are not as many exciting enhancements in the release, but some noteworthy enhancements are there. Hence, this blog post is to look into new features of SSIS 2016 in detail, so I will get a chance to explore them. I am going to publish this post as series so it will be convenient to read and write Smile

Package Deployment:

One of the most complained or requested feature after SSIS 2012 release was package deployment. I am not a huge fan of this, but I can see why people were missing this feature. I have seen people debating about SSIS, saying “SSIS is same as any other development so you deploy entire solution at a time, not individual packages”. Probably Microsoft also considered SSIS development as any other development and introduced project deployment in SSIS 2012. I totally agree with the argument, and I would like to do that way, but most of SSIS developers deploy individual packages…

Well, it’s nice to see package deployment back in SSIS 2016. So what happens when you decided to deploy individual packages?

We all know how to do project deployment, new incremental package deployment in SSIS 2016 is also same as project deployment, but you can right click on the individual package or multiple selected packages and choose deploy.

image

All the rest of the steps are same as the previous deployment. Though, if you want to see what packages are selected or if you changed your mind about deployment after you started deployment wizard; you can click on the previous button which gives us a new window in deployment wizard where we can choose the packages you want to deploy. One thing I noticed here is you can deselect, but you are not allowed to select new one’s, well you can select new one’s but wizard will not allow you to go to the next step (Next button was greyed out)

Usually, SSIS does project deployment from .ispac ( Integration Services Project Deployment File) file. When you choose package deployment instead of using .ispac file, it selects the packages which you wanted to deploy. You can see in the following screenshot only selected packages were loaded into deployment wizard. Also instead of choosing .ispac location deployment wizard is using Packages folder path. If you change deployment model to project deployment, it will ask for .ispac file path

image

Package Deployment

2016-06-06_01-16-07

Project Deployment

When we look at deployed packages in SSISDB, surprisingly we can see all packages are deployed irrespective of incremental deployment.

SELECT [package_id]
      ,[project_version_lsn]
      ,[name]
      ,[package_guid]
      ,[description]
      ,[version_build]
      ,[version_comments]
  FROM [SSISDB].[internal].[packages]

In the following screenshot, we can see, yellow highlighted is the deployment where ChildPackage2 and Master Package selected and in the green highlighted one only master package has been chosen to deploy.When we look at version_build column value, we get the assurance that it is indeed incremental package deployment. All packages have a row in [SSISDB].[internal].[packages] because of [project_version_lsn] which is the column used to related to project table. So all the relationships between different tables in SSISDB will work smoothly.

image

[SSISDB].[internal].[packages]

Is new package deployment model addressing existing issues?

  1. We still can not reference packages from different project or packages from SSISDB using Execute Package Task. Before SSIS 2012 we were able to reference to SSIS Package Store and execute packages from different solution or deployed solutions. There are various workarounds to achieve this; I prefer using Execute SQL task to call Stored Procedure to execute a deployed package but easy reference in EPT is something still missing for me.
    image

    (EPT) Execute Package Task

    2. Parameter Bindings in Execute Package Task:

    Execute Package Task still does not support variables; If you want to send parent package variable value to a child package variable, you first need to create a parameter in the child package and then use that parameter value as an expression in the variable where you wanted to use the value from the parent package.
    No biggie but most of the tasks still does not support parameters. e.g. We can not use SQL Command from Parameter in OLEDB Source, or ExcuteSQLTask result set can not be a parameter or For each Loop variable mapping does not support parameters.

image

Wrap-Up:

As I mentioned earlier, It is nice to see incremental package deployment. It’s great that Microsft listened to user’s voice and implemented this feature.  I don’t know what’s going on behind the screens, but when Packages deployed individually to a server, SSISDB nicely references the previously deployed packages. Probably just checking the version build of all packages and where it is same it just copy the existing packages metadata. But if they managed package deployment, I don’t think it will be difficult for Microsoft to develop SSISDB reference under Execute Package Task, maybe in the next release …

Next blog post in this series will be my favourite feature, Control Flow Templates.

Till next time,

Prathy 🙂

0 comment
0 FacebookTwitterPinterestEmail