Modifying an SSIS Package through code

Part of any SSIS development experience inevitably results in you discovering a minor mistake or something that was missed a long way into the development cycle – or even after, in testing (you do test your code, right?). Then you are faced with the tedious job of opening every single package, making a change in every one… and getting some serious mouse finger. Much like I once did when I learned about BufferTempStoragePath.

Fortunately, there is a way to automate these fixes. The SSIS Object model is (relatively) easily manipulated through .NET languages – so it’s not too difficult to write a small program that will change your package. Below is a sample I knocked up that will add a variable to an existing package and save the change:

using System;
usingMicrosoft.SqlServer.Server;
usingMicrosoft.SqlServer.Dts.Runtime;

namespace Package_Modifier
{
    class Program
    {
        static void Main(string[] args)
        {
            // Initialize an Application and Package object
            Application app = new Application();
            Package package = null;

            // Set a package path
            StringpkgPath = “C:\\BI Monkey\\SamplePackage.dtsx”;

            // Load the package in package object
            package = app.LoadPackage(pkgPath, null);

            // Add the new variable
            package.Variables.Add(“NewVar”, false, “User”, 0);

            // Save the package
            app.SaveToXml(pkgPath, package, null);
          }
    }
}

You can essentially make any change you like to a package – I’ve chosen adding a variable because it’s an easy manipulation of the package object and I’ve got a long way to go before I work out how to do anything much harder :)

9 thoughts on “Modifying an SSIS Package through code

  1. Thanks :) I wish this was out a month ago before I changed manually 60+ packages to add a variable and set a property based on it..next time I’ll know better!

  2. Thanks James, it’s a very useful idea! I think everyone has stumbled to the need of modifying multiple SSIS packages in the same way. Especially when an implemented framework has to by altered. I had tried modifying the XML code directly but most of the times that’s really not the way to go so I gave up quite soon. But this could be a usable solution if one can master it :)
    Good job!

  3. Thank you James, was exactly what I was looking for.
    Now, I have a small question regarding to this article. I’m trying to create a package that will create the connection manger dynamically by a C# script. What I do not understand is why I’m not able to see the new connection after executing the task specifically.
    The code is over here:

    public void Main()
    {
    Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
    Package p = app.LoadPackage(@”C:\Package.dtsx”, null);
    ConnectionManager ConMgr;
    ConMgr = p.Connections.Add(“OLEDB”);
    ConMgr.ConnectionString = @” Some Connection String”;
    ConMgr.Name = “Test”;
    ConMgr.Description = “This is a Test”;

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

    Am I missing something?
    Thanks!

  4. I don’t think you can actually modify the package once it is loaded into memory at runtime. My script only works on packages living on disk.

    What your script is doing is loading an instance of a package into the script’s local memory space and modifying that instance. It’s not altering the instance of the package that is executing the script, if that makes sense.

  5. Hey James, Nice article. I want to know can we update the connection string of the package.

    I.e. I have fetch all the packages from Solution and filled into List control. On selection of any one package, I am displaying information in grid below like
    Connection Name | Connection String
    Now I want to update this Connection String and save the operation.

    Can you please help me for the same if its possible.

  6. Hi James

    As you mention, the SSIS object model is (relatively) easy to modify. But without a nice start up, like the code you shared, can be a bit difficult to start.

    Thank you very much for your article.

    As I am a bit rusty with my programming skills, I ended up using something I am familiar with, the SSIS Script Task. Combined it with a For Each Loop and modifying 150 packages was quite fun.

  7. I am trying to get all the variables from the package , and show it in datagrid view . Then wanted to edit if any values of the variable. Once the edit is done i wanted to save the package.

    I am able to edit and saveasxml in the C# console app. But wanted to do multiple value for the exisiting variables in the package. Anybody has done the same earlier kindly let me know.

    TIA….

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>