Invoking a DTS Package from BizTalk 2009 x64

July 26, 2011 at 12:40 pm 1 comment

It is surprisingly familiar how many companies still use Microsoft SQL Server 2000 in enterprise architectures. This week I am building a BizTalk 2009 application that needs to perform certain FTP tasks and finally execute a DTS package stored in a SQL Server 200 cluster.

The interesting point about this, besides the need to interact with an outdated transformation technology, is that the SQL Server installation is x86 while the BizTalk Server architecture if fully x64. In mo0st cases this shouldn’t be a problem but because of the way the connectivity is implemented you need to make a few adjustments to the helper class built to implement this.

To accomplish the execution of the DTS package you need to create a wrapper for the COM object used. The steps are as follows:

1) Fist of all you are going to need to download and install the Microsoft SQL Server 2005 Backward Compatibility Components. In my case and because my BizTalk installation is x64 I downloaded the X64Package. The installer creates the DLLs needed to have runtime support for the DTS packages in the C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn folder.

If you are not interested in accessing DTS packages from SQL Management Studio then you should only select the Data Transformation Services 2000 runtime option in the Feature Selection step in the installation wizard.

image

2) Once the installation finishes you need to register the Service Provider module for the Microsoft SQL Server. You accomplish this executing the following command from a Visual Studio 2008 Command Prompt:

image

3) Now you need to convert the type definitions found within the COM type library into equivalent definitions in a common language runtime assembly. This creates a wrapper for the COM dll we registered in the previous step so we can instantiate it in our helper class. First you need to create a strong name key for the wrapper:

image

4) Next, you need to execute the tblimp.exe importer from a Visual Studio 2008 Command Prompt:

image

5) Once the wrapper is created you can proceed to register it in the GAC:

image

6) Now you go to your helper class project in Visual Studio 2008 and add the proper reference to the wrapper dll created before:

image

7) As I stated before the DTS COM dll is a x86 component and our project is x64 so you need to change the platform target of your helper class to x86

image

Failing to do so will result in the following error when executing the code:

“Retrieving the COM class factory for component with CLSID {10020200-EB1C-11CF-AE6E-00AA004A34D5} failed due to the following error: 80040154.”

8)  Now you are ready to write down the code to execute the DTS package. I modified the following code from AzamSharp with some adjustments:

using DTS = Microsoft.SqlServer.DTSPkg80;

public static void RunDTSPackage(string serverName, string user, string password, string packageName)
{
    object pVarPersistStgOfHost = null;

    DTS.Package package = new DTS.Package();
    package.LoadFromSQLServer(serverName, user, password, DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, 
        null, null, null, packageName, ref pVarPersistStgOfHost);

    try
    {
        package.Execute();
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }

    finally
    {
        package.UnInitialize();
        package = null; 
    }
}

If you want to know more about how to edit, run and manage DTS packages in SQL Server 2008 read the following article at MSDN http://msdn.microsoft.com/en-us/library/bb500440.aspx

Advertisements

Entry filed under: BizTalk.

SharePoint Editions Comparison Maximize HTTP Connections in BizTalk

1 Comment Add your own

  • 1. Milford  |  October 18, 2012 at 1:38 pm

    Thanks for the article!

    I faced the problem you mentionned when executing a DTS on my development machine (SQL 2008 Express only):

    Installing the SQL Server 2005 Backward Compatibility module and switching the application to x86 solved the problem 🙂

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Trackback this post  |  Subscribe to the comments via RSS Feed


July 2011
M T W T F S S
« Jun   Aug »
 123
45678910
11121314151617
18192021222324
25262728293031

Categories


%d bloggers like this: