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

	In SSMS browse to Security > Credentials Right click to create the account credentials Give the credentials  a name Enter the name of the NT account as well as the username and password for the account
TSQL	USE [master] CREATE CREDENTIAL [<Credential Name>] WITH IDENTITY = N’<DOMAIN>\<USERNAME>‘, SECRET = N’<Password>‘

 

Create the proxy account

	In SSMS  browse to SQL Server Agent > Proxies > SSIS Package Execution Right Click to create a new Proxy Give the Proxy a meaningful name Choose the credentials account created above Select the SSIS Integration Services Subsystem Click on the principals option 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>‘, @credential_name=N’<Name>‘, @enabled=1 EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’<Proxy Name>‘, @subsystem_id=11

 

Give the account the necessary permissions

	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 DEFAULT_SCHEMA=[dbo] ALTER AUTHORIZATION ON SCHEMA::[db_dtsltduser] TO [<Username>]

 

Add the package to SQL Server Agent

	In SSMS browse to SQL Server Agent > Jobs > New Job Give the job a name Add a step to the job Give the step a name Under Run as choose the name of the Proxy account created above Enter the name of the SQL Server where the package is stored Browse and Select the package 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>

except

SELECT *   FROM <table2>

 

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

• http://www.sqlservercentral.com/articles/SQL+Server+2005+-+SSIS/upgradingsqlserver2000dtspackagestossis/2201/
• http://blogs.msdn.com/psssql/archive/2009/02/19/how-to-copy-dts-2000-packages-between-servers-and-from-sql-2000-to-sql-2005-and-sql-2008.aspx
• http://msdn.microsoft.com/en-us/library/aa337083.aspx
• http://mohansmindstorms.spaces.live.com/Blog/cns!69AE1BEA50F1D0E7!213.entry
• http://www.sqlservercentral.com/Forums/Topic560372-391-1.aspx
• http://dougbert.com/blogs/dougbert/archive/2009/04/09/ssis-and-clustering-what-you-should-do-instead.aspx

Moving SQL Server user accounts

A number of databases currently  running on SQL Server 2000 are been upgraded to a new 2005 installation, this also requires moving the existing logins. Several options are available to achieve this but I found the method below to be the most straightforward.

Firstly make a backup of the existing database and restore it on the destination Server.

Next create a project in Integration Services (IS) and use the ‘transfer logins task’ to move the logins, to complete this the following properties need populating :

 

1. Setup source connection
2. Setup destination connection
3. for LoginsToTransfer Specify SelectedLogins
4. Choose the logins to move
5. Save and Execute the task

Ensure  all the logins on the source server  have all their attributes populated. When I initially ran the the IS package I got the error message “This property may not exist for this object, or may not be retrievable due to insufficient access rights” I traced the cause to   not having a default database associated with the login, this error message appeared for all logins that were missing the attribute, even if they weren’t been moved.

Once the IS task completes successfully;

On the destination server, for each of the SQL Server logins Execute the following TSQL code. I did not have to do this for the NT logins.

USE <Database Name>
GO
EXEC sp_change_users_login ‘UPDATE_ONE’, ‘<username>‘, ‘<login name>‘
GO

 

References

http://sqlblog.com/blogs/eric_johnson/archive/2008/10/17/fixing-orphaned-users.aspx