Home Dashboard Directory Help
Search

[SSIS] Package Parts revisited by Jamie Thomson


Status: 

Active


28
0
Sign in
to vote
Type: Suggestion
ID: 742002
Opened: 5/14/2012 6:48:34 AM
Access Restriction: Public
0
Workaround(s)
view

Description

Four years ago I raised a Connect item called "SSIS Package Parts" (https://connect.microsoft.com/SQLServer/feedback/details/338609/ssis-package-parts#details - 50 "up" votes so far) where I requested the ability to store a subset of a package's control-flow as a file and *instantiate* it inside of a .dtsx file. I colloquially refer to these files that contain subsets of functionality as "package parts"

The value that this would bring to SSIS development can be summed up in one word - reusability. For example, I have an Execute SQL Task that I have copied and pasted into multiple packages simply because it does the same thing in each - e.g. INSERT the value of a variable into a table. Clearly this is not an optimum way of working because we have the same code in multiple places, thus negating the value of DRY (i.e. Don't Repeat Yourself).

The answer from the SSIS team as to why this has not been implemented is that the same can be achieved by putting the Execute SQL Task into a .dtsx file all of its own and calling it from the Execute Package Task. I have pretty much accepted this reasoning but have recently had a change of heart - I no longer consider this to be a satisfactory workaround for the following reasons:

1) Siphoning functionality into a dedicated package introduces an extra executable in the executable hierarchy. Not only does this consume extra resources (because it is a package) but it also results in superfluous log messages to be recorded in tables:
-[event_message]
-[executables]
-[execution_data_statistics]
-[executable_statistics]
in the SSIS Catalog. Moreover, it also results in superfluous executables to be listed in -[event_messages].[execution_path]
All of this extra superfluous information makes the log information more cluttered and therefore harder to derive any value from the logs.

2) Siphoning functionality into a dedicated package also has the disadvantage that information may not be logged against the relevant package. For example, suppose I have a package called "InfoLogger.dtsx" that logs throws an information event containing some value that is passed to it via a parameter from another package (let's call that package "DoWork.dtsx"). If I look in [catalog].[event_messages].[package_name] then I will see "InfoLogger.dtsx" whereas in reality the information is pertinent to "DoWork.dtsx". This means that I can no longer filter on [package_name] to get all the information pertinent to that package. Package Parts would prevent this.
P.S. This technique of having a dedicated logging package (InfoLogger.dtsx) is something I've been doing a lot recently as it adheres to the DRY principle (http://en.wikipedia.org/wiki/Don't_repeat_yourself) - something I am a big fan of.

