Showing posts with label ssis. Show all posts
Showing posts with label ssis. Show all posts

Friday, March 23, 2012

Help with sample code for ssis surrogate key transform

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 products
Input 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"

Wednesday, March 7, 2012

Help with ordering the SSIS packages the way they appear in the project

In solution explorer under SSIS Packages there are many packages. I name them specifically so that they should be ordered with their names, but no the solution explorer shows them in the order I added them, I couldn't figure out a way but removing all the packages and adding them in the right order to fix that problem. I have to kinda redo this if I want to add a new package not to the end.

Any ideas?

All here: http://solidqualitylearning.com/blogs/erik/archive/2005/08/08/1269.aspx

-Jamie

|||Thank you, I didn't want to edit the configuration without knowing what I was doing.