Creating SSIS packages programatically is one of the emerging requirements from the .NET world. I had put down a post earlier on this topic, where I also provided a link on MSDN samples on creating SSIS packages programatically. This post is an extension to the same post.
A new wrapper on the SSIS object model has been made available, which facilitates creation of packages programatically thru fewer lines of code. Credits of this wrapper goes to a gentleman, Evgeny Koblov - one of the testers on the SSIS team. I had spent around 4+ Years programming on Microsoft .NET right from version 1.0 till the present days of WCF, WPF etc... Based on my experience, my feeling is that, this wrapper would definitely be a value addition but it would come with a few trade-offs too.
Few which I can think of are, performance, extensibility are the two factors which can be a concerning factor. Manageability would be definitely be a great value addition.
Performance - As the inference level would increase to one more level performance in pressure environments can degrade, but without proper load test on this layer it's premature to comment anything.
Extensibility - If the wrapper is directly used in compiled form, and there are any changes in the SSIS object model (for ex. effective due to some service packs), the same would not be available immediately in this wrapper. But as the source code is available, these wrapper can be implemented as a part of the project or can be implemented as a library for continuous value addition.
Now let me introduce the star of the post - it's called "EzAPI – Alternative package creation API". As per the documentation of this Framework, here's the explanation behind the motive that this architecture facilitates:
"Having an easy way to create SSIS packages programmatically is vital. In some cases static packages do not provide enough flexibility – it is hard to change property task or component of a static package using SSIS object model while using configurations is not always possible. Current approach to create SSIS package programmatically is to use SSIS object model directly. However SSIS object SSIS object model was designed to be universal for all kinds of components (native and managed ones) and to separate runtime and design time phases of component usage. However both of the stages use the same metadata. This is the reason why SSIS distinguishes design-time, runtime functionality and metadata. The side effect is that more code is necessary to create a package and once the package is created – it is not very convenient to change properties of pipeline components and especially metadata of input, output, virtual and external columns.
However, in .NET managed world we can use all the advantages of OOP and have a corresponding class that will encapsulate both metadata and operations that can be made on them – so, there is no need to separate it as we do not deal with runtime internals during design phase.
The framework can significantly reduce the amount of code that need to be supported and allows more efficient development of SSIS packages programmatically."
Article 
No comments:
Post a Comment