June 19, 2007 at 5:29 pm
I am using the DB2 provider with ssis and I have been getting strange results. The packages are schedule to run at 7:30 pm and on somedays the package runs great but on others days I get the following error.
Date 06/17/2007 07:30:00 PM
Log Job History (WB_OVERRIDE)
Step ID 1
Server ETLXXXX
Job Name WB_OVERRIDE
Step Name WB_OVERRIDE
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: JBH01\lanadmin. ...ion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 7:30:00 PM Error: 2007-06-17 19:30:00.86 Code: 0xC0014005
Source: Description: The connection type "OLEDB" specified for
connection manager "(local)" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name. End Error Error: 2007-06-17 19:30:00.86
Code: 0xC0010018 Source: Description: Error loading value
"<DTS:ConnectionManager
xmlns: DTS="www.microsoft.com/SqlServer/Dts"><DTS: Property
DTS:Name="DelayValidation">0</DTS: Property><DTS: Property
DTS:Name="ObjectName">(local)</DTS: Property><DTS: Property
DTS:Name="DTSID">{225b3fb3-6b76-463c-9c6b-4644d35460b9}</DTS" from node "DTS:ConnectionManager". End Error Could not load package "D:\SQL_D...
The package could not be lo... The step failed.
This package ran for 7 consecutive nights without a problem but then sunday it failed and then ran successfully again on Monday. There were 4 other packages that use the same connection string the they worked on sunday but failed on monday or worked on both sunday and monday but failed last Thrusday. What I dont understand is that the "local" connection manager that is displayed in this error message connects to the msdb database on the MSSQL server. Can anybody shed some light on this issue for me?
June 20, 2007 at 2:23 am
Very weird error.
Do you have all the patches or Sp2a installed?
Does it only happen when you are using Agent to run the package?
Do that other packages that fail connect to the DB2 source or not?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
June 20, 2007 at 6:21 am
Crispin,
I have SP2 installed but I have not applied the post SP2 patches. I read through the post sp2 patches but did not see anything that might fix this issue, although I might be wrong. I will take another look at it today.
Yes, this seems to only happen when the agent runs the package. The hard part is I can run the package 10 times with no problems but then the very next time it will fire the error. Then the next day or time it runs fine again. This is not happening on just one package either there are about 6 packages that are doing the same thing. All the agents are set to go off at 7:30pm is there anyway they could be stepping on each other?
Yes there are other packages that connect to DB2 without out any issues but there are about 6 packages that are exhibiting this behavior. I might be wrong but if the packages fail to use the "local" OLE DB to connect to the msdb then it can't get the connection string from the database, so its failing way before it even gets a chance to connect to DB2. But, why would it work one day but not the next. I will pull my hair out if this yet another bug. I am so ready to be stupid and find out I need to check a check box or something. Thanks for replying so quickly.
Kevin
June 20, 2007 at 6:29 am
I would say try stagger the schedules by 2 minutes or something like that. Might be a bug with all the packages kicking off at the same time.
I would assume it has something to do with that because when you run it, presumably not at 7:30, it is fine.
Are there not any clues in the event log?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
June 20, 2007 at 9:25 am
I changed the schedule to delay by 5 min for the packages we will see if that works. My theory is that it wont change anything, I say this because lastnights jobs all ran without issues all at the same time.
Confussed,
Kevin
June 20, 2007 at 10:01 am
let us know how things go...
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
June 27, 2007 at 8:10 am
Changing the run times of the jobs did not help at all. Anybody have any suggestions?
Thank you,
Kevin
June 28, 2007 at 2:13 am
Here's some info - Seems you not alone.
A number of different things tried. Some responses from MS as well.
Similar: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127090&wa=wsignin1.0
Long thread with some answers: http://forums.microsoft.com/MSDN/ShowPost.aspx?PageIndex=2&SiteID=1&PostID=88468
and another. http://forums.microsoft.com/MSDN/ShowPost.aspx?PageIndex=4&SiteID=1&PostID=363238&PageID=1
HTH
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply