Generating surrogate keys is no sweat in SSIS. Though it does require a custom script to be written. (You might be able to use the Row Number Transformation)

Below is a screen shot of the control flow for a package I’ve built. To make this work (and I’m assuming that you’ll be interested in starting with a key value greater than what’s in the table already) you’ll need two tasks on the control flow: Execute SQL Task & Data Flow.

SurrogateKeys01

You can see the Execute SQL task below. It’s pretty simple. Set the ResultSet to “Single row”, type in the query, return it to a variable, User::MaxKey. Here’s how that works:

SurrogateKeys02

Here you can see the initial setup. Pretty straightforward. The SQLStatement is simply:

select maxkey = case
when max(clientkey) is null then 0
else max(clientkey)
end
from clientdim

SurrogateKeys03

There are no parameter mappings to speak of, so the next thing we’re interested in will be the Result Set section. Set the Result Name to 0 (zero) and the Variable Name to which ever variable you’ve defined. In this case, I’ve used User::MaxKey. This variable, whatever you call it, should be of integer type.

Next, in the data flow you should be able to perform whatever you need to do. When you determine where you want to introduce the surrogate key into the pipeline, add a script component. It should be set to transformation.

SurrogateKeys04

Here you can see the rest of the pipeline in my data flow. I use a row count transformation to simply count the rows going to the destination table. This is not a requirement. Edit the Script Component.

SurrogateKeys05

Here I just want to illustrate that I did not chose to include any input columns. We don’t operate on any input columns from the data flow in this situation, so leave them blank.

SurrogateKeys06

Inthis screen shot, notice that I’ve added one output column, ClientKey. It is a four-byte signed integer. This will be the name of the added column to the data flow.

SurrogateKeys07

In this screen shot the one thing I’m interested in is the fact that we’ve added the variable, MaxKey, to the list of ReadOnlyVariables. The next step involves the script itself, so click on that “Design Script…” button. The script follows:

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
Private NextKey As Int32 = 0

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim MaximumKey As Int32 = Me.Variables.MaxKey ‘ Grab value of MaxKey which was passed in

‘ NextKey will always be zero when we start the package.
‘ This will set up the counter accordingly
If (NextKey = 0) Then
‘ Use MaximumKey +1 here because we already have data
‘ and we need to start with the next available key
NextKey = MaximumKey + 1
Else
‘ Use NextKey +1 here because we are now relying on
‘ our counter within this script task.
NextKey = NextKey + 1
End If

Row.ClientKey = NextKey ‘ Assign NextKey to our ClientKey field on our data row
End Sub

End Class

And that’s it. Close the script and the designer for the script component. In the destination (or the next component after the script transformation) you’ll be able to use the new column, ClientKey in my case.