Setup Integration Services to run on a SQL Server cluster and migrate over a simple DTS package


Currently we have several SQL Server 2000 DTS packages running within a cluster ,they  use few features but move a significant chunk of data by executing  stored procedures, creating temporary tables and exporting the results to CSV files.  As part of the 2005 cluster  upgrade the packages needed to be either;

  1. Run as 2000 DTS  packages in the SQL Server 2005 environment
  2. Rewritten  Integration Services packages.

Both of these options have several flavours to them, for instance I could have embedded the DTS package as a Integration Services task.

Option 2 offered us the ability to take full advantage of our new 64 bit environment and meant we did not have to install the Backward compatibility components since the rewrite was straightforward this was the obvious choice.

The steps to complete this
  • Rewrite DTS package as a Integration Services Project
  • Configure Integration services to work with our 2005 cluster
  • Import the IS package into Integration services
  • Schedule the package in SQL Server Agent
  • Test the results obtained from the 2005 package against the 2000 results 
Rewrite DTS package as a Integration Services Project

This was straightforward, creating a new Integration services project I was only working with 2 components the Execute SQL task and Data flow task.

Configure Integration services to work with a clustered environment

This step is not necessary in order to get the packages to run, but it is useful for administrating them.

Integration services gets installed as part of a SQL Server Cluster, however it is not setup as a clustered resource and Microsoft do not recommend doing so.

Following  Microsoft’s instructions I had  to edit the MsDtsSrvr.ini.xml file on all nodes in the cluster, changing the <ServerName>.</ServerName> tag by  replacing localhost with the SQL Server cluster network name.


Next the  security settings in Component Services need to be modified. The “Launch and Activate permissions” need to be changed in two places;

  • Under Component Services > My Computer > Properties


  • Under  Component Services > DCOM Config > MsDtsServer >Properties


In both cases any accounts that will be connecting to Integration Services will need to appropriate permissions.

One other  point on this, it is only possible to connect to SQL 2005 Integration Services from the 2005 Management Studio, it it not possible to connect to  a 2005 server from the 2008 Management Studio

Import the IS package into Integration services

Not that it is possible to connect to Integration through SSMS. I can import the package into the MSDB database.

Firstly use SSMS to connect to Integration services, specifying the SQL Server cluster network name


Once connected add the package to the MSDB database



Scheduling the package in SQL Server Agent

When configuring the package to run as a scheduled task the Windows account under which the package will run requires permissions to execute the package and also possibly permissions to connect to data sources within the package depending on how it is configured. I looked at 2 choices to achieve this

  1. Give the SQL Server Agent Account the necessary permissions
  2. Create a proxy account

Looking ahead we will probably need to configure a number of different packages to run on the server over its lifetime, potentially these may be from different business entities, with this in mind using proxy accounts will give us more flexibility.

To  create a proxy account


Add the NT users credentials

  1. In SSMS browse to Security > Credentials
  2. Right click to create the account credentials
  3. Give the credentials  a name
  4. Enter the name of the NT account as well as the username and password for the account
  •      TSQL

USE [master]



SECRET = N’<Password>


Create the proxy account

  1. In SSMS  browse to SQL Server Agent > Proxies > SSIS Package Execution
  2. Right Click to create a new Proxy
  3. Give the Proxy a meaningful name
  4. Choose the credentials account created above
  5. Select the SSIS Integration Services Subsystem
  6. Click on the principals option
  7. Add the SQL Server Agent Account as a Proxy Account Principal, giving it access to the proxy account
  •      TSQL

USE [msdb]

EXEC msdb.dbo.sp_add_proxy

@proxy_name=N’<Proxy Name>,



EXEC msdb.dbo.sp_grant_proxy_to_subsystem

@proxy_name=N’<Proxy Name>, @subsystem_id=11


Give the account the necessary permissions

  1. Add the account to the  MSDB database and granting it the db_dtsltduser role

  •      TSQL

USE [msdb]

CREATE USER [<Username>] FOR

LOGIN [<Domain Name>\<Username>] WITH



ON SCHEMA::[db_dtsltduser] TO [<Username>]


Add the package to SQL Server Agent
  1. In SSMS browse to SQL Server Agent > Jobs > New Job
  2. Give the job a name
  3. Add a step to the job
  4. Give the step a name
  5. Under Run as choose the name of the Proxy account created above
  6. Enter the name of the SQL Server where the package is stored
  7. Browse and Select the package
  8. Save the step and enter a schedule for the package


To do a comparison between the old CSV and the new CSV file I ran the job I created above when the files were generated I imported them back into SQL Server tables, I also generated the files using the old DTS package but against the same data set, I imported this into a separate set of tables. I then compared the data in both tables using the TSQL except statement to compare the two tables

SELECT *   FROM <table1>


SELECT *   FROM <table2>


As part of doing some research for this, I found the following articles useful


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s