Errors with SQL Cluster running .DTSX packages

I'm experiencing some frustration with my active/passive SQL cluster not running my .DTSX packages. I am hoping someone can shed some light on what I need to do.

I've created some .DTSX packages with the SQL Server Business Intel Dev Studio. I initially built & tested these packages in a non-SQL cluster environment without any problems. I'm now re-creating them to work on our SQL cluster. If I run the package through the Dev Studio it works great.

The packages basically grab .txt files from one of the shared drives (which is a resource of the sql cluster group) and imports the data into the one of the databases. The database does not have any special settings (right-click -> new database... -> enter name -> click ok).

I've setup a SQL Server Agent Job with 1 step with the following properties.

Step name: I Offices
Type: SQL Server Integration Services Package
Run as: SQL Agent Service Account
Package source: File system
Package: G:\ImportRAGFLOffices.dtsx

When I run the SQL Server Agent Job through MSSQL Server Management Studio (right-click -> start job) I get an error on "Execute job 'RAGFL TestJob'".

These are the 2 messages that show up when I view the history of the SQL Server Agent job.

***********************************************************************************************
Date 9/25/2007 1:16:13 PM
Log Job History (RAGFL TestJob)

Step ID 0
Server BADBOYS
Job Name RAGFL TestJob
Step Name (Job outcome)
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
The job failed. The Job was invoked by User sa. The last step to run was step 1 (I Offices).
***********************************************************************************************

***********************************************************************************************
Date 9/25/2007 1:16:13 PM
Log Job History (RAGFL TestJob)

Step ID 1
Server BADBOYS
Job Name RAGFL TestJob
Step Name I Offices
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: THEISLAND\Administrator. The package execution failed. The step failed.
***********************************************************************************************

THEISLAND is our internal domain.

There is a decent article about clustering SSIS in Expert SQL Server 2005 Integration Services from Wrox...

The following are the high level steps:

1. Install SSIS on all nodes that can use the service

2. Create a new cluster group (optional)

3. If you created a new group, create a virtual IP, name, drive as clustered resources.

4. Copy the MsDtsSrvr.ini.xml to the clustered drive

5. Modify the MsDtsSrvr.ini.xml file to change the location of hte packages

6. Change the Registry setting to point to the MsDtsSrvr.ini.xml file

7. Cluster the MsDtsServer service as a generic service

Also, see the following for more information on Clustering SSIS:

http://wiki.sqlis.com/default.aspx/SQLISWiki/ClusterSSIS.html

http://sqljunkies.com/WebLog/knight_reign/archive/2005/07/06/16015.aspx

|||

What a great name for a server!