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!

ERROR: SQLSERVERAGENT COULD NOT BE STARTED(ERROR CREATING A NEW SESSION)

I keep on getting this msg on a active/passive win2003 cluster environment
with SQL 2005
ERROR: SQLSERVERAGENT COULD NOT BE STARTED (ERROR CREATING A NEW SESSION)
(sqagtres) online thread: error 435 brining Resource online.
(sqagtres) online Thread: ResUtilsStartResouceService failed (status 435)
Thank you in avance
>>> On 12/4/2006 at 10:16 AM, in message
<esNUSh8FHHA.3616@.TK2MSFTNGP06.phx.gbl>, Peter<Vik> wrote:
> ERROR: SQLSERVERAGENT COULD NOT BE STARTED (ERROR CREATING A NEW
> SESSION)
> (sqagtres) online thread: error 435 brining Resource online.
> (sqagtres) online Thread: ResUtilsStartResouceService failed (status
> 435)
If you are using AWE, you need to give the agent user (in Windows) the
lock pages in memory permission.
|||Thank you for your quick response, but what's AWE and which lock pages?
"Joel Maslak" <jmasla@.state.wy.us> wrote in message
news:4573F823.BB84.007F.0@.state.wy.us...
> <esNUSh8FHHA.3616@.TK2MSFTNGP06.phx.gbl>, Peter<Vik> wrote:
> If you are using AWE, you need to give the agent user (in Windows) the
> lock pages in memory permission.
|||>>> On 12/4/2006 at 10:41 AM, in message
<OtdTgv8FHHA.3468@.TK2MSFTNGP04.phx.gbl>, Peter<Vik> wrote:
> Thank you for your quick response, but what's AWE and which lock
> pages?
You probably aren't using AWE if you don't know about it. You have to
provide /PAE as a boot command-line option, and it only applies to 32
bit OS, 32 bit SQL, and > 2GB of memory allocated to SQL.