Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

SSIS Failed with unknown value returned Expand / Collapse
Author
Message
Posted Wednesday, December 08, 2010 2:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, October 13, 2012 7:04 PM
Points: 2, Visits: 36
Did you get this issue resolved? If so, what accomplished it?
Post #1032180
Posted Saturday, March 19, 2011 11:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 03, 2013 7:26 PM
Points: 1, Visits: 55
Hi,

I have the same problem here.

I'm using Windows Server 2008, SQL Server 2008 R2 at 64 bit machine.

My job is running well at BIDS and below error message only appeared when I schedule the dtsx to run at SQL Server Agent.

"The step did not generate any output. The return value was unknown. The process exit code was -1073740940. The step failed."

I have tested with schedule 4 jobs for the same package and running in parallel, sometimes some package succeed and some failed, the failure is very intermittent.
Post #1080868
Posted Saturday, March 19, 2011 9:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, October 13, 2012 7:04 PM
Points: 2, Visits: 36
I still do not have it resolved. Almost have given up on this task.
Post #1080943
Posted Monday, March 21, 2011 9:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 10, 2013 9:06 AM
Points: 2, Visits: 124
Hi,

Facing similar problem.

Windows Server 2003 R2 SP2 x64; SQL Server 2005 SP2 (9.00.3355.00 (X64)).

SSIS package:
scheduled under Agent Job (domain account);
calls multiple sub-packages;
SSIS storage - file store; "Do not save sve sensitive" option;
Configutaion file passed;

Job runs daily. In 99,5% execution cases it runs succesfully. But sometimes returns error (returned by Agent job):
The return value was unknown. The process exit code was -1073741819. The step failed.


There in no information in other error logs, SSIS logging do not return any information. Dtexec process craches suddenly, SSIS event handlers not trigerring.

All this types of errors occurs either on process startup initialization or on source text files processing flows -> not able to define problem source and actual process step.

Error is't reproducible..

Post #1081347
Posted Friday, October 12, 2012 9:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 24, 2013 10:45 AM
Points: 11, Visits: 23
RESOLUTION: TURNS OUT THE ERROR IS LESS THAN HELPFUL. IT'S ACTUALLY A STACKOVERFLOW IN THE DTexec.exe AND LIMITED TO A 4GB STACK WHEN RUNNING ON 64-BIT MACHINES USING SQL SERVER 2005. THE PROBLEM IS FIXED IN 2008 AND NEWER SQL VERSIONS. HOPE THIS HELPS SOMEONE ELSE EXPERIENCING THIS PROBLEM AS I WASTED ABOUT A WEEK.

I will be going to the nut-house with you. I have a 2005, sp3 SSIS pkg that has run successfully for months. The pkgs connect to Oracle and mid-Sept worked one night failed the next with the same errors you are seeing. The pkgs that connect to SQL Server run just fine. Thought I would post the errors and the configuration in the event some one has a solution.

Cases opened with both Oracle and Microsoft.

Update: overnight a new server was built, same SQL version, level and edition. Oracle 11.2.0 64-bit client installed. One of the jobs was exported from old server to this new server and scheduled to run which failed with the same error messages as the old server.


SQL Agent History

Date 10/12/2012 7:39:00 AM
Log Job History (SSIS PS sync)

Step ID 1
Server NTMSQL
Job Name SSIS PS sync
Step Name SSIS PS sync
Duration 00:00:38
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: KCPL\domainaccount. The return value was unknown. The process exit code was 255. The step failed.

Windows Application Event Log

Date 10/12/2012 7:39:28 AM
Log Windows NT (Application)

Source Application Error
Category (100)
Event 1000
Computer NTMSQL.xxx.com

Message
The description for Event ID '1000' in Source 'Application Error' cannot be found. The local computer may not have the necessary registry information or message DLL files to display the message, or you may not have permission to access them. The following information is part of the event:'DTExec.exe', '2005.90.4035.0', '492b1b7b', 'oraldapclnt11.dll', '10.1.4.0', '4b9b5cf1', 'c00000fd', '000000000016ca67', 'e4c', '01cda8768df4582e', 'c:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe', 'C:\oracle\product\11.2.0\bin\oraldapclnt11.dll', 'dc48df72-1469-11e2-a417-005056b900f9'

