Zoned Decimal Converter

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:

Input file

Output file

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

2 thoughts on “Zoned Decimal Converter

  1. I am currently writing an SSIS package to import and transform a flat file exported from a mainframe and the zoned decimal issue has definitely come up. Any chance you could please post the code for the The ConvertZonedDecimal Class? Many thanks

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s