未加星标

Control Flow Package Parts in SSIS 2016

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二05 | 时间 2016 | 作者 红领巾 ] 0人收藏点击收藏

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数据库 万方数据库

主题: SQLSQL Server
分页:12
转载请注明
本文标题:Control Flow Package Parts in SSIS 2016
本站链接:http://www.codesec.net/view/483883.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(27)