January 9, 2008 at 10:18 am
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.
January 10, 2008 at 10:26 am
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
Consultant - Straight Path Solutions
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
January 10, 2008 at 12:15 pm
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.
January 13, 2008 at 11:02 am
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?)
January 21, 2008 at 9:58 am
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
January 31, 2008 at 4:36 pm
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.
January 31, 2008 at 6:07 pm
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
January 31, 2008 at 6:28 pm
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
February 1, 2008 at 7:14 am
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
February 1, 2008 at 8:14 am
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.
February 1, 2008 at 8:24 am
Are you using the IBM driver or MS driver for DB2? I know with the MS driver there is an option to tell it what type of DB2 system you are connecting to...i can't remember if there is a similar setting on the IBM. Have you tried the other? What you tried just doing a "select * from" to see if that works? Could something be locking the table and that is why it works sometimes and not others?
-Mike
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply