February 28, 2011 at 2:10 pm
Alright, first, the error:
Error: 0xC0047062 at Insert record as error, Get Package Variables into Datastream [31]: System.Runtime.InteropServices.COMException (0xC001404D): Exception from HRESULT: 0xC001404D
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PreExecute()
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper90 wrapper)
Error: 0xC004701A at Insert record as error, DTS.Pipeline: component "Get Package Variables into Datastream" (31) failed the pre-execute phase and returned error code 0xC001404D.
My googlefu has failed, I found one post in this regard, and the person was able to solve it because there was 'more to the error'. Well, that's a dirty, dirty lie in my case. 😉
This error spawns from... wait for it... a Scripted Data Source object in a datapump in an Event Handler that's triggered by the script object in the main control flow triggering a DTS.TaskResult = DTS.Results.Failure.
Now, some lead in. The process here is to do row by row evaluation and modification of a resultset fed into an ADO Recordset by another process that's completed and working as planned. One of the primary reasons for the row by row is I'm dealing with the Windows File System, and doing folder renames, so I'm not looking to work around that.
I've generated the ForEach loop on the ADO recordset and loaded all the values to package variables, we're good there.
Inside the loop, I have two progressing steps, first is an EXECUTE SQL statement that returns an OUTPUT parameter to a variable, the second is a VBScript that evaluates the result of the output parameter and decides if it should fail itself (thus going off to the event handler and restarting the loop which has a max error count of 0 (infinite)), or succeed and keep on going.
Originally this event handler merely had a dataflow object in it, with my scripted datasource. I have two of 19 records that are specifically built to fail at this point in the process, and I wasn't seeing the resultant records in the output file, but also wasn't receiving any errors. I included a script object in the same event handler to cough up a messagebox at me (just a simple MSGBOX("Triggered Event Handler blah blah")), and that's triggering just fine, but now the dataflow is coughing up the error above.
Anyone have any ideas?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 28, 2011 at 3:02 pm
What happens if, instead of a message box, you log the message to a file or table?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 28, 2011 at 3:27 pm
GSquared (2/28/2011)
What happens if, instead of a message box, you log the message to a file or table?
I currently have logging in the SSIS package shipped to a table, it gives me the exact same error message there that it does in the message box that pops up when the component in the event handler fails.
If you're talking about the other messagebox, that's just there to let me know we got into the component.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 28, 2011 at 4:54 pm
Okay, I found the problem... the error message just was never actually showing up in my output for some reason until I created a much more simplified version of the process and began to go through adding in changes inch by inch until I found the cause.
The real error it should have coughed up was something like this:
Error: 0xC001405B at Script Task: A deadlock was detected while trying to lock variable "User::Col0, User::Col1, User::Col2, User::Col3" for read access. A lock could not be acquired after 16 attempts and timed out.
Error: 0x5 at Script Task: The script files failed to load.
Task failed: Script Task
Warning: 0x80019002 at OnError: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Error: 0x4 at Script Task: The Script returned a failure result.
Task failed: Script Task
Warning: 0x80019002 at OnError: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Basically, I'm using a variable in my success/fail object. This proceeds to then call the event handler, specifically OnError. Guess what... the OnError object is not part of the scope of the calling object, and they compete for the same variable lock on the package... causing failure. D'oh!
I know there's a way to lock a variable for a single pass, I'll have to get my hands on that code and do that so my Error Handler doesn't self destruct because the failing object still hangs onto the variable. I can post the error example if anyone's interested.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply