Generating a new SSIS Package GUID

Case
When you copy a SSIS package, the GUID that identifies the package, remains the same. So far no problem, but when you enable logging this value is logged as the SourceID. If you see a package error in your log and a couple of packages have the same GUID, it's hard to see which package caused the error. How do I change duplicate GUIDS?

Solution
It's a bug, but there are a couple or solutions.

A) Visual Studio / BIDS

When you copy a package, open the new package and goto the properties. Find the ID, click on the arrow and select <Generate New ID>. A new GUID will be generated and you can save your package to finish.

Generate New ID

B) dtutil

You can use dtutil Utility to change the GUID.

dtutil /FILE "myFirstPackage.dtsx" /IDRegenerate

Or abbreviated

dtutil /FILE "myFirstPackage.dtsx" /I
This action creates a new GUID for the newly-copied package to distinguish it from the original

You can even use the FOR command to give a whole bunch of file-based packages a new GUID at once:
for %f in (N:\folder\*.dtsx) do dtutil /IDRegenerate /FILE %f
Use a single percent sign (%) when typing the command at the command prompt. Use a double percent sign (%%) if the command is used inside a batch file.

C) Script Task
If you have a whole bunch of packages (file- or server-based) and you're not sure which one have duplicate GUIDS, you can use a Script Task to give them all new GUIDS.

Create a new package and add a Script Task to the Control Flow. Edit the Script Task and replace you Main()-method with this one.

For file-based packages:

//File-based GUID changer
using System;
using System.Data;
using System.IO; // Added
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_fb40a32cd5d74b8da11ec9720443960a.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        public void Main()
        {
            /////////////////////////////////////////             
            // Get all package from a folder.
            string[] packages = Directory.GetFiles(@"d:\Integration Services Project1\", "*.dtsx");

            /////////////////////////////////////////             
            // Set application context
            Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();

            /////////////////////////////////////////             
            // Loop through the packages
            foreach (string filepath in packages)
            {
                /////////////////////////////////////////             
                // Open package, Generate new GUID and save package.
                Package localPackage = app.LoadPackage(filepath, null);
                string oldGuid = localPackage.ID.ToString();
                localPackage.RegenerateID();
                app.SaveToXml(filepath, localPackage, null);

                /////////////////////////////////////////             
                // Log old and new GUID
                bool fireAgain = true;
                Dts.Events.FireInformation(0, "Change GUID", "Package: " + localPackage.Name + ", Old GUID: " + oldGuid + ", New GUID: " + localPackage.ID.ToString(), string.Empty, 0, ref fireAgain);   
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

For SQL server-based packages:

//SQL server-based GUID changer
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_fb40a32cd5d74b8da11ec9720443960a.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        public void Main()
        {
            /////////////////////////////////////////             
            // The SQL Server
            String SSISServer = "Name_Of_Your_Server"; 
            
            /////////////////////////////////////////             
            // Set application context
            Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();

            /////////////////////////////////////////             
            // Create a package variable to temporary store the server packages
            Package serverPackage;
            
            /////////////////////////////////////////
            // Loop through packages and folders in the root: / (or change the path for your folder)
            foreach (PackageInfo serverPackageInfo in app.GetPackageInfos("/", SSISServer, null, null))
            {
                // Get only the packages. You could make a recursive function to loop through all folders
                if (serverPackageInfo.Flags.ToString().ToLower().Equals("package"))
                {
                    /////////////////////////////////////////             
                    // Open package, Generate new GUID and save package.
                    serverPackage = app.LoadFromSqlServer(serverPackageInfo.Folder + serverPackageInfo.Name, SSISServer, null, null, null);
                    string oldGuid = serverPackage.ID.ToString();
                    serverPackage.RegenerateID();
                    app.SaveToSqlServer(serverPackage, null, SSISServer, null, null);

                    /////////////////////////////////////////             
                    // Log old and new GUID
                    bool fireAgain = true;
                    Dts.Events.FireInformation(0, "Change GUID", "Package: " + serverPackage.Name + ", Old GUID: " + oldGuid + ", New GUID: " + serverPackage.ID.ToString(), string.Empty, 0, ref fireAgain); 
                }
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

Note: the folderpath and servername are hardcoded because you probably won't run this package on a regular base. But you can use variables to remove the hardcoded strings.

Note: If you want to reset the GUIDS of tasks, you should use this open source application: BIDS Helpeer

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

Create a Row Id

Case
If you add records to a database table with an ID column, you preferably would use an identity column (Identity Specification, Is Identity = Yes. But what if your destination does not support an auto-identity or you're not allowed to modify it? You could do this with our Rownumber Component or a Third Party component, or..

Solution
You can use a Script component to accomplish an auto-identity column. In this example I will get the highest ID from a table and use that number as a starting number for new records.

1) Add variable
Add an integer variable named Counter to store an ID.

Right click in Control or Data Flow to show variables

2) Add Execute SQL Task and Data Flow Task
Add an Execute SQL Task to your Control Flow and add a Data Flow Task right behind it.

Execute SQL Task

3) Get max ID
Edit the SQL task and change the ResultSet from None to Single Row. Select the right Connection and enter the query to get the highest ID.

General Tab of  the Execute SQL Task

-- Get highest ID
SELECT  MAX([Id]) as MaxId
FROM    [YourTable]

4) Result Set
Continue editing the SQL task and go to the Result Set tab. Connect the field MaxId from the query to your variable Counter. After this the Execute SQL Task is ready.

Result Set

5) DataFlow
Now go to your Data Flow. Add a random source, a Script Component (transformation) and a destination (the same table as in your Execute SQL Task. Give them suitable names. The result should look something like this.

Data Flow

6) The script Component
We need a new column to store the RowId in. Add a new column on the tab Inputs and Outputs. The type should be an integer, size depends on the column size in your database table.

New column RowId

7) The script itself
SSIS create 3 methods for you: PreExecute to get the MaxId from the variable, Input0_ProcessInputRow to fill the new column RowId and optional PostExecute to fill the variable with the new MaxId after all the records have passed. This third method is only required if your need that number somewhere else.

