Thursday, March 29, 2012

Error loading package

Hi there all...

We've just started using SSIS packages, and everything seems to be going ok.

I've recently set up a couple of jobs which just run an SSIS package every 5 minutes to collect data.
These run fine 90% of the time, but intermittantly produce an error.

When using a SSIS task in the job it simply reported
"Could not load Package."

I have changed these to use DTExec as I heard it gave more verbose execution results, and now I get an error like
"Executed as user: NZ\SQLAdmin. Microsoft (R) SQL Server Execute Package Utility Version 9.00.1399.06 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:25:00 a.m. Could not load package "\MSDB\RadiusBilling\Get_RADIUS_AuthLog" because of error 0x80040E37. Description: Table/view either does not exist or contains errors. Source: Microsoft SQL Native Client Started: 8:25:00 a.m. Finished: 8:25:00 a.m. Elapsed: 0.047 seconds. Process Exit Code 5. The step failed."

So far I have not been able to find anyone else with similar errors.
Whenever the package executes it seems to run fine, so this seems to be an error just getting the package to execute in the first place, and as I said it only happens internittantly.

The packages talk to a SQL2005 server using windows authentication (which I assume is inherited from the SQLAgent service user), as well as a MySQL server through an ODBC connection.
The package is saved in the MSDB SSIS Package Store and uses the "Rely on Server for Security" option

Does anyone know why this is happening and how I can fix it?

disenfranchised wrote:

Hi there all...

We've just started using SSIS packages, and everything seems to be going ok.

I've recently set up a couple of jobs which just run an SSIS package every 5 minutes to collect data.
These run fine 90% of the time, but intermittantly produce an error.

When using a SSIS task in the job it simply reported
"Could not load Package."

I have changed these to use DTExec as I heard it gave more verbose execution results, and now I get an error like
"Executed as user: NZ\SQLAdmin. Microsoft (R) SQL Server Execute Package Utility Version 9.00.1399.06 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:25:00 a.m. Could not load package "\MSDB\RadiusBilling\Get_RADIUS_AuthLog" because of error 0x80040E37. Description: Table/view either does not exist or contains errors. Source: Microsoft SQL Native Client Started: 8:25:00 a.m. Finished: 8:25:00 a.m. Elapsed: 0.047 seconds. Process Exit Code 5. The step failed."

So far I have not been able to find anyone else with similar errors.
Whenever the package executes it seems to run fine, so this seems to be an error just getting the package to execute in the first place, and as I said it only happens internittantly.

The packages talk to a SQL2005 server using windows authentication (which I assume is inherited from the SQLAgent service user), as well as a MySQL server through an ODBC connection.
The package is saved in the MSDB SSIS Package Store and uses the "Rely on Server for Security" option

Does anyone know why this is happening and how I can fix it?

So you say it fails intermittently? Well that's weird.

Its failing because it cannot validate one of its external data sources. The fact that its intermittent leads me to suggest that there is some outside influence that is causing this. network connectivity perhaps? I'm just speculating here.

-Jamie

|||

I have suspected that it might be something to do with the permissions on the domain account that the SQLAgent runs as.

Something like the account was getting locked perhaps.

However if that was the case I'd expect to see 5-6 failures as the job runs every 5 minutes, and the domain keeps accounts locked for 30 minutes. We only ever seem to get isolated failures, but about 6 times per day (which out of 288 executions isn't to bad)

|||

Hi

I have the same problem with my SSIS packages.

I'm still developing the package so it isn't finished yet. But on each step I put it into production to see, how it behaves.

In adtition to you, I noticed, that this error occurs more often, when the package is stored in MSDB store. Previously I have had packages stored in SQL's FileSystem store and error occurs maybe twice or three times per week. Now it occurs several times per day.

I use the domain user and id in Administators group on the server.

I execute the package on the server where the inserting database is.

I execute the package every minute.

BR

ZigaP

|||Again, it sounds like an intermittent connectivity issue.|||

I think the network connectivity is not an issue.

It cannot be, because I run packages on the server where the database is.

what about this: if I replace the name of the server with "(local)"... can this solve the problem?

|||My company develops SSIS components.
No matter what you think or what you are told, I can tell you that most parts of SSIS only run about 90% of the time when you execute packages from the 'Business Intelligence Development Studio' (VS2005 really)
We actually have a problem right now because we do not have any test cases which will run 100% of the time if you let them run for a day.
Most things seem to work if you keep testing to less than an hour, but anymore and you will eventually hit a failure.

Oddly, the problem only occurs at the package level. We have not seen a package or flow that dies in the middle, or in different places, or anything variable.
It either runs to success, dies upon startup (by any means, scheduled, manual, debug), or just plain does the wrong thing. Once a given package shows a particular problem, the problem is the same every time that package fails, and there is usually only one problem.

It is known to us and some big customers that you need to shut down dev studio and reload your project to get things to work again.

The size and/or complexity of the work does not seem to matter either.
We have simple flows with one src and one dest that moves 10000 rows, all the way to massive, multi-pronged flows with multiple sources, lookups, and millions of records.
They all eventually fail. Once you get the first failure, all others on that package will be of the same nature.

Its odd.
|||

FarmerChet wrote:

My company develops SSIS components.
No matter what you think or what you are told, I can tell you that most parts of SSIS only run about 90% of the time when you execute packages from the 'Business Intelligence Development Studio' (VS2005 really)
We actually have a problem right now because we do not have any test cases which will run 100% of the time if you let them run for a day.
Most things seem to work if you keep testing to less than an hour, but anymore and you will eventually hit a failure.

Oddly, the problem only occurs at the package level. We have not seen a package or flow that dies in the middle, or in different places, or anything variable.
It either runs to success, dies upon startup (by any means, scheduled, manual, debug), or just plain does the wrong thing. Once a given package shows a particular problem, the problem is the same every time that package fails, and there is usually only one problem.

It is known to us and some big customers that you need to shut down dev studio and reload your project to get things to work again.

The size and/or complexity of the work does not seem to matter either.
We have simple flows with one src and one dest that moves 10000 rows, all the way to massive, multi-pronged flows with multiple sources, lookups, and millions of records.
They all eventually fail. Once you get the first failure, all others on that package will be of the same nature.

Its odd.

I've not observed this in my packages. So are you here with a question, or just posting your experience? There are so many things that influence package execution, and until you are sure you have a perfect environment, I wouldn't take your post with too much credit.|||

Hm... After my last post error occured only once.

I didn't do anything from my last post. Also admins on server didn't do anything...

Is server has to get used to these packages to run well?

BR
ZigaP

sql

No comments:

Post a Comment