SQL Server Blogs

Is there any documentation for this compenent?

Can you include a simple example on this page. i can't get it to work.

I think this component is a great idea. Is there any documentation or examples on it. I am having trouble getting it to work.

Did anyone ever get this to work?

Has anyone got anywhere with this? it seems to through an exception when I run it in my package.

Hi Simon, I can not get this component to work. I insert into table, redirect error columns to my error table and your component is in between. But it's failing. Please help

Hi Simon, I'm trying to implement your component in between the Destination and Error destination with redirecting the error flow , but your component is failing. As i'm new to SSIS , can you please help me to get your component work in my Package.

Hi Simon, Can you provide any help document about this component as I am unable to implement this component. In addition to a column for the error description occured in to the data flow does this component provide me with the exact coluimn name where the error occured. Please provide the needful guidance and oblige. Thanks and Regards.

Want to access the site

As posted here: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1427902&SiteID=17 I am getting the same error as last poster. I am connecting (On the entry end) Flat file Source Error Output (Error Output) to (ErrorInput) <<<< I believe this is mandatory otherwise the Enhanced Error Component doesn't pass validation. In "Configure Error Output" I select all columns and choose to Redirect row for both Error and Truncation errors. I am connecting (On the destination end) EnhancedErrorOutput to DataReader Destination Input (I tried with OLEDB destination too). Everything seems to start ok but fails with bellow described error. Can you please clarify how to use this component. My understanding is that it must have ErrorInput on entry side and it only appends one extra field ErrorColumnName. I created equvalent field name in my destination. Can you please explain what might be wrong Thanks [Enhanced Error Flow [1743]] Error: System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary. at System.ThrowHelper.ThrowKeyNotFoundException() at System.Collections.Generic.SortedList`2.get_Item(TKey key) at Onarc.SQLServer.SSIS.Components.ErrorFlowComponent.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket)

Hello There, Even i am getting the same error:- "[Enhanced Error Flow [6674]] Error: System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary. at System.ThrowHelper.ThrowKeyNotFoundException() at System.Collections.Generic.SortedList`2.get_Item(TKey key) at Onarc.SQLServer.SSIS.Components.ErrorFlowComponent.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket)".I am using datareader source and oledb as a destination component.I am redirecting the error rows to this "Enhanced Error Component" and then using the Error Column name in the script component to display the column name in error.But exception occurs at the "Enhanced Error Component" itself. Can you please guide me how to proceed further? Thanks in advance. Ruby

Can you please provide the source code for this component. Thanks in advance

the same problema ... help simon!!

Works fine for me :o) You have to plug the standard output flow of the component generating the error to the PassThroughInput of the Enhanced Error component, plus the error flow to the ErrorInput.

Thanks a lot gcousin ..It's working gr8 work simon.right now i m testing it on diferent transformation.please update your findings.

This will not work with any destination adapter.We can not give ErrorInput and PassthroughInput from destination. I tried to fool component by passing same error output as both ErrorInput and PassthroughInput using multitask,No use. Simon can you please give some explanation on working of this component? If it requires data buffer to flow through component then it is not useful for destination adapter. We will have to do all datatype, NULL check before loading target. Anyways it is very useful component :).

Can anyone that has use this component PLEASE tell me how I can use it? I have downloaded the most recent update (6/27/07) - thinking that there would be some type of README file, but it is just a single DLL. Thanks in advance for ANY help you can give.

I face this problem when try to configure the component>>>>>>>>Could not load type 'Microsoft.SqlServer.Dts.Design.IDtsPipelineEnvironmentService' from assembly 'Microsoft.SqlServer.Dts.Design, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. (Microsoft Visual Studio) Anyone know this? thx

I've been working on a component to provide not only the description of the eroor but also the column and row that cause the error. Source code is available at: http://antoon.blog.com//SSIS+-+Error+logging/

Please, Simon, provide me with the source code. I really need it badly. My e-mail is : ishbasha@gmail.com THANKS MAN!

Will this work with Ole DB Destination Error output. When I did attached with Ole Db Destination component, it didnt gives Error column name and error Description

Wow this looks like the trick, however I've installed several times and it never shows up as an available add-in under Choose Items>SSIS DataFlow Items; we have a named instance of 2005 and a default instance of 2000 on this machine, could the DLL be going the wrong place? Thanks, Dave.

This worked great! And, right out of the box (so to speak). Simon, if you could provide the source, that would be fantastic! forty3 at google's mail.

I do respond to issues that are raised to me. If you let me know your problem I will try and help. As you can see many people have used the component successfully.

Simon, I connected my error output (from a lookup) to the EEC and already noticed the Error Description returning, so that works fine. But instead of the expected error descr. (row yielded no match) I now get "The given key was not present in the dictionary." with an empty columname. Weird thing is I'm still seeing errorcode -1071607778 which belongs to "row yielded no match". Now I've read above that I should connect a regular output as a throughput to the EEC ...? Or is this outdated? Because it complains there is no (second) available input. Anyway, could you shed a light? Provide a simple working example? doesn't have to be in 2007 :) Thanks a bunch; you the Man

