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 packages\jobs connecting to Linux DB2 fail randomly with message:SQLAgent job failed. process exit code was -10737418 Expand / Collapse
Author
Message
Posted Wednesday, January 9, 2008 10:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 9:33 AM
Points: 14, Visits: 68
Hello,
I can not get thru this issue and I found other people complain about it, but there is no solution.
Please help!

Problem Description:

We have many DTS packages on SQL 2000 server that access VSE DB2 on Mainframe using DB2 Connect. They run fine.

We have equivalent SSIS packages on new SQL 2005 server that run fine in parallel. We are getting ready to discard old 2000 server and use new 2005 one.

Mainframe switched from VSE DB2 to Linux DB2 (UDB). The only thing I had to do on servers is to point PRODDB2 alias from VSE database to Linux database.

On 2000 server jobs still run fine.

On 2005 server jobs fail randomly. The same job runs OK a minute after it fails. I do not see any pattern.

There is no clear error message:

Executed as user: xxxService. 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: 11:03:00 AM. The return value was unknown. The process exit code was -1073741819. The step failed.

I tried to switch to “dtexec.exe” as was advised on Mictrosoft forum

dtexec.exe /SQL "\MasterTables\MasterTables - Load CompanyMasterDB2 All Rows" /SERVER SQLCLV1 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF

/REPORTING V /CONSOLELOG NCOSGXMT

It only added warnings:

Message

Executed as user: xxxService. ....3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 1:03:01 PM Progress: 2007-10-03 13:03:02.21 Source: Create Night Table Executing query "if exists (select * from sysobjects where id = obj".: 33% complete End Progress Progress: 2007-10-03 13:03:02.22 Source: Create Night Table Executing query " CREATE TABLE [dbo].[CompanyMasterDB2Night] ( CP1".: 66% complete End Progress Progress: 2007-10-03 13:03:02.24 Source: Create Night Table Executing query " ".: 100% complete End Progress Progress: 2007-10-03 13:03:02.25 Source: Data Flow Task Validating: 0% complete End Progress Progress: 2007-10-03 13:03:03.82 Source: Data Flow Task Validating: 25% complete End Progress Progress: 2007-10-03 13:03:03.82 Source: Data Flow Task Validating: 50% complete End Progress Progress: 2007-10-03 13:03:03.88 Sourc... Process Exit Code -1073741819. The step failed.

I tried to experiment adding job logs and running packages from BI and noticed that when it fails the package runs all steps up to Data Flow task that accesses DB2 and stops after Pre-Execute step

Progress: 2007-10-04 13:03:03.53

Source: Data Flow Task

Pre-Execute: 0% complete

End Progress


In “Show output from: Debug” window I see:

Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.

SSIS package "MasterTables - Load CompanyMasterDB2 All Rows.dtsx" finished: Canceled.



Then on next run it runs thru and in the same window it says:

SSIS package "MasterTables - Load CompanyMasterDB2 All Rows.dtsx" starting.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.

Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.

Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.

Information: 0x402090DF at Data Flow Task, Destination - CompanyMasterDB2Night [346]: The final commit for the data insertion has started.

Information: 0x402090E0 at Data Flow Task, Destination - CompanyMasterDB2Night [346]: The final commit for the data insertion has ended.

Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "Destination - CompanyMasterDB2Night" (346)" wrote 18420 rows.

SSIS package "MasterTables - Load CompanyMasterDB2 All Rows.dtsx" finished: Success.



I contacted IBM and provided them with trace info. According to them it does not contain any errors and looks like it never even entered DB2 because last command is from month ago.

What and why cancels the step? Can I get more info on error?

Please help!

BTW: The package creator ID is the same as the SQL Agent ID what is the SA account the SQL Server is running under.

Thanks.
Post #440793
Posted Thursday, January 10, 2008 10:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:19 PM
Points: 10,342, Visits: 13,347
How did you migrate the 2000 DTS packages to SSIS? Are you using the Execute DTS 2000 Pakcage Task or did you re-write the packages in SSIS?



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #441310
Posted Thursday, January 10, 2008 12:15 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 9:33 AM
Points: 14, Visits: 68
I had to completely rewrite them because most of the "stuff" was not supported by SSIS and they were working fine until DB2 side changed to run under Linux.
Post #441384
Posted Sunday, January 13, 2008 11:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 9:33 AM
Points: 14, Visits: 68
I found one pattern:
There are two ways to specify source (DB2)

Specify table or view and just select it from the list. In this case access mode is OpenRowSet
Specify QUERY and then enter SQL statement. In This case access mode is SQLCommand.


Case ‘a’ always works.

It is the case ‘b’ that produces the problem. It looks like at some point Pre-Execute step can not get some info (catalog or else?)

Post #442226
Posted Monday, January 21, 2008 9:58 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 7:42 AM
Points: 747, Visits: 472
I had the same issue with AS400 DB2 but it only affected 1 package of about 12 that read from the same system. The package was working fine until a added a script task to lookup the error description. No matter what I didn't the package would throw random error massages or work. I tried wrapping a try catch around the script command - No luck. The exact same script command works perfect in another package that is run in the same job just prior.

I finally gave up and just deleted the script command. I can lookup the errors myself if I really care but most of the error related to inserts/updates are pretty obvious: Missing data or key violations.

If anyone finds a solution i would love to know!

-Mike

Post #445554
Posted Thursday, January 31, 2008 4:36 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 5, 2014 7:04 PM
Points: 112, Visits: 193
Hi Mike,

If your SSIS package is running on x64 Windows 2003, please check the following link

http://support.microsoft.com/default.aspx?scid=kb;en-us;931846

I am having the same issue, unfortunately, none of the methods in the article applies to me. Hopefully, you will find one. I am thinking to open a ticket for "HotFix" IF they already have it.
Post #450238
Posted Thursday, January 31, 2008 6:07 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 7:42 AM
Points: 747, Visits: 472
Hey Harvey,

Thanks for the post but unfortunately I am not using a script task.

I did open a case with Microsoft and have since discovered it is an issue with the "sort" command and memory. MS has 1 other case that is similar to this and they have classified that case and this as a bug. Unfortunately, MS is focusing mainly on SQL 2008 and only issuing security fixes (for the most part) at this time. I was able to work around the issue by redesigning my package so I didn't push it with MS, either.

If I hear anything back I will post it here.

Best of luck!

-Mike
Post #450255
Posted Thursday, January 31, 2008 6:28 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 5, 2014 7:04 PM
Points: 112, Visits: 193
Hey Mike,

I am glad to hear you single-handed solving the issue because I am still fighting with it. My developer's SSIS package DOES have a lot of "Sort" transform. Would you mind sharing more details about your work-around design ?

Best regards,

Harvey

Post #450260
Posted Friday, February 1, 2008 7:14 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 7:42 AM
Points: 747, Visits: 472
Hey Harvey,

Well, I guess you could say I was lucky. I had designed the package and it was working without issue for about a month when I had to revisit the package since i forgot a condition on one of my select statements (good thing it was only in test at the time) and I decided to add an addition data reader, merge join and sort to drop a significant amount of data that I was carrying in memory that I didn't need. After I made those change sis when the package started to "blow up" when I ran it in SSIS but was fine in BIDS (it would even occasionally work in SSIS).

It was pretty easy for me to identify the change that was causing the issue so I just deleted them and left the extra data in the process since it would drop out latter on regardless.

The best suggestion I have for you is to do the following:
-Log into the SSIS Server via Remote Desktop or some other tool
-Open SSMS and find the job that you have for the SSIS package that is fail
-Open the step for the SSIS Package
-Go to the command line tab and copy the text out. It should begin like /DTS "\MSDB\FCPA\DSD Delivery Costs...
-Open a command line prompt
-Type DTEXEC and paste the command from above
-Run the command and it should then run your package
-Depending on several factors the package should eventual complete in error status (kind of ironic that we want it to fail)
-1 or 2 things should happen: 1) The package may just suddenly end with no info but you will find a mini dump in the SQL dump area or 2)If you scroll up in the command window you should come to the error step with some detailed info which might lead you to what tasks are causing the issue

My understanding is that this issue is very random and tough to pinpoint.

1 suggestion when it comes to sorts...if possible avoid them. They can take forever since they need to wait for everything proceeding it to complete first. The other suggest is to put the order by command in your select statements and then manually set the isSorted property and sort key properties for the pipeline. Then you can do you merge with out a sort command.

Hope this helps.

-Mike
Post #450456
Posted Friday, February 1, 2008 8:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 9:33 AM
Points: 14, Visits: 68
It looks like your issues\causes are different from mine.
I do not have any sorts\merges. It is a straight SELECT from DB2 table (only one of them) and load into SQL server table.
And using a query as a source causes the problem...
And I can not use a table or a view as source because they are just too big.
Post #450521
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse