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 Thursday, June 03, 2010 1:27 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 5:50 AM
Points: 550, Visits: 1,040
Good day,
I'm having a problem with a ssis package, which fails 99% of the time.
The error message returned:

Message
Executed as user: DomainName\AdminUser. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 08:55:23 AM. The return value was unknown. The process exit code was -2146233082. The step failed.

It's running on a SQL 2008 R2 platform.

I have a main package, which calls about 30 other ssis packages.
It's running on a 64 bit windows 2008 server. But I have to run the package in 32 bit mode, as I'm connecting to progress with 32 bit drivers.


I've enabled logging. I don't get any errors in the log file.
All that I get is a Package start - and NO package end - and NO warnings....

I've searched for this exit code, but can't find any information about it.

Thanks
Post #931877
Posted Friday, June 04, 2010 1:58 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, January 03, 2014 3:52 PM
Points: 6,066, Visits: 5,277
You might look at: http://social.msdn.microsoft.com/Forums/en/vswpfdesigner/thread/378eae0a-ca38-48a8-875b-917b73ef6660

CEWII
Post #933048
Posted Friday, June 04, 2010 3:52 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:28 PM
Points: 1,794, Visits: 652
Wandrag (6/3/2010)
Good day,
I'm having a problem with a ssis package, which fails 99% of the time.

It's running on a SQL 2008 R2 platform.

I have a main package, which calls about 30 other ssis packages.
It's running on a 64 bit windows 2008 server. But I have to run the package in 32 bit mode, as I'm connecting to progress with 32 bit drivers.


If the link previously posted is not helping, I have some questions....

The 30 called ssis packages....Do they ALL have to be run under 32-bit mode? Is it possible to have TWO packages; one for 64 bit and another for 32 bit packages? Or perhaps you might want to consider starting over with NOTHING in your controlling package, then add one or two of the 30 called packages and get the process to run those...then add two or three more of the 30 and retest the whole package....then add two or three more and repeat. It may be that only one of the 30 is causing your problems....or it may be that one of your many connection strings is invalid and you missed it. Sure seems that the Execution tab should be showing you SOMETHING when the package is processed.

Something is obviously bad in the current ssis package. Starting over may be the solution.
Post #933083
Posted Saturday, June 05, 2010 1:37 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 5:50 AM
Points: 550, Visits: 1,040
nelsonj-902869 (6/4/2010)
Wandrag (6/3/2010)
Good day,
I'm having a problem with a ssis package, which fails 99% of the time.

It's running on a SQL 2008 R2 platform.

I have a main package, which calls about 30 other ssis packages.
It's running on a 64 bit windows 2008 server. But I have to run the package in 32 bit mode, as I'm connecting to progress with 32 bit drivers.


If the link previously posted is not helping, I have some questions....

The 30 called ssis packages....Do they ALL have to be run under 32-bit mode? Is it possible to have TWO packages; one for 64 bit and another for 32 bit packages? Or perhaps you might want to consider starting over with NOTHING in your controlling package, then add one or two of the 30 called packages and get the process to run those...then add two or three more of the 30 and retest the whole package....then add two or three more and repeat. It may be that only one of the 30 is causing your problems....or it may be that one of your many connection strings is invalid and you missed it. Sure seems that the Execution tab should be showing you SOMETHING when the package is processed.

Something is obviously bad in the current ssis package. Starting over may be the solution.


