Creating a Package Programmatically


Applies To: SQL Server 2016 Preview

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.

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.

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())  
  End Sub  
End Module  

Sample Output:

Number of configurations = 2

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

ProtectionLevel = DontSaveSensitive

Integration Services icon (small)  Stay Up to Date with Integration Services
For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN:

 Visit the Integration Services page on MSDN

For automatic notification of these updates, subscribe to the RSS feeds available on the page.

Adding Tasks Programmatically

Community Additions