HOW TO: Programmatically create SSIS packages using Whidbey

Hi,

If you had read my last two articles, you would now be familiar with SSIS and how to create simple packages Business Intelligence Development Studio.

In this article, let us now check how to programmatically create a package/edit an existing package using the APIs provided with SQL Server 2005.

Let us start with creating a simple Console Application in Whidbey.

First of all, we need to add the following references to your Whidbey project:

1. Microsoft.SqlServer.DTSPipelineWrap.dll
2. Microsoft.SQLServer.DTSRuntimeWrap.dll
3. Microsoft.SQLServer.ManagedDTS.dll

The reference dlls can be found in the following location:
C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies

You must then add the namespace references in the Program.cs file as follows:

#region Using directives
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
#endregion

An SSIS package can be created using the Package class present in the Microsoft.SqlServer.Dts.Runtime namespace as follows:

Package pkg = new Package();
pkg.Name = "MyCreatedPackage";
pkg.ID = "MyPackageID";

Now lets see how to take an existing package, modify its properties and save the package to your file system or to Yukon server.

We begin with defining a Package object and an Application object.

Package pkg = new Package();
Application a = new Application();

The Application class is used to discover and access Package objects.
Next we declare the MainPipe interface which is used to programmatically configure the data flow task. The IDTSComponentMetaData90 Interface contains the definition of a data flow component; including the custom properties, inputs, outputs, and input and output columns defined on a data flow component.

IDTSComponentMetaData90 oledbSource;
IDTSComponentMetaData90 oledbDestination;
MainPipe dataFlow;

In case you are trying to modify the properties of an existing package, you must first load the external package that you already have as a template.

pkg = a.LoadPackage(@"C:\TestPackages\MyPackage.dtsx", null);

You must now add the connection using the ConnectionManager class and set connection properties. The ConnectionManager class contains all the information necessary to connect to a single type of data source.
//Add connections
ConnectionManager conMgr = pkg.Connections.Add("OLEDB");

/// Set stock properties
conMgr.Name = "OLEDBConnection";
conMgr.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Test;Data Source=mdpkb2e139;Auto Translate=False;";

The next step is to add/edit the data flow task. The following is the code to add a Data Flow Task and set its properties:

TaskHost th = pkg.Executables.Add("DTS.Pipeline") as TaskHost;
th.Name = "DataFlow";
dataFlow = th.InnerObject as MainPipe;

//set source component
oledbSource = dataFlow.ComponentMetaDataCollection.New();
oledbSource.ComponentClassID = "DTSAdapter.OLEDBSource";
oledbSource.Name = "OLEDBSource";
CManagedComponentWrapper instanceSource = oledbSource.Instantiate();

oledbSource.RuntimeConnectionCollection.New();
oledbSource.RuntimeConnectionCollection[0].ConnectionManagerID = pkg.Connections["OLEDBConnection"].ID;

oledbSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(pkg.Connections["OLEDBConnection"]);
instanceSource.ProvideComponentProperties();
instanceSource.SetComponentProperty("OpenRowset", "Emp");
instanceSource.SetComponentProperty("AccessMode", 0);

// Acquire Connections and reinitialize the component
instanceSource.AcquireConnections(null);
instanceSource.ReinitializeMetaData();
instanceSource.ReleaseConnections();

In case you already have a template with the data flow task, we can get the handle of it using the Executable collection. The following is the code for modifying a DataFlow task:

Executable exe = x.Executables["Data Flow Task"];
TaskHost th2 = exe as TaskHost; d
ataFlow = th2.InnerObject as MainPipe;
IDTSComponentMetaDataCollection90 metadataCollection = dataFlow.ComponentMetaDataCollection;
//set source component
oledbSource = dataFlow.ComponentMetaDataCollection[0];
if (oledbSource.RuntimeConnectionCollection.Count > 0)
{
oledbSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(x.Connections["OLEDBConnection"]);
oledbSource.RuntimeConnectionCollection[0].ConnectionManagerID = x.Connections["OLEDBConnection"].ID;
}
CManagedComponentWrapper instanceSource = oledbSource.Instantiate();

instanceSource.SetComponentProperty("OpenRowset", "Temp_1");
instanceSource.SetComponentProperty("AccessMode", 0);

// Acquire Connections and reinitialize the component
instanceSource.AcquireConnections(null);
instanceSource.ReinitializeMetaData();
instanceSource.ReleaseConnections();

The same steps are followed for setting the properties of the destination component. The following is the code for modifying the connection properties of OLEDB Destination component:

//set destination component
oledbDestination = dataFlow.ComponentMetaDataCollection[1];
if (oledbDestination.RuntimeConnectionCollection.Count > 0)
{
oledbDestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(x.Connections["OLEDBConnection"]);
oledbDestination.RuntimeConnectionCollection[0].ConnectionManagerID = x.Connections["OLEDBConnection"].ID;
}
CManagedComponentWrapper instanceDest = oledbDestination.Instantiate();
instanceDest.SetComponentProperty("OpenRowset", "Temp_2");
instanceDest.SetComponentProperty("AccessMode", 0);
// Acquire Connections and reinitialize the component
instanceDest.AcquireConnections(null);
instanceDest.ReinitializeMetaData();
instanceDest.ReleaseConnections();

The next step is to "Map" the columns of the source and destination components. The following is the code to map the input column collection (source collection) to the External Metadata column collection (Destination collection):

IDTSInput90 input = oledbDestination.InputCollection[0];
IDTSVirtualInput90 vInput = input.GetVirtualInput();
foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
IDTSInputColumn90 vCol = instanceDest.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READWRITE);
instanceDest.MapInputColumn(input.ID, vCol.ID, input.ExternalMetadataColumnCollection[vColumn.Name].ID);
}

Finally we save the package to the file system.

string currentDirectory = System.IO.Directory.GetCurrentDirectory();
a.SaveToXml(currentDirectory + "\\DTSDataflow.dtsx", pkg, null);
Console.WriteLine("Successfully created an SSIS package");
Console.ReadLine();

In case you want to save the package directly to Yukon server, you can use the following code:

a.SaveToSqlServer(pkg, null, "mdpkb2e139", "", "");

Here it goes! You have now programmatically created a simple SSIS package.