Package FAIL on 2k8 SQL Agent Run

Question

Package FAIL on 2k8 SQL Agent Run

irobertson

Hall of Fame

Points: 3695
More actions
December 4, 2009 at 9:53 am

#137469

Hi,
I'm trying to deploy an SSIS package to a client's server. The job imports various files to a SQL Server database, with a check to a location defined in the config file for the existence of each file to load. If the file doesn't exist its load task doesn't execute.
The client is running 64 bit servers with 64 bit SQL 2k8. I've developed on 32 bit servers running SQL 2k%.
I've followed the advice from MSDN on running 32 packages in a 64 bit environment from SQL Agent, essentially, this consists of an explicit call to the 32 bit version of DTExec via an Operating System (CmdExec) job step and a command line argument.
The job is running under the SA account. The Run64BitRuntime property for the package is set to false.
Package flow is:
Check package can be run
Execute sproc on SQL db to check for files
Load data from files, conditional on there being as file to load
Load files to SQL db, split errors to flat file
Execute validation sproc on SQL db
The job runs sporadically. Sometimes it runs just fine, others it errors with the following:
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 15:55:50
Error: 2009-12-04 15:56:00.31
Code: 0xC0047062
Source: Load new <SomeFileName> data Get Error Description [90]
Description: System.NullReferenceException: Object reference not set to an instance of an object.
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.CreateUserComponent()
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
(The file name varies, but it's always a 'Load new' step that fails)
Initially the package used Script Tasks to verify the existence of files before executing the load attempt. Having thought this was the cause of the problem, I changed this so that an SQL sproc populated a table with a sequence of bit values for each of the files to be loaded. These are then queried from the SSIS package via Execute SQL Tasks to determine whether to attempt to load a file. This hasn't solved the problem.
Files are correctly formatted, defined and mapped. Everything works just fine on my server. This leads me to believe it's either 64/32 bit issues or permissions. But the permissions angle would surely mean it wouldn't run at all?
Any suggestions for avenues for exploration would be gratefully apprecited.
Apologies for verbosity.
Regards, Iain
Edit: Package also fails on attempting to run from command line

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

What kind of task is the load new. I see the Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.CreateUserComponent and am wondering if you are using any custom SSIS tasks. Also, silly question, are we talking about SQL 2008 or 2005, because the DTExec is 2005 but you reference 2008 several times so it isn't clear..

CEWII

irobertson Hall of Fame Points: 3695 More actions · Answer 2

Hi Elliott,

Each task is a data flow task comprising of a flat file source, a data conversion, a dervived column calculation, then an insert into a SQL Server table. Failed rows are exported to a reject file with an error code and column.

The SQL Server is running version 2008 on a 64 bit platform.

The packages were developed on a 32 bit platform using VS 2k5 (version 9.00.4035.00.).

Thanks, Iain

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

irobertson (12/7/2009)
Hi Elliott,
Each task is a data flow task comprising of a flat file source, a data conversion, a dervived column calculation, then an insert into a SQL Server table. Failed rows are exported to a reject file with an error code and column.
The SQL Server is running version 2008 on a 64 bit platform.
The packages were developed on a 32 bit platform using VS 2k5 (version 9.00.4035.00.).

First I think you should upgrade the packages to SSIS 2008 before we try to diagnose any further. Also, based on the output you provided:

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

You are trying to execute it with the 2005/32-bit version of DTExec. For 2008 it should be like 10.00.1600 or higher.

Upgrade the packages first then we can try to figure out what the problem is..

CEWII

irobertson Hall of Fame Points: 3695 More actions · Answer 4

Hi Elliott,

Unfortunately I can't upgrade the packages. I don't have access to the server they're being deployed on and the client won't undertake development work on them.

I should be able to run these just fine using the 2005 DTExec call, as per Microsoft's advice here:

http://technet.microsoft.com/en-us/library/ms139805(SQL.90).aspx

I think the problem may be that the file loads have a 'hidden' script task component that I'd not considered - the error code retrieval for splitting load errors to a new file.

I've set these to PreCompile = true and it looks like this may have solved the problem. Although the errors are sporadic, so I'm having the client run through the packages a few times to check.

Thanks for posting, appreciate it. I'll let you know if this does truly solve the problem or if I'll need to start digging again.

Iain

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

I think you might have read too much into that link you sent. I don't see anything where is says you can run 2005 packages on 2008 with no problems. Legacy DTS yes.. If you are using the SSIS 2005 functionality you would have needed all the libraries.. The upgrade is usually incredibly easy, the only thing I can think of to check is the connection strings to SQL. But I understand we all have limits placed on us..

CEWII