How to update existing custom connection manager without breaking existing packages

Question

We have an existing MQ custom connection manager that's currently being used by several existing SSIS packages.

I want to add a new property and modify the code a little bit, but if I do that, it looks like I am breaking everything else (have to redo all of them).

Is there a way where I can get around this without disrupting the existing packages that use it?

Answer 1

Assuming that your you need to edit the SSIS package by adding the property and RUN it. After that you don't need the SSIS package. Following code method should be added to your application and call this method. So all the changes will be applied only to the new package without modifying another package.

1. Get the SSIS package
2. Create the copy of it by appending the GUID. so your package name is like PackageName_GUID.dtsx
3. Add your property.
4. RUN your SSIS package
5. OnSuccess full execution. Delete the SSIS package.

Code

    public static DtsErrors RunSSISPackage(string packagePath, string MQProperty)
    {
             * Append the auto generated GUID with the package name for running the SSIS package
             */
            string uniqueId = Guid.NewGuid().ToString();

            string uniquePackage = Path.GetDirectoryName(packagePath) + @"\" + Path.GetFileNameWithoutExtension(packagePath) + "_" + uniqueId + ".dtsx";

            File.Copy(packagePath, uniquePackage);

            Package pkg;
            Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();

            pkg = app.LoadPackage(uniquePackage, null);

            //MessageBox.Show(srcFileName);
            //MessageBox.Show(TPODBConnection);

            pkg.Connections["MQConnection"].<<YourPropertyName>> = MQProperty;

            //Uncomment this to overwrite the existing file
            //Do nothing until you are using a version control system
            //app.SaveToXml(packagePath, package, null);

            DTSExecResult result = pkg.Execute();

            if (result == DTSExecResult.Failure)
            {
                return pkg.Errors;
            }

            File.Delete(uniquePackage);

        return null;
    }