Export (0) Print
Expand All
2 out of 13 rated this helpful - Rate this topic

Creating a Package Programmatically

The Package object is the top-level container for all other objects in an SSIS project solution. As the top-level container, the package is the first object created, and subsequent objects are added to it, and then executed within the context of the package. The package itself does not move or transform data. The package relies on the tasks it contains to perform the work. Tasks perform most of the work performed by a package, and define the functionality of a package. A package is created and executed with just three lines of code, but various tasks and ConnectionManager objects are added to give additional functionality to your package. This section discusses how to programmatically create a package. It does not provide information about how to create the tasks or the ConnectionManager. These are covered in later sections.

To write code using the Visual Studio IDE, a reference to Microsoft.SqlServer.ManagedDTS.DLL is required in order to create a using statement (Imports in Visual Basic .NET) to the Microsoft.SqlServer.Dts.Runtime. The following code sample demonstrates creating an empty package.

using System;
using Microsoft.SqlServer.Dts.Runtime;

namespace Microsoft.SqlServer.Dts.Samples
{
  class Program
  {
    static void Main(string[] args)
    {
      Package package;
      package = new Package();
    }
  }
}

To compile and run the sample, press F5 in Visual Studio. To build the code using the C# compiler, csc.exe, at the command prompt to compile, use the following command and file references, replacing the <filename> with the name of the .cs or .vb file, and giving it an <outputfilename> of your choice.

csc /target:library /out: <outputfilename>.dll <filename>.cs /r:Microsoft.SqlServer.Managed DTS.dll" /r:System.dll

To build the code using the Visual Basic .NET compiler, vbc.exe, at the command prompt to compile, use the following command and file references.

vbc /target:library /out: <outputfilename>.dll <filename>.vb /r:Microsoft.SqlServer.Managed DTS.dll" /r:System.dll

You can also create a package by loading an existing package that was saved on disk, in the file system, or to SQL Server. The difference is that the Application object is first created, and then the package object is filled by one of the Application's overloaded methods: LoadPackage for flat files, LoadFromSQLServer for packages saved to SQL Server, or LoadFromDtsServer for packages saved to the file system. The following example loads an existing package from disk, and then views several properties on the package.

using System;
using Microsoft.SqlServer.Dts.Runtime;

namespace Microsoft.SqlServer.Dts.Samples
{
  class ApplicationTests
  {
    static void Main(string[] args)
    {
      // The variable pkg points to the location of the
      // ExecuteProcess package sample that was installed with
      // the SSIS samples.
      string pkg = @"C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services" +
        @"\Package Samples\ExecuteProcess Sample\ExecuteProcess\UsingExecuteProcess.dtsx";

      Application app = new Application();
      Package p = app.LoadPackage(pkg, null);

      // Now that the package is loaded, we can query on
      // its properties.
      int n = p.Configurations.Count;
      DtsProperty p2 = p.Properties["VersionGUID"];
      DTSProtectionLevel pl = p.ProtectionLevel;

      Console.WriteLine("Number of configurations = " + n.ToString());
      Console.WriteLine("VersionGUID = " + (string)p2.GetValue(p));
      Console.WriteLine("ProtectionLevel = " + pl.ToString());
      Console.Read();
    }
  }
}

Sample Output:

Number of configurations = 2

VersionGUID = {09016682-89B8-4406-AAC9-AF1E527FF50F}

ProtectionLevel = DontSaveSensitive

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.