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 
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!
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!
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!
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.
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.
Shishir, you can change any property you like. Connection managers are one of the easiest objects to play with.