Control Flow Package Parts in SSIS 2016
Control Flow Package Parts are a new feature inMicrosoft SQL Server2016 that attempts to enable code reuse within SQL Server Integration Services packages. The intent is great, but like with many first iterations, there are limited use cases, and rough edges. I'll provide an overview of those here, and in subsequent posts I'll detail how I'm currently using package parts in a data warehousing environment.Why Were Control Flow Package Parts Created?
In Integration Services, there are pain points.
<Thisspace left intentionally blank (for you to crack your own jokes about the volume and variety of SSIS pain points)>
One of those difficulties only really becomes apparent after you use SSIS extensively - as part of a non-trivial data warehousing project orsynchronization system, for example. In those cases, you're typically going to have multiple packages, development time is going to span months, and you're going to learn things along the way. Demands for broad progress monitoring, standardizing error handling, or execution management are likely to arise. Most of those scenarios (and many others) typically result in extremely similar plumbing being added to all packages involved in the project - typically by copy and paste. And when the "standard" implementation changes - because it always does - it's a burden to re-copy and paste (and adjust) each package to follow the new and improved system.
Microsoft created Control Flow Package Parts to help this situation.How Do Control Flow Package Parts Help Code Reuse?
The basic idea behind package parts makes complete sense to a coder - they're macros. You take code you've used in several places, put it in a separate file that you then include and "expand" in multiple other files.
If you have multiple packages withparts of the Control Flow that are identical - setting up a database in a certain way, sending emails, calling a set of stored procedures, ... - then Control Flow Package Parts can help.
The assistance isn't just limited to the initial coding, either. Yes - creating a new package with the "duplicate" code is much easier. But the real gain of Control Flow Package Parts is when your "standard" code needs changes. Instead of having to edit multiple packages to address the modifications - you only have to alter the package part. Deploying the project(s) that depend on this part automatically incorporates those improvements.The Scorecard Good!
To recap, the positives of Control Flow Package Parts are that you can now reuse code in SSIS. If you've used Integration Services at all in the past ten years, you should be happy about that. It's great that you can bundle up a series of tasks, preconfigured to operate in a specific way, and just drop that "algorithm" into multiple packages. Moreover, it's looking fairly robust in its implementation - what's there does work reliably.
The package parts aren't "just" macros - they step one level higher than that - you can (lightly) configure them by setting values on the exposed properties of the single top-level task in the part. This means you can (in limited fashion) have a tool encoded in a package part that you can apply to slightly different ends in different packages, or even multiple instances within the same package.Bad...
Unsurprisinglyfor a "v1" feature, the initial glimmers of awesomeness are dulled by the relatively small set of scenarios that are enabled with 2016's Control Flow Package Parts.There are a few minor annoyances here:Invisible Annotations
One of the first things I naturally did when making my first package part was to place an annotation in it so that "users" of the part could be informed about the purpose, side effects, and how to use the part. Unfortunately, when that part is placed inside a host package... annotations aren't visible.
I've posted a Connect case on that as a bug.Conditional Precedence Gets Reset
It wouldn't surprise me if I was told I was pushing this new feature too far... but one of my goals was to have Control Flow Package Parts participate fully in the host package's control flow. One of those necessary capabilities is for the package part to participate in precedence constraints with other tasks in the Control Flow. They do... except if you have a conditional precedence constraint from the package part to another task. When the package part is edited and saved (independently from the host package), and then the "macro" of the package part is "expanded" inside the host package... the condition on the precedence constraint disappears.
I've posted a Connect case on that as well. This bug has the potential to be extremely vicious - edits from one user (the package part editor) destroy code in a package they didn't touch... silently.Ugly?
I have to confess that I'm probably taking the feature outside the design envelope - but I feel that it's the natural place to take it. I described Control Flow Package Parts as a "macro" earlier - but what I really want (and I'm sure you want it too) is "Control Flow Package Functions". I want those "parts" to be first-class citizens in the host package. Since they're essentially macros - the code from the package part appears to be literally automatically copied and pasted into the host - there seems to be no reason why not.
I want a managed way to interact with the part: an API if you will. And no, altering the list of top-level task properties isn't an API. Nice try. If one of the strengths of a package part is to have multiple tasks act as "one unit", then by definition, none of the settings of those included tasks is accessible to configure. Secondly, altering properties on a task is one of the cruder methods of "configuring" execution dynamically.Shared Connection Managers
I want the Package Part to be able to "inherit" Connection Managers from the host, and/or be able to "map" Connection Managers from the host package to Connection Managers inside the package part. I've filed a Connect item for that request, which should enable far more code re-use. Imagine a simple package part built to test the health of a database and record it in a table, perhaps asa first "validation" step in a standardETL process you conduct in many packages. This part would need to use two Connection Managers - one for the database under test, and another for the logging table. The logging table could be static (bad idea, nothing should be "unconfigurable"), but the database under test would naturally want to be specified by the host package. Unfortunately, there's no documented* way to "pass in" the connection manager you want.Shared Variables
Probably more problematic is the inability to communicate values "in" to the package part and get results "out." One of the best ways I can see using package parts is to distill a complex operation to a simple one - but that commonly requires input and output to be passed back and forth. Take our previous example about testing the health of a database and recording it in a table. In addition to wanting the Connection Manager be configurable, youcould also want a table name to be passed in, and the results of the test (perhaps a "last updated date") returned.There's no
本文数据库（mssql）相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库