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
work it!, thank's
ReplyDeleteHi 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?
ReplyDeleteThanks
You can use tostring to get other guid formats:
Deletehttp://msdn.microsoft.com/en-us/library/97af8hh4%28v=vs.110%29.aspx For example:
36chars: Guid.NewGuid().ToString("D");
32chars: Guid.NewGuid().ToString("N")
How to you store this value in a variable? In what variable data type?
ReplyDeleteI would probably use a string variable:
DeleteSystem.Guid.NewGuid().ToString()
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
ReplyDeleteIs the name of your new coluw "Guid"? See step two.
Delete