Date 10/12/2012 7:39:38 AM
Log Windows NT (Application)

Source Windows Error Reporting
Category (0)
Event 1001
Computer NTMSQL.xxxx.com

Message
The description for Event ID '1001' in Source 'Windows Error Reporting' cannot be found. The local computer may not have the necessary registry information or message DLL files to display the message, or you may not have permission to access them. The following information is part of the event:'', '0', 'APPCRASH', 'Not available', '0', 'DTExec.exe', '2005.90.4035.0', '492b1b7b', 'oraldapclnt11.dll', '10.1.4.0', '4b9b5cf1', 'c00000fd', '000000000016ca67', '', '', '
C:\Users\sql\AppData\Local\Temp\WER10F3.tmp.appcompat.txt
C:\Users\sql\AppData\Local\Temp\WER1142.tmp.WERInternalMetadata.xml
C:\Users\sql\AppData\Local\Temp\WER1143.tmp.hdmp
C:\Users\sql\AppData\Local\Temp\WER33B0.tmp.mdmp', 'C:\ProgramData\Microsoft\Windows\WER\ReportQueue\AppCrash_DTExec.exe_3d4bb741298ade0fe424516323b698db1d9f9_cab_0f123478', '', '0', 'dc48df72-1469-11e2-a417-005056b900f9', '4'

Date 10/12/2012 7:39:38 AM
Log Windows NT (Application)

Source Windows Error Reporting
Category (0)
Event 1001
Computer NTMSQL.xxxx.com

Message
The description for Event ID '1001' in Source 'Windows Error Reporting' cannot be found. The local computer may not have the necessary registry information or message DLL files to display the message, or you may not have permission to access them. The following information is part of the event:'', '0', 'APPCRASH', 'Not available', '0', 'DTExec.exe', '2005.90.4035.0', '492b1b7b', 'oraldapclnt11.dll', '10.1.4.0', '4b9b5cf1', 'c00000fd', '000000000016ca67', '', '', '
C:\Users\sql\AppData\Local\Temp\WER10F3.tmp.appcompat.txt
C:\Users\sql\AppData\Local\Temp\WER1142.tmp.WERInternalMetadata.xml
C:\Users\sql\AppData\Local\Temp\WER1143.tmp.hdmp
C:\Users\sql\AppData\Local\Temp\WER33B0.tmp.mdmp', 'C:\ProgramData\Microsoft\Windows\WER\ReportQueue\AppCrash_DTExec.exe_3d4bb741298ade0fe424516323b698db1d9f9_cab_0f123478', '', '0', 'dc48df72-1469-11e2-a417-005056b900f9', '0'


Additional information about the Oracle client running on Microsoft Windows 2008R2, 64-bit:

- Oracle client and OLEDB provider is 11.2.0.1. Base 11.2.0.1, 64bit.
- Oracle client software is installed on Windows 2008 R2 64bit.
- Oracle database version software where connections is being made are b oth 11.2.0.2 and 10.2.0.4.
- Operating System for the Oracle databases is HP UNIX.
- A successful connection to Oracle can be made from the Windows client via SQLPLUS.
Post #1372211
Posted Tuesday, September 03, 2013 9:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 03, 2013 10:02 AM
Points: 1, Visits: 1
OK. I got the same error, what is the resolution?

Post #1491010
Posted Tuesday, September 03, 2013 2:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 10, 2013 9:06 AM
Points: 2, Visits: 124
Hi,
Please, check: do you have more than 1 Lookup component that might run in parallel in your ssis package and these lookups have the same query in setup? If it is so: change lookups to have different queries.
If 2 lookups run in parallel with the same query => than they are stealing memory from each other. And than there is a memory stack overflow.
I had this on mssql 2005. Starting from mssql 2008 this problem is resolved by Microsoft.
Post #1491085
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse