If you are trying to generate Uniqueidentifier/Newid() in SSIS data flow, you will soon realize that there is no out-of-the-box transformation you could use. Fear not. Script Component can be used to create Uniqueidentifier columns.
In the below package, I’ve an OLE DB source. Then, i dragged script component to the data flow. I selected transformation as the script component type and connected green arrow from source to script transformation.
Next, open the script transformation editor. In the Inputs and Outputs page, expand Output 0, select output columns and click add column in the bottom, give it a name, select unique identifier [DT_GUID] as the data type, and click ok. I named mine UniqueId.
In the script page, make sure Microsoft Visual Basic 2008 is selected as the scripting language. I just like working with VB. Click edit script, then copy paste the following script.
' Microsoft SQL Server Integration Services Script Component
' Write scripts using Microsoft Visual Basic 2008.
' ScriptMain is the entry point class of the script.
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)
Row.UniqueId = System.Guid.NewGuid()
End Sub
End Class
When you add a destination, UniqueId will be available as a new column in the mappings page.
