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