Reading the way package parts were described in the SQL 2016 release notes I was pretty excited. I remember having hundreds of SSIS packages where the overall structure was fairly redundant, this was before BIML was a thing, and package parts sounds like just the thing I wanted. The ability to generalize the common parts of the package, and create modular, stand-alone functions that could be reused in different packages would minimize changes that needed to be made to every package in order to tweak a particular sub-system.

In a recent blog post Shubhankar Thatte gives his First Impressions of SSIS Package Parts.

Reading his article it sounds like Microsoft is heading in the right direction with SSIS. However, a couple of the limitations severely limit the usefulness.

  • The inability to have a package part access a project variable makes package parts pretty much useless. I expect that this will be supported in a coming release.
  • Along the same lines having package parts not be able to use the project connection objects and require they have their own connection objects defined makes them a support configuration nightmare. Even more so than before project level connections existed and you had to manage them at a package level. Now you have to manage more connections one level lower.
  • Not having the ability to have a package part return a value is also a pretty big issue, and really is just the same issue as not having access to project variables. If Microsoft allows access to project variables then this would also be resolved, assuming they do not just give read only access to project variables.

I know we do not use SSIS much here for on-site customer installations. This is primarily due to the cost of supporting it at locations where they have no experience with installing, configuring, troubleshooting, or any kind of basic support.

I can definitely see us using it more and more as we start to host solutions here since it really is a great tool for performing ETL tasks and tracking ETL errors during processing.

 

Have you read about any SQL 2016 features that you wish you could use?

Let us know in the comments below.