A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Monday, June 29, 2015

Biml - Unpivot transformation

Biml - Unpivot transformation

I had cause today to use the Unpivot transformation in SSIS. My source database was still in 2000 compatibility mode, don't laugh, so I couldn't use the PIVOT operator and I was too lazy to remember the CASE approach. My client records whether a customer uses a particular type of conveyance. For simplicity sake, we'll limit this to just whether they own a car or truck. Some customers might own both, only one or none. Part of my project is to normalize this data into a more sane data structure.

Source data

The following table approximates the data but there are many more bit fields to be had.
CustomerNameOwnsCarOwnsTruck
Customer 111
Customer 210
Customer 310
Customer 410
Customer 510
Customer 610
Customer 711
Customer 810
Customer 910
Customer 1010
Customer 1110
Customer 1210
Customer 1301
Customer 1400
Customer 1500
Customer 1600
Customer 1700
Customer 1800

SSIS Package

The package is rather simple - we have the above source data fed into an Unpivot component and then we have a Derived Column serving as an anchor point for a data viewer.

Unpivot

To no great surprise to anyone who's worked with Biml, the code is not complex. We need to provide specifics about how the pivot key column should work and then the detailed mapping of what we want to do with our columns. Here we're going to keep our CustomerName column but we want to merge OwnsCar and OwnsTruck columns into a single new column called SourceValue. The PivotKeyValue we supply will be the values associated to our pivot. Since we specified an Ansi string of length 20, the values we supply of Car and Truck must map into that domain.

Unpivot Biml

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection ConnectionString="Provider=SQLOLEDB;Data Source=localhost\dev2014;Integrated Security=SSPI;Initial Catalog=tempdb" Name="CM_OLE" />
    </Connections>
        <Packages>
        <Package ConstraintMode="Linear" Name="Component_Unpivot">
            <Variables>
                <Variable Name="QuerySource" DataType="String">
                    <![CDATA[SELECT
    'Customer ' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS varchar(2)) AS CustomerName
,   *
FROM
    (
        VALUES
            (CAST(1 AS bit))
        ,   (CAST(1 AS bit))
        ,   (CAST(0 AS bit))
    ) S(OwnsCar)
    CROSS APPLY
    (
        VALUES
            (CAST(1 AS bit))
        ,   (CAST(0 AS bit))
        ,   (CAST(0 AS bit))
        ,   (CAST(0 AS bit))
        ,   (CAST(0 AS bit))
        ,   (CAST(0 AS bit))
    ) F(OwnsTruck);
]]></Variable>
            </Variables>
            <Tasks>
                <Dataflow Name="DFT Unpviot">
                    <Transformations>
                        <OleDbSource 
                            ConnectionName="CM_OLE" 
                            Name="OLESRC Unpivot Source">
                            <VariableInput VariableName="User.QuerySource" />
                        </OleDbSource>

                        <Unpivot
                            Name="UPV Vehicle types"
                            PivotKeyValueColumnName="Vehicle"
                            PivotKeyValueColumnDataType="AnsiString"
                            PivotKeyValueColumnCodePage="1252"
                            PivotKeyValueColumnLength="20"
                            AutoPassThrough="false"
                            >
                            <Columns>
                                <Column SourceColumn="CustomerName" IsUsed="true" />
                                <Column SourceColumn="OwnsCar" TargetColumn="SourceValue" PivotKeyValue="Car" />
                                <Column SourceColumn="OwnsTruck" TargetColumn="SourceValue" PivotKeyValue="Truck" />
                            </Columns>
                        </Unpivot>

                        <DerivedColumns Name="DER Placeholder" />
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>

No comments: