Friday, May 06, 2005

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.

16 comments:

Maxim Donde said...

Hello,
The pkg.Execute function returns only an execution status. Is there any way to get the result itself?
For example, If the package destination is ADO.NET DataReader how can I read the data from this reader?

Oystein said...

Very nice article.

I tried to do the same using VS2005 August CTP connection to a SQL Server September CTP, but I could not find the correct files to reference.

Have you tried this on the latest versions?

Sincerely,

- Oystein Saebo -

Kim said...

Hey, I came by actually looking for a specific blog but got yours instead. However, you seem to have a great blog here!

I have a free articles site. It pretty much is about free articles .

Come and take a look if you get time :-)

blogdollar2 said...

hi, i am learning about blogs, I started a blog about information . If you would like to share a link, please let me know.

hosuronline.com said...

I came here from google searching for the term "packaging industries" and end up here.

Any how i read some useful techie contents here.

best regards

pepper77 said...

i tried your code in vb.net and i get an error at line

nstancesource.AcquireConnections(Nothing)

error:
Exception from HRESULT: 0xC0208035

i am using excel file as source and sql server as destination.

any idea?

benamor said...

I've got the same error as pepper77 -

Exception from HRESULT: 0xC0208035

googling this error brings me straight back to this page!

I'm using the sample code pretty much without alteration so my source and destination are both OLEDB connections (if I'm reading the code correctly!)

indra said...

I'm getting a similar error...
Exception from HRESULT: 0xC0208035..

Any ideas

rajagadu said...

hi lakshmi,

i am able to add new tasks to the existing paackage.

but it is giving me error when try to move the task after creation.

this is error:

the element cannot be found in a collection. this error happens when you try to retrieve an element from a collection on a container during the execution of the package and the element is not there.


plz help me
sanjay
sanjay.bollina@gmail.com

Suresh A M said...

Hi lakshmi,
I developed simple package that to copy all the rows from FlatFile to SQLServer2005.In that, i couldnt use ColumnType "RaggedRight". it is giving error while executing. and if use ColumnType as "FixedWidth", then there were not an error,executed successfully. But the rows inserted has wrong format.At the end of each row, extra two spaces are coming and that two get inserted in the seond row. so the datas are Zigzag in database.

So please assist me on how to use "raggedright" option and how to avoid the empty spaces at the end of the row(Note:in source file there is no empty spaces. it is perfect only) or please tell me how to use "FixedWidth".

JaiPrakash said...

HI! Lakshmi Please Replay.
I tried your code in vb.net and i get an error at line

instancesource.AcquireConnections(Nothing)

error:
Exception from HRESULT: 0xC0208035

$Manpreet$ said...

Hi
there are 2 variables X and a in ur code..
what are they..??
thanks in advance

Harika said...

Hi ,
I created sql task ,I want to copy result set to variable.
How can i achieve this through programming

sagar said...

Hi,

I created a sample SSIS package; however, I had problem excessing in one of the operation.

In OLE DB Destination task, I could map the input columns to the output columns. Now, when I wanted to get the names of those mapped columns, I am looking at the computer like a dumb @$$.

hanasoft said...

This is some fix for errors

1. change this part of code from :

"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();"

to this one:

"CManagedComponentWrapper instanceSource=oledbSource.Instantiate();

instanceSource.ProvideComponentProperties();"

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

oledbSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(pkg.Connections["OLEDBConnection"]);"







basically, just put:
instanceSource.ProvideComponentProperties();

before any... "oledbSource.RuntimeConnectionCollection."

in other word..ProvideComponentProperties must be stated first.




2. Replace all "x" to "pkg"


hope's that'll help :)

sandeep said...

Hi Lakshmi,

I am creating an application wherein user can map data between source and destination tables with some data conversion in a visual environment. I need to create an SSIS package based on this input. How I can proceed with this. I may create some dlls for data conversion using C# code can I include them in package. I am a new bee in this world please tell what is possible and whats not.