Modifying an SSIS Package through code

[James Beresford]

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



Get More from the original blog...