looks like I more or less answered my own question; the component works. I made a simple data flow: [flat file src] --errorflow--> [EEC] --normalflow--> [flat file dest.] ...and made sure a truncation error would occur at the source. A dataviewer (and the outputfile) shows the correct columnname. That only leaves me with the question when using lookup error handling; why doesn't a lookup error provide the EEC with a valid LineageID? It gives 0 resulting in a "The given key was not present in the dictionary."... does anyone have any thoughts/solutions to this flaw? Can this LineageID be provided using some 'throughput'? I'm off to do more research.

Hi Simon, I have registered so I can DL this component, I still havent recieved my password 3 hours after registering. Cheers Az

Tried to use this custom component. Not massively experienced SSIS but learning more each day. I have SP2, installed but not SP1, which assuming that MS holds constant, Should cover the SP1 requirement. ;) I get the following error in the debug output window when trying to run this package, with your Enhanced Error Flow component hooked to the error output of a data conversion flow, and input to a derived column flow. Information: 0x0 at ImportD61Grades Data Flow Task, Enhanced Error Flow: Looking at output EnhancedErrorOutput Information: 0x0 at ImportD61Grades Data Flow Task, Enhanced Error Flow: Found Error Output : Column Count=1 Information: 0x0 at ImportD61Grades Data Flow Task, Enhanced Error Flow: Column0 =ErrorDescription Information: 0x0 at ImportD61Grades Data Flow Task, Enhanced Error Flow: Found Error Description Output Column Information: 0x0 at ImportD61Grades Data Flow Task, Enhanced Error Flow: Out Col LineagesId 0 Information: 0x0 at ImportD61Grades Data Flow Task, Enhanced Error Flow: Input name ErrorInput Information: 0x0 at ImportD61Grades Data Flow Task, Enhanced Error Flow: Input name ErrorInput 2 ErrorCode 80 Information: 0x0 at ImportD61Grades Data Flow Task, Enhanced Error Flow: Input name ErrorInput 1 ErrorColumn 81 Error: 0xC0047062 at ImportD61Grades Data Flow Task, Enhanced Error Flow [2690]: System.Runtime.InteropServices.COMException (0x80004005): Error HRESULT E_FAIL has been returned from a call to a COM component. at Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSBufferManager90.FindColumnByLineageID(Int32 hBufferType, Int32 nLineageID) at Onarc.SQLServer.SSIS.Components.ErrorFlowComponent.PreExecute() at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper90 wrapper) Error: 0xC004701A at ImportD61Grades Data Flow Task, DTS.Pipeline: component "Enhanced Error Flow" (2690) failed the pre-execute phase and returned error code 0x80004005. Information: 0x40043008 at ImportD61Grades Data Flow Task, DTS.Pipeline: Post Execute phase is beginning. I rebooted after the install just in case something didn't register correctly, but Com processing in Win32 is not my current forte... Any sugestions would be welcome, I'll be pulling the module out of my package for now, as I must get it going. Thanks in advance, PW

Akk, the format had blanks in the edit window... Sorry..

please give us the source man!!!!

Thanks for a solution Microsoft opviously left out! You component works great. I had it working in 5 minutes. Thanks again!

Thanks for the component. I was able to use the component in 2005 but with 2008 it doesn't work.So can you provide the updated dll or the source code so that i can make the necessary changes and use the component.

Simon, thank you very much! So easy! Small note: If unique constraint errors take place, SSIS may return 0 for ErrorColumn and ErrorDescription will be empty. For this case i add string "0;PK column;" to component property "Columns". Thank!

A new 2008 version is now available here http://sqlblogcasts.com/files/folders/transforms/entry11865.aspx Please feel free to make donations through paypal. https://www.paypal.com/cgi-bin/webscr?cmd=_s-xclick&hosted_button_id=6191748

Got error message on attempting to add item, with SP3 on SQL 2005 developer :-( Guess better deinstall

This is working great for me, thanks so much.

Getting error trying to add the component to the data flow (not even connecting to to anything). Any idea's? I have SP1 TITLE: Microsoft Visual Studio ------------------------------ The component could not be added to the Data Flow task. Please verify that this component is properly installed. ------------------------------ ADDITIONAL INFORMATION: The data flow object "Onarc.SQLServer.SSIS.Components.ErrorFlowComponent, Onarc.SQLServer.SSIS.Components.ErrorFlowComponent, Version=1.0.0.0, Culture=neutral, PublicKeyToken=314cbe55a41d0c70" is not installed correctly on this computer. (Microsoft.DataTransformationServices.Design) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.762&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=PipelineObjectNotInstalled&LinkId=20476 ------------------------------ BUTTONS: OK ------------------------------