In the past I have talked alot about how you can use the script component in a number of different ways. It provides a quick and easy way to add custom, performant functionality to your data-flows. Of late I haven't provided many examples of using the script component but then I read this post on the SSIS forum that asked how to perform an unpivot operation within a script component and thought that would be a good subject for a new post. And here it is.
The first thing to note is that any unpivot operation (otherwise known as normalising) inherently produces output that has different metadata to the input and therefore our script component needs to be an asynchronous script component. For the example I have produced I have normalised some of the contents of the FactInternetSales table that is provided in the AdventureWorksDW sample database that comes with SQL Server 2005. The script component has 4 columns in its input:
Here we are going to use ProductAlternateKey as the pivot key, and we'll pivot UnitPrice, SalesAmount & Freight columns, all of which have numerical values in them. Hence, we are going to need two new columns in our output:
AmountType - To identify the type of pivoted data (in this case it will be UnitPrice, SalesAmount or Freight)
AmountValue - To hold the pivoted value
Here's how we define those output columns (along with ProductAlternateKey as the pivot key):
As with many things in SSIS (and most of the things I write about), this is pretty easy. Here's the code to do it (I have no idea why the remainder of this blog post appears multiple times. I think its something wrong with our blogging engine that causes it - I've tried hacking the HTML, to no avail):
1 Imports System
2 Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
3 Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
4
5 Public Class ScriptMain
6 Inherits UserComponent
7
8 Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
9
10 'All pivoted values come from the Row object which contains the incoming row
11 With Output0Buffer
12 .AddRow()
13 .ProductAlternateKey = Row.ProductAlternateKey
14 .AmountType = "Sales"
15 .AmountValue = Row.SalesAmount
16
17 .AddRow()
18 .ProductAlternateKey = Row.ProductAlternateKey
19 .AmountType = "UnitPrice"
20 .AmountValue = Row.UnitPrice
21
22 .AddRow()
23 .ProductAlternateKey = Row.ProductAlternateKey
24 .AmountType = "Freight"
25 .AmountValue = Row.Freight
26 End With
27
28 End Sub
29 End Class
That's pretty easy stuff although there's probably more use in you seeing this working for yourself. Here is the demo package that I took this code from.
-Jamie
1 Imports System
2 Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
3 Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
4
5 Public Class ScriptMain
6 Inherits UserComponent
7
8 Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
9
10 'All pivoted values come from the Row object which contains the incoming row
11 With Output0Buffer
12 .AddRow()
13 .ProductAlternateKey = Row.ProductAlternateKey
14 .AmountType = "Sales"
15 .AmountValue = Row.SalesAmount
16
17 .AddRow()
18 .ProductAlternateKey = Row.ProductAlternateKey
19 .AmountType = "UnitPrice"
20 .AmountValue = Row.UnitPrice
21
22 .AddRow()
23 .ProductAlternateKey = Row.ProductAlternateKey
24 .AmountType = "Freight"
25 .AmountValue = Row.Freight
26 End With
27
28 End Sub
29 End Class
That's pretty easy stuff although there's probably more use in you seeing this working for yourself. Here is the demo package that I took this code from.
-Jamie
1 Imports System
2 Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
3 Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
4
5 Public Class ScriptMain
6 Inherits UserComponent
7
8 Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
9
10 'All pivoted values come from the Row object which contains the incoming row
11 With Output0Buffer
12 .AddRow()
13 .ProductAlternateKey = Row.ProductAlternateKey
14 .AmountType = "Sales"
15 .AmountValue = Row.SalesAmount
16
17 .AddRow()
18 .ProductAlternateKey = Row.ProductAlternateKey
19 .AmountType = "UnitPrice"
20 .AmountValue = Row.UnitPrice
21
22 .AddRow()
23 .ProductAlternateKey = Row.ProductAlternateKey
24 .AmountType = "Freight"
25 .AmountValue = Row.Freight
26 End With
27
28 End Sub
29 End Class
That's pretty easy stuff although there's probably more use in you seeing this working for yourself. Here is the demo package that I took this code from.
-Jamie
1 Imports System
2 Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
3 Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
4
5 Public Class ScriptMain
6 Inherits UserComponent
7
8 Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
9
10 'All pivoted values come from the Row object which contains the incoming row
11 With Output0Buffer
12 .AddRow()
13 .ProductAlternateKey = Row.ProductAlternateKey
14 .AmountType = "Sales"
15 .AmountValue = Row.SalesAmount
16
17 .AddRow()
18 .ProductAlternateKey = Row.ProductAlternateKey
19 .AmountType = "UnitPrice"
20 .AmountValue = Row.UnitPrice
21
22 .AddRow()
23 .ProductAlternateKey = Row.ProductAlternateKey
24 .AmountType = "Freight"
25 .AmountValue = Row.Freight
26 End With
27
28 End Sub
29 End Class
That's pretty easy stuff although there's probably more use in you seeing this working for yourself. Here is the demo package that I took this code from.
-Jamie
1 Imports System
2 Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
3 Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
4
5 Public Class ScriptMain
6 Inherits UserComponent
7
8 Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
9
10 'All pivoted values come from the Row object which contains the incoming row
11 With Output0Buffer
12 .AddRow()
13 .ProductAlternateKey = Row.ProductAlternateKey
14 .AmountType = "Sales"
15 .AmountValue = Row.SalesAmount
16
17 .AddRow()
18 .ProductAlternateKey = Row.ProductAlternateKey
19 .AmountType = "UnitPrice"
20 .AmountValue = Row.UnitPrice
21
22 .AddRow()
23 .ProductAlternateKey = Row.ProductAlternateKey
24 .AmountType = "Freight"
25 .AmountValue = Row.Freight
26 End With
27
28 End Sub
29 End Class
That's pretty easy stuff although there's probably more use in you seeing this working for yourself. Here is the demo package that I took this code from.
-Jamie
1 Imports System
2 Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
3 Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
4
5 Public Class ScriptMain
6 Inherits UserComponent
7
8 Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
9
10 'All pivoted values come from the Row object which contains the incoming row
11 With Output0Buffer
12 .AddRow()
13 .ProductAlternateKey = Row.ProductAlternateKey
14 .AmountType = "Sales"
15 .AmountValue = Row.SalesAmount
16
17 .AddRow()
18 .ProductAlternateKey = Row.ProductAlternateKey
19 .AmountType = "UnitPrice"
20 .AmountValue = Row.UnitPrice
21
22 .AddRow()
23 .ProductAlternateKey = Row.ProductAlternateKey
24 .AmountType = "Freight"
25 .AmountValue = Row.Freight
26 End With
27
28 End Sub
29 End Class
That's pretty easy stuff although there's probably more use in you seeing this working for yourself. Here is the demo package that I took this code from.
-Jamie