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.

Example

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();  
    }  
  }  
}  
Imports Microsoft.SqlServer.Dts.Runtime  

Module Module1  

  Sub Main()  

    Dim package As Package  
    package = New Package  

  End Sub  

End Module  

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\100\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();  
    }  
  }  
}  
Imports Microsoft.SqlServer.Dts.Runtime  

Module ApplicationTests  

  Sub Main()  

    ' The variable pkg points to the location of the  
    ' ExecuteProcess package sample that was installed with  
    ' the SSIS samples.  
    Dim pkg As String = _  
      "C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services" & _  
      "\Package Samples\ExecuteProcess Sample\ExecuteProcess\UsingExecuteProcess.dtsx"  

    Dim app As Application = New Application()  
    Dim p As Package = app.LoadPackage(pkg, Nothing)  

    ' Now that the package is loaded, we can query on  
    ' its properties.  
    Dim n As Integer = p.Configurations.Count  
    Dim p2 As DtsProperty = p.Properties("VersionGUID")  
    Dim pl As DTSProtectionLevel = p.ProtectionLevel  

    Console.WriteLine("Number of configurations = " & n.ToString())  
    Console.WriteLine("VersionGUID = " & CType(p2.GetValue(p), String))  
    Console.WriteLine("ProtectionLevel = " & pl.ToString())  
    Console.Read()  

  End Sub  

End Module  

Sample Output:

Number of configurations = 2

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

ProtectionLevel = DontSaveSensitive

External Resources

See Also

Adding Tasks Programmatically