The idea of creating your own custom component in SSIS is very powerful. With SSIS being an enterprise ETL platform we have the ability to create our own pipeline components. What we have found with SSIS is that it has very basic functionality with the architecture and by no means limited but enough to be dangerous. Custom components allow developers to take advantage of the SSIS pipeline architecture by utilizing language like C# and VB.NET to create functions that can make ETL much more efficient. The components allow ease of use drop and drag functionality with very little coding or configuration. Most importantly is the reuse of the components in any project. This document points out the Zoned Decimal Converter which is an issue for numbers comingout of Mainframe flat files. We discovered that the numbers were zoned and had a funny character at the end that needed to be translated to a full number. With the growing development community we found nothing to remedy this type of conversion so we attempted to build our own.
The following illustrates that we create the Zoned Decimal Converter component and add it to the SSIS toolbox which allows us to see it in our SSIS toolbox designer.
Once we have this in our toolbox we are now ready to add it to the dataflow task as seen below:
We can also see that the component is just like all the other component designers we have our input and output columns. The nice thing about this component is that we are concerned about 2 things with the output. One the input is a string and the output is a decimal so the component automatically creates the output column the same name as the input so you won’t have to map it in the end. Second is the output field needs a decimal number and you can do that with the global Scale or individually set that at the output column level. The global scale needs to be done at the beginning when you first add the component and commit the changes. The reason for this is because the component creates the output columns dynamically.
The output for this function looks like the following:
Note: the component changed the input column and added an * to keep the mappings for the output.
The ConvertZonedDecimal Class is written in the C# language as shown below:
To add the components to be used in SSIS go to the following link https://jessekraut.wordpress.com/custom-control-guidelines/