Search code examples
sqlguidssisflat-file

How can I add a GUID column in SSIS from a Flat File source before importing to a SQL Table?

I'm importing flat files through SSIS which then exports them into a SQL table. I need to add an additional column containing a GUID somewhere in the middle so that it can also be exported to the table.

I've made sure there's an additional column ready in the SQL Table for the GUID to be passed into but I'm unsure of how to create the GUID in the package, any ideas?

Thanks

Solution

  • You can do this via a Script Component Transformation.

    In your data flow task, between source and destination, add the script component. Under 'Inputs and Outputs' add an output column, name it as you like and in Data Type Properties give it DataType of unique identifier [DT_GUID]

    Use this script (Make sure ScriptLanguage is VB.net):

    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