Conditional Split is a very simple and effective transform, and is one of the most commonly used transforms in a SSIS package. But those who do not pay attention to detail or those who are do not work regularly / extensively in SSIS, often miss the detail that once a record matches a criteria, it's moved to the first matching output path. So each output path has exclusive records, and no repeated records.
Many times we come across a situation where we have records that are matching more than one criteria and we need such records in more than one output paths. Generally we take help of multicast transforms in such scenarios, but that is not an efficient design, and definitely such design won't scale. When the question / task comes of splitting data, right from the schools / parlance of SSIS, we are used to answer / use "Conditional Split" Transform. But we know the limitation of this transform for the scenario in question and design issues with Multicast transform. So what is the way to conditionally split data in an SSIS package without the help of this transform ?
Today one of my article got published and it's titled " Intelligent Conditional Split in an SSIS package "; it is aimed to present a solution at this issue, and the same can be read from here. The origins of this technique is derived from the niche art of custom SSIS components / transforms development methodology. If this solution is wrapped as a custom component, it can be used very well as an Intelligent Conditional Split component. To avoid ruining the value of my article, I would limit my explanation and would let you check out the original article. Feel free to share your opinion about this article.
Article 
2 comments:
Good article. What's solution if output path needs to variable e.g. redirecting 100 rows per batch when number of rows are unknown in table, which would require N numbers of output.
Number of Output paths need to be fixed. And if you have variable output path, then you would not have even the logic the process those outputs. So in a practical scenario, there must be very very exceptional reasons for such a requirement.
Post a Comment