Friday 4 February 2011

Create a GUID column in SSIS

Case
How do you create a new Guid column in SSIS?

Solution
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;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        // Create a Globally Unique Identifier with SSIS
        Row.Guid = System.Guid.NewGuid(); 
    }
}

' VB.net 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

7 comments:

  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?
    Thanks

    ReplyDelete
    Replies
    1. You can use tostring to get other guid formats:
      http://msdn.microsoft.com/en-us/library/97af8hh4%28v=vs.110%29.aspx For example:

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

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


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

    ReplyDelete
    Replies
    1. I would probably use a string variable:
      System.Guid.NewGuid().ToString()

      Delete
  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

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

      Delete

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...