Friday, 4 February 2011

Create a GUID column in SSIS

How do you create a new Guid column in SSIS?

There is no SSIS function for that, but there are a few workarounds.

A) If you already have a valid GUID in your source, but it's still a string type, then you can use a Derived Column to create a real guid.
(DT_GUID) ("{" + YourGuid + "}")

B) If your source is a SQL Server table, you can use the TSQL statement to generate a Guid column.
NEWID() as Guid

C) Or you can use a Script Component to generate a new Guid Column:

1) Script Component
Add a Script Component in your Data Flow and select Transformation as the Script Component Type.
Transformation Type

2) Create new column
Edit the Script Component and goto the Inputs and Outputs tab. Expand the Output 0 and add a new column. The column type should be unique identifier [DT_GUID].
Add new Guid column

3) The Script
Edit the Script. Remove the PreExecute and PostExecute methods and add the following code to the Input0_ProcessInputRow method. That's all. Only one row of code.
// C# code
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

public class ScriptMain : UserComponent
    public override void Input0_ProcessInputRow(Input0Buffer Row)
        // Create a Globally Unique Identifier with SSIS
        Row.Guid = System.Guid.NewGuid(); 

' code
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

<microsoft.sqlserver.dts.pipeline.ssisscriptcomponententrypointattribute> _
<clscompliant(false)> _
Public Class ScriptMain
    Inherits UserComponent

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        ' Create a Globally Unique Identifier with SSIS
        Row.Guid = System.Guid.NewGuid()
    End Sub
End Class


  1. Hi This works like a charm. I need one help. It creates a 38 char GUID. Is there a way I can limit it to 35 char?

    1. You can use tostring to get other guid formats: For example:

      36chars: Guid.NewGuid().ToString("D");

      32chars: Guid.NewGuid().ToString("N")

  2. How to you store this value in a variable? In what variable data type?

    1. I would probably use a string variable:

  3. I copied your VB script and I'm getting the error new GUID is not a member of vbproj.input0buffer, so my result is a null. How do I get rid of this error message to make the script work and not get a null result ? -Roland

    1. Is the name of your new coluw "Guid"? See step two.


Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...