SQL Server SSIS Sample Component: UnpackDecimal

Brief Description

UnpackDecimal takes an input column formatted in packed decimal (comp-3), and generates the corresponding Decimal value.

Overview

SSIS has no provision for converting packed decimal (comp-3) data.

This transform takes a bytes column, and converts to decimal, using a user provided scale.

Configure in advanced editor by clicking input columns containing packed decimal values. Afterwards, go to the input and output tab and set the PackedScale property on the input column if it is to differ from 0.

This component automatically creates an output column when the input column is selected, then forbids any change to that column's metadata. The only allowed user edits are on column name and description, and the scale. All else are rejected.

Scale must be between 0 and 28. The length of the input field must be 14 bytes or fewer. This imposes a limit of 27 digits on the converted value. Though 28 digit numbers are supported by the decimal format, they take 15 bytes to store, and a 15 byte packed can hold 29 digits, overflowing the decimal at runtime.

Left as an exercise for the student:
a) add an error output to direct overflows or badly formatted value to.
b) update component to allow 15 bytes in, and reject values with non-zero most significant nibble.

This component is part of a series of components that illustrate increasingly complex behavior, each one exercising a greater proportion of the SSIS object model. If studying in order, this component follows ConfigureUnDouble, and precedes UnDoubleOut.

This component was built to provide an introduction to the use of output columns. Also illustrated are:

- Binding input to output columns with custom properties.
- Distinguishing upstream columns from each other.
- Operating on DT_BYTES
- Copy and paste support
- Use of Ondeletinginputcolumn.
- Setusagetype gives you a virtual input. (because new columns won’t be in the input)
- ReinitializeMetadata to clear up referring columns
- Use the input buffer id not the output buffer id, when setting output column values.

Top of page