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.

CustomerName	OwnsCar	OwnsTruck
Customer 1	1	1
Customer 2	1	0
Customer 3	1	0
Customer 4	1	0
Customer 5	1	0
Customer 6	1	0
Customer 7	1	1
Customer 8	1	0
Customer 9	1	0
Customer 10	1	0
Customer 11	1	0
Customer 12	1	0
Customer 13	0	1
Customer 14	0	0
Customer 15	0	0
Customer 16	0	0
Customer 17	0	0
Customer 18	0	0

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>