// C# code: surrogate key script
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
{
    // New internal variable to store the rownumber
    private int rowCounter = 0;

    // Method that will be started before the rows start to pass
    public override void PreExecute()
    {
        base.PreExecute();

        // Lock variable for read 
        VariableDispenser variableDispenser = (VariableDispenser)this.VariableDispenser;
        variableDispenser.LockForRead("User::Counter");
    
        IDTSVariables100 vars;
        variableDispenser.GetVariables(out vars);

        // Fill the internal variable with the value of the SSIS variable
        rowCounter = (int)vars["User::Counter"].Value;

        // Unlock variable
        vars.Unlock();
    }

    // Method that will be started after all rows have passed
    // This method is optional. Only add it if you are gonna
    // use the SSIS variable after the dataflow is finished.
    public override void PostExecute()
    {
        base.PostExecute();

        // Lock variable for write
        VariableDispenser variableDispenser = (VariableDispenser)this.VariableDispenser;
        variableDispenser.LockForWrite("User::Counter");

        IDTSVariables100 vars;
        variableDispenser.GetVariables(out vars);

        // Fill the SSIS variable with the value of the internal variable
        vars["User::Counter"].Value = rowCounter;

        // Unlock variable
        vars.Unlock();
    }

    // Method that will be started for each record in you dataflow  
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        // Seed counter
        rowCounter++;
        // Fill the new column
        Row.RowId = rowCounter;
    }
}

Or VB.net

' VB.Net code: surrogate key 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

    ' New internal variable to store the rownumber
    Private rowCounter As Integer = 0

    ' Method that will be started before the rows start to pass 
    Public Overrides Sub PreExecute()
        MyBase.PreExecute()

        ' Lock variable for read  
        Dim variableDispenser As VariableDispenser = CType(Me.VariableDispenser, VariableDispenser)
        variableDispenser.LockForRead("User::Counter")

        'Use IDTSVariables90 if you're using SSIS 2005
        Dim vars As IDTSVariables100
        variableDispenser.GetVariables(vars)

        ' Fill the internal variable with the value of the SSIS variable
        rowCounter = CInt(vars("User::Counter").Value)

        ' Unlock(Variable)
        vars.Unlock()
    End Sub

    ' Method that will be started after all rows have passed
    ' This method is optional. Only add it if you are gonna
    ' use the SSIS variable after the dataflow is finished.
    Public Overrides Sub PostExecute()
        MyBase.PostExecute()

        ' Lock variable for write 
        Dim variableDispenser As VariableDispenser = CType(Me.VariableDispenser, VariableDispenser)
        VariableDispenser.LockForWrite("User::Counter")

        'Use IDTSVariables90 if you're using SSIS 2005
        Dim vars As IDTSVariables100
        variableDispenser.GetVariables(vars)

        ' Fill the SSIS variable with the value of the internal variable 
        vars("User::Counter").Value = rowCounter
        ' Unlock variable 
        vars.Unlock()
    End Sub

    ' Method that will be started for each record in you dataflow   
    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        'Seed counter
        rowCounter = rowCounter + 1
        ' Fill the new column
        Row.RowId = rowCounter
    End Sub
End Class

8) Map in Destination
Make sure you don't forget to map the new column RowId in your destination. Now run your package to see the result.

An other option to create an unique RowId is to use a GUID instead of an integer. The Script component needs only one method for this solution.

// C# code
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    // Create a Globally Unique Identifier with SSIS
    Row.Guid = System.Guid.NewGuid();
}

' VB.Net code
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
    ' Create a Globally Unique Identifier with SSIS
    Row.Guid = System.Guid.NewGuid()
End Sub

Detailed information about that can be found here.