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