Servers

    SQL Server SSIS Sample Component: UnpackDecimal

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

      1.2

      File Name:

      SetupUnpackDecimal.msi

      Date Published:

      3/6/2006

      File Size:

      123 KB

        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.
    • Supported Operating System

      Windows 2000, Windows NT, Windows XP

        SQL Server 2005; Visual Studio 2005
      • Install and build as directed in readme.txt

    Popular downloads

    Loading your results, please wait...

    Free PC updates

    • Security patches
    • Software updates
    • Service packs
    • Hardware drivers

    Microsoft suggests

    Download a free trial of Windows Server 2012 R2.
    Windows Server 2012 R2 free trial
    Experience the new and enhanced capabilities.
    Free trial