3) The executED (i.e. child) packages cannot write to variables in the executING (parent) package. This is a big BIG drawback. Variables are the means by which executables communicate between themselves so in forcing us to hive off functionality into other packages we lose that "line of communication" (and I don't accept that using a Script Task to write to variables in the parent package is a suitable workaround).
Think about an Execute SQL Task that executes a stored procedure with an OUTPUT parameter. If we're calling that stored procedure from a dedicated .dtsx package how do we ever get to use the OUTPUTted value? Simple answer - we can't.


I really hope that this request earns some serious consideration. Its a failing within SSIS that has been unaddressed for too long in my opinion.

N.B. If you vote this up (or down) please leave a comment explaining why. The product team need to see good reasons why this should be implemented.
Details
Sign in to post a comment.
Posted by Theron Knapp on 2/20/2014 at 11:16 AM
Much like partial classes in c# code allow us to manage code elements, this idea would make SSIS code especially much easier to manage. A simple change to a query in an execute sql task and the checked in to source control is obfuscated by embedding in XML format of the package to the point where it is difficult to find the change using Compare / Diff tool. Sure, Compare will show the change on the line with SqlStatementSource, but not nearly so useful as if the sql code itself were checked in or at the very least, only the component or task XML.

As an example, I have a 6300 line package file, one line of it represents the changed SQL Statement, which should instead be 50 lines of its own.

To Quote @DocWabaki on the previous incarnation of this connect:" [MS] need to separate all the user settings and other nonsense stored in the DTSX file into a different file. Then, it could be merged like code and changes could be evaluated like code. Today, looking at the impact of a single change set is way too complex. If you have multiple developers working on code, as we do, then managing code promotion can become a convoluted nightmare. We have developed tools in Team Foundation Server to indicate when we have conflicting change sets, some ready for promotion and others not. The lack of concise change sets means we need to manually adjust the code as we got through branch promotion. It should be like any other language where we can merge the code and pick up the changes we need."
Posted by Chris H (UK) on 8/7/2013 at 5:36 AM
I am new to SSIS having used Cognos DecisionStream/Data Manager for the last 10 years and am totally dismayed by finding this lack of functionality.
Having worked in large Enterprise Data Warehouses with multiple developers; I have learnt first hand that code management and deployment is pretty key to the success of any development, and having to consider using packages as the lowest level of reusable code is pretty frightening. E.g. the last ETL solution I worked on had 300 user defined functions that were core to the scalability and flexibility of the delivery, as they ensured standardised code throughout.
Whilst having a component by component comparison (from source to target environment), and the option to promote only certain components within a 'package' ensured that only the desired pieces of code were promoted.
Posted by ArthurZ on 6/19/2013 at 7:36 AM
The "package parts" feature would bring MS ETL developers to a new level allowing creating a collaborative repository of frequently [re]used components (say on CodePlex) that would be (in my dreams) installable thru NuGet right from within a SSIS project.
Posted by BlueRaja on 1/18/2013 at 1:12 PM
(adding to my comment below) Either that, or the SSIS team doesn't have the slightest idea how their product is used in the real world.
Posted by BlueRaja on 1/18/2013 at 1:08 PM
I don't understand how SSIS can be used in the real-world for any non-trivial tasks without functions/procedures. This should have been one of the first features ever added to SSIS; the fact that it's 2013 and this, the most basic and essential feature imaginable, still isn't in SSIS makes it obvious SSIS isn't supported by Microsoft anymore.
Posted by Koen Verbeeck on 8/3/2012 at 12:30 AM
This would be extremely useful. I use custom logging in my packages using the same Execute SQL Tasks over and over again. Recently I had found a tiny bug in the logging, so I had to edit all the packages, instead of editing it at one place.
Posted by SSISJoost on 7/17/2012 at 2:08 PM
This would be a very useful addition to SSIS.
Posted by JasonBacani2791 on 7/13/2012 at 2:16 PM
While I do not have some of the expertise or experience as some of the folks listed here, I do feel that "SSIS Package Parts", as it is called, would allow for mid-level SSIS users like myself to quickly learn and apply best practices as established by development teams and/or by organizations. While ‘copy and paste’ will be used with such package parts, use of these will often allow more customization and flexibility that calling an additional package does not afford. Just the benefits of reusability and simplicity seem enough to warrant its inclusion. Lastly, as stronger SQL Server developer versus .Net developer, anywhere where an inherent SSIS tool or task can be used over a script task is a plus (even though I do recognize that custom components are viable via script tasks). Thanks.
Posted by DataOgre on 7/12/2012 at 10:06 PM
Much of what I have done in SSIS would have been able to take advantage of a feature like "package parts" (eg, implementing the same custom logging in every package). I really prefer to solve business problems over cranking out boilerplate code. Please help us out here, copy/paste reuse doesn't really count as reuse. Thanks!
Posted by DaveValz on 7/12/2012 at 9:01 AM
I agree with the comments that have been left thus far, and want to re-iterate the importance of having an ability to create and reuse code in development, or in our case common tasks in SISS. Having a tool like "SSIS Package Parts" would allow the BI Development community to leverage existing tested code, instead of constantly reinventing the wheel.
Posted by Eric Wisdahl on 7/12/2012 at 6:58 AM
All of the reasons listed above. This would allow modularized development for frameworks or templates across an enterprise that would speed up both development and maintenance while allowing for greater standardization.
Posted by DaveFackler on 7/12/2012 at 6:52 AM
I totally agree with Jamie, FatPete, and JoshR. Trying to use SSIS in an enterprise ETL environment is difficult at best without reusability of components, tasks, etc. Even if the first iteration of package parts was limited to certain tasks like the Execute SQL task and a few others, that alone would be immensely helpful. Please, please, please consider this for the next release!!
Posted by JoshR on 7/12/2012 at 6:00 AM
This feature would have been useful on so many projects in the last 7+ years. We are constantly re-using chunks of code for such things as detailed auditing, merging records, etc. that involves cookie cutter components that require copying an existing package and then changing a bunch of things. On one project, we have close to 200 packages that operate this way, therefore it is not reasonable to make any changes to the repeated code since it would require changing so many times. We could call a second package via Exec Package, but for all of the reasons listed in the Connect item, this is not feasible, especially because of the "workaround" required to edit parent package vars via script task.
Posted by FatPete on 7/12/2012 at 5:10 AM
This is something I've been craving for a long time now. Like Jamie I've been implementing child packages as a workaround, but wasn't 100% satisfied with the results (for pretty much the same reasons as above). So many of our packages would benefit from this.
Posted by Microsoft on 7/12/2012 at 2:45 AM
Hi Jamie,

Thank you for your feedback. We are looking at the issue and will keep you updated.
Sign in to post a workaround.