Thanks.
To answer your questions:
1. Yes all packages must run in 32 bit (all is using the odbc driver to connect to progress)
2. You see - sometimes it works - so it will be very hard to eliminate the problem packages. And I can't reproduce the problem... :-( (not every time)
3. When I run the package out of the debugger - it works. It is just when it's scheduled. So I can't see what's going on.
As I've mentioned - the log files is not helping out either. The log files will just have a begin package - with no end package or package error.
(But I might be able to see which of the 30 package only have a start with no end. And this will be the package which is causing the error... But I still don't know how to fix that package!!!!)
Post #933132
Posted Monday, June 07, 2010 8:22 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:28 PM
Points: 1,794, Visits: 652
Hmmmm. The only other thing i can think of is..... Were all of the job steps created under the same logon, with the same permissions, same owner, etc.? Perhaps one of the steps was generated by a different userid, different owner...something like that? If the job scheduler (agent) is having problems, but everything runs under the debugger and in ssis, you must have a mismatch of rights somewhere.

I'd give some serious consideration to breaking this big job up into smaller groupings of steps to try to find the problem. To make it more dependable it may be worth the time.
Post #933537
Posted Monday, June 07, 2010 5:54 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 26, 2012 12:43 PM
Points: 1,330, Visits: 455
There are couple of ways to check this out:

1. Copy & Paste below command in .cmd file and execute the package through command line
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe" /FILE "PackagePath\PackageName.dtsx" /CONFIGFILE "ConfigFilePath\configFileName.dtsconfig"

Before executing the command, make sure the path of DtExec.exe is correct.

2. Double click on the Job in SQL Server Agent --> Select Steps --> Click on Edit --> Select Type as OperatingSystem (CmdExec) from drop down box. Save the changes and executing the job.


Cheers,
Hari
Tips & Tricks for SQL BI Developers
Post #933742
Posted Monday, June 07, 2010 11:05 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 5:50 AM
Points: 550, Visits: 1,040
Hari.Sharma (6/7/2010)
There are couple of ways to check this out:

1. Copy & Paste below command in .cmd file and execute the package through command line
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe" /FILE "PackagePath\PackageName.dtsx" /CONFIGFILE "ConfigFilePath\configFileName.dtsconfig"

Before executing the command, make sure the path of DtExec.exe is correct.

2. Double click on the Job in SQL Server Agent --> Select Steps --> Click on Edit --> Select Type as OperatingSystem (CmdExec) from drop down box. Save the changes and executing the job.


Thanks - I'll try this.
But - what will this return and how will this help me?
What must I look for?
Post #933793
Posted Thursday, August 12, 2010 10:52 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 5:50 AM
Points: 550, Visits: 1,040
Hi,
I'm still having issues on this!!! $%#%#$

I've tried the methods mentioned above, but still I can't get any use-able error messages.

I'm not sure if the following error is related to this error, but sometimes when I open the package in Visio studio on the server & run it it simply just crash!
With no error message - the app simply just close - no warning - no nothing!

In Event viewer I see the following error messages :
(First message)

Log Name: Application
Source: .NET Runtime
Date: 2010/08/13 06:34:08 AM
Event ID: 1023
Task Category: None
Level: Error
Keywords: Classic
User: N/A
Computer: Server.Domain.local
Description:
.NET Runtime version 2.0.50727.4200 - Fatal Execution Engine Error (72DA7B3E) (80131506)
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
<System>
<Provider Name=".NET Runtime" />
<EventID Qualifiers="0">1023</EventID>
<Level>2</Level>
<Task>0</Task>
<Keywords>0x80000000000000</Keywords>
<TimeCreated SystemTime="2010-08-13T04:34:08.000Z" />
<EventRecordID>1572693</EventRecordID>
<Channel>Application</Channel>
<Computer>Server.Domain.local</Computer>
<Security />
</System>
<EventData>
<Data>.NET Runtime version 2.0.50727.4200 - Fatal Execution Engine Error (72DA7B3E) (80131506)</Data>
</EventData>
</Event>


Second Message:


Log Name: Application
Source: Application Error
Date: 2010/08/13 06:34:08 AM
Event ID: 1000
Task Category: (100)
Level: Error
Keywords: Classic
User: N/A
Computer: Server.Domain.local
Description:
Faulting application dtexec.exe, version 2009.100.1600.1, time stamp 0x4bb679aa, faulting module mscorwks.dll, version 2.0.50727.4200, time stamp 0x4a9ee474, exception code 0xc0000005, fault offset 0x0000c242, process id 0x%9, application start time 0x%10.
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
<System>
<Provider Name="Application Error" />
<EventID Qualifiers="0">1000</EventID>
<Level>2</Level>
<Task>100</Task>
<Keywords>0x80000000000000</Keywords>
<TimeCreated SystemTime="2010-08-13T04:34:08.000Z" />
<EventRecordID>1572694</EventRecordID>
<Channel>Application</Channel>
<Computer>Server.Domain.local</Computer>
<Security />
</System>
<EventData>
<Data>dtexec.exe</Data>
<Data>2009.100.1600.1</Data>
<Data>4bb679aa</Data>
<Data>mscorwks.dll</Data>
<Data>2.0.50727.4200</Data>
<Data>4a9ee474</Data>
<Data>c0000005</Data>
<Data>0000c242</Data>
</EventData>
</Event>


Please - This getting a bit out of hand now!!!! I really need to get this issue solved! I might just land in the damn nut-house because of this!
Post #968651
Posted Tuesday, August 17, 2010 5:09 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 27, 2011 10:42 PM
Points: 309, Visits: 135

Hi,

If you say this problem occurs only when u schedule the package, Think the problem could be of executing the package in 64bit instead of 32bit. Did you set the property "Use 32 bit runtime" for your Job Step? You can find this under "Execute Options" of your Job Step, if it is unchecked, then Enable/Check this option and then run your package through the Job.

Hope it helps, if the problem is related to execution of Package in 64bit environment.

Regards,
Ashok S
Post #970254
Posted Tuesday, August 17, 2010 5:18 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 5:50 AM
Points: 550, Visits: 1,040
Ashok.S (8/17/2010)

Hi,

If you say this problem occurs only when u schedule the package, Think the problem could be of executing the package in 64bit instead of 32bit. Did you set the property "Use 32 bit runtime" for your Job Step? You can find this under "Execute Options" of your Job Step, if it is unchecked, then Enable/Check this option and then run your package through the Job.

Hope it helps, if the problem is related to execution of Package in 64bit environment.

Regards,
Ashok S


Nope - this is not it...

I've figured that it have something to do with the .net framework 3.5 SP1.
But for some reason the patch that MS recommend does not work for me.. :-(
Post #970261
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse