August 13, 2010 at 11:03 am
Hi All,
I've deployed a package in SQL 2005. When i try to execute, it's getting following error without any subsequent processing.
ERROR - SSIS package <<<Package Name>>> failed ERRORLEVEL(-1073741819)
When i run the package in desing mode of BIDS, it runs without any error.
Plz can you help me in resolving the issue.
TIA
Cheers,
Suresh
Suresh
August 13, 2010 at 12:49 pm
Suresh.Utham (8/13/2010)
Hi All,I've deployed a package in SQL 2005. When i try to execute, it's getting following error without any subsequent processing.
ERROR - SSIS package <<<Package Name>>> failed ERRORLEVEL(-1073741819)
When i run the package in desing mode of BIDS, it runs without any error.
Plz can you help me in resolving the issue.
TIA
Cheers,
Suresh
Is it failing on any specific step? What the full error message captured in the log?
If you are scheduling this in SQL Agent, then you need to check whether the process has necessary access to read files if you are using them in the package. I hope this error is due to insufficient permission.
[font="Arial"]BASKAR BV
http://geekswithblogs.net/baskibv/Default.aspx
In life, as in football, you won’t go far unless you know where the goalposts are.[/font]
August 13, 2010 at 1:06 pm
Here's the complete error message for your reference...
*************** Thu 08/12/2010 22:32:44.55 - OK - SSIS package <<package name>> Started ***************
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.4035.00 for 64-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 10:32:44 PM
Info: 2010-08-12 22:32:46.23
Code: 0x40016041
Source: <<package name>>
Description: The package is attempting to configure from the XML file "<<Config file name>>".
End Info
Info: 2010-08-12 22:32:46.36
Code: 0x4004300A
Source: Load <<task name>> DTS.Pipeline
Description: Validation phase is beginning.
End Info
Progress: 2010-08-12 22:32:46.36
Source: Load <<task name>>
Validating: 0% complete
End Progress
*************** Thu 08/12/2010 22:32:49.66 - ERROR - SSIS package <<package name>> failed ERRORLEVEL(-1073741819) ***************
Further details:
1) We have deployed the package as File System.
2) It has lot of script & data flow tasks.
3) It stops execution during validation of 1st task itself. By checking the error, you can see that it stops within a sec...
Start Time: 2010-08-12 22:32:46.23
End Time : 2010-08-12 22:32:46.36
Additional details:
We have deployed around 10 packages in the same server (as file system) & everything is working fine except this.
Suresh
August 13, 2010 at 1:19 pm
Description: The package is attempting to configure from the XML file "<<Config file name>>".
Hope you would have done this.. if not, can you please check whether the xml file is mapped with the SSIS package in the SQL Agent under configurations tab and also validate all the configuration properties are set properly.?
[font="Arial"]BASKAR BV
http://geekswithblogs.net/baskibv/Default.aspx
In life, as in football, you won’t go far unless you know where the goalposts are.[/font]
August 13, 2010 at 1:26 pm
We are not using SQL Agent for scheduling the job... an UNIX scheduler is in place.
And, the config file settings are correct. The package works from BIDS - Design mode with reference to parameter values in the config file. So, it should not be a problem.
PS: The config file is placed in SAN drive. So, it's available for access within the domain.
Suresh
August 13, 2010 at 1:29 pm
Can you tell me your package protection level? You can try by setting it as "DontSaveSensitive".
[font="Arial"]BASKAR BV
http://geekswithblogs.net/baskibv/Default.aspx
In life, as in football, you won’t go far unless you know where the goalposts are.[/font]
August 13, 2010 at 1:33 pm
the protection level is same as mentioned in your post.
Suresh
August 19, 2010 at 9:45 am
Can someone provide any solution for this?
More info about the server:
~~~~~~~~~~~~~~~~~
Microsoft SQL Server 2005 - 9.00.4220.00 (X64)
Apr 2 2009 18:34:24
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
Suresh
August 24, 2010 at 12:49 pm
We have resolved the problem :-).
Solution:
We had an user variable to store the SELECT query & used as SQL Command in OLE DB Source.
When we added few more columns, it exceeded the string length (not sure whether max length is 3000 here also).
The worst part is, it didn't throw error while running in design mode. Only when it's deployed in Server, the error was popping up.
Thanks to all for your response / suggestions.
Suresh
August 24, 2010 at 1:00 pm
Unfortunately that is pretty typical since it is usually run in production as a different user than was used to develop it..
CEWII
August 24, 2010 at 1:16 pm
Elliott Whitlow (8/24/2010)
Unfortunately that is pretty typical since it is usually run in production as a different user than was used to develop it..CEWII
The query would be the same even it is getting executed by different user.. right?? How this would make an impact. Please clarify.
[font="Arial"]BASKAR BV
http://geekswithblogs.net/baskibv/Default.aspx
In life, as in football, you won’t go far unless you know where the goalposts are.[/font]
August 24, 2010 at 2:42 pm
Elliott,
Just to add on - i'm using a service account which is same across Dev, UAT & PROD. So, it should not matter.
Suresh
August 24, 2010 at 3:45 pm
Baskar:
It isn't the query that is the problem, but the connection information required to satisfy the query that gets encrypted. And can only be decrypted by the same user who created it.
Suresh:
I think the underlying point is being missed..
The user who created the package and saved it, HIS user information is used to encrpyt the "sensitive" information. And since the developer's account is RARELY the same user who runs it in other environments you have this happen. Unless you use the service account DURING development this WILL happen with "sensitive" information.
Is this clearer now?
CEWII
August 24, 2010 at 4:05 pm
Here's some more info:
Package creation ID : My User Id
OLE DB Source (DB2) : Service Account
OLE DB Dest (SQL) : Win Authentication
Package prot level : Rely on server for access & role info (changed this while deploying the package).
When i execute the package in server, we should not have any issue in this case related to decrypting connection info.
Plz can you clarify me for better understanding.
TIA.
Suresh
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply