SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS packages\jobs connecting to Linux DB2 fail randomly with message:SQLAgent job failed. process...


SSIS packages\jobs connecting to Linux DB2 fail randomly with message:SQLAgent job failed. process exit code was -10737418

Author
Message
Andrei-176105
Andrei-176105
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 70
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.
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44835 Visits: 14925
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
Andrei-176105
Andrei-176105
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 70
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.
Andrei-176105
Andrei-176105
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 70
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?)
Mike Nowill
Mike Nowill
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1508 Visits: 623
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
PandaDBA
PandaDBA
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 206
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.
Mike Nowill
Mike Nowill
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1508 Visits: 623
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
PandaDBA
PandaDBA
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 206
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
Mike Nowill
Mike Nowill
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1508 Visits: 623
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
Andrei-176105
Andrei-176105
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 70
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search