I am trying to write a ssis surrogate key data transform, my problem is I can't find an example how to add a column to the incoming columns and add some data to it. If anyone has a sample, can you please post it. I found a script option that works but I would like an actual transform.
Thanks
Basically - here is a surrogate key Transform Script - to generate image numbers for productsInput is ProdNum column - output ImgNo colum.
The idea is to get the result like this:
ProdNum ImgNo
1 1
1 2
2 1
3 1
3 2
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Dim imgno As Short, incr As Short, prevProdNum As String
Public Sub New()
imgno = 0
incr = 1
prevProdNum = ""
End Sub
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
If Row.ProdNum <> prevProdNum Then
imgno = incr
Else
imgno += incr
End If
Row.ImgNo = imgno
prevProdNum = Row.ProdNum
End Sub
End Class|||Also - you can check out this article "SSIS Generating Surrogate Keys"
No comments:
Post a Comment