Saturday 5 February 2011

Generating a new SSIS Package GUID

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

No comments:

Post a Comment

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...