Tag: pivot

This is a situation where the data from a number of tables with the same structure needs to be imported into a single table.  Rather than hard coding multiple data flows from source to destination, you can loop through each SQL statement using a single data flow by building a dynamic SQL statement.  This simple package loops through each table name to accomplish just that.  It also has a secondary loop to pivot some hard coded week numbers in the field names.

. 

The data source is a series of tables with the same structure. Each one holds a different set of planning data.  The fields have the fiscal month hard coded into the name, rather than have Week Number as an attribute.

The destination not only needs to map multiple tables to a single table, but it also needs to pivot the fiscal month weeks.

 

LOOP THROUGH TABLES

To accomplish this I hard coded the table names into the For Each loop, but an object variable could just as easily done this. 

 

 

LOOP THROUGH FIELD NAMES (WeekNo)

 

 

VB SCRIPT TASK TO WRITE THE DYNAMIC SQL STATEMENT

In the script portion, write a simple script which uses the variables and embeds them in the SQL statement you are writing.  This outputs to your SQLStatement variable to be used in the data flow.

    Public Sub Main()
        '

        Dim WeekNo As Integer
        Dim TableName As String
        Dim SQLStmt As Object

        WeekNo = Dts.Variables("WeekNo").Value
        TableName = Dts.Variables("TableName").Value
        SQLStmt = "SELECT [Version Code] as Plan_Version, [Fiscal Year] as FiscalYear,[Fiscal Month Sequence] FiscalMonth, " & WeekNo & " as FiscalWeek, convert(numeric(38,20),[SLS NET $ W" & WeekNo & "]) as Sales, FROM " & TableName
        Dts.Variables("SQLStatement").Value = SQLStmt

        Dts.TaskResult = ScriptResults.Success
    End Sub

To set up the data flow, you must first enter a valid SQL Statement in the SQLStatement variable.  Your variable will then be replaced with a new one during each loop. 

I hope you find this useful.