in

SQL Server Blogs

Voices from the UK SQL Server Community

msi Enhanced Error Component

Downloads: 2,337 File Size: 1.3MB
Posted By: simonsabin Views: 27,044
Date Added: Wed, Feb 8 2006

This custom component is used to to add the column name an error occurred in to the data flow in Intergration Services.

It requires the use of SQL Server Service Pack 1. If you get the error "Could not load type 'Microsoft.SqlServer.Dts.Design.IDtsPipelineEnvironmentService' from assembly 'Microsoft.SqlServer.Dts.Design, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'" it is because you do not service pack 1 installed

Following a false start this component now uses the pipeline service to obtain the column details from the upstream component. This requires the user edits the component, which launches the user interface. In the UI you can select whether you want the column name and or the error description added to the flow

Installing the component,

  1. Un Install any previous versions
  2. Run the setup
  3. Open VS and load or create a SSIS package
  4. Open the toolbox, right click and select "Choose items..."
  5. Select the SSIS Data Flow components tab
  6. Check the check box next to the enhanced error component

This should now result in the Enhanced Error Component appearing in the toolbox.

To use the component it is simple,

  1. Open your SSIS package
  2. Drag the Enhanced error component onto the data flow.
  3. Attach an error output to it
  4. Double click on the component
  5. Select whether you want to have the error column and/or the error description added to the flow.
  6. Connect the output of the component to a destination or another component
  7. Run your package.

This has not had extensive testing with destination components. It is best for use when errors are raised against a single column.

I am currently trying to resolve what the error codes from the OLE DB Destination are.

Feel free to contact me using the links above witany feedback/bugs/suggestions.

I hope you find it usefull

Filed under: , , , ,

Comments

 

James Bennett said:

Is there any documentation for this compenent?
Thu, Sep 7 2006 2:26 PM
 

Marc vd Gulik said:

Can you include a simple example on this page. i can't get it to work.
Sat, Oct 21 2006 8:26 AM
 

K. Lyles said:

I think this component is a great idea. Is there any documentation or examples on it. I am having trouble getting it to work.
Fri, Oct 27 2006 8:23 PM
 

tommy1adams said:

Did anyone ever get this to work?
Tue, Nov 14 2006 6:36 PM
 

Russ Bentley said:

Has anyone got anywhere with this? it seems to through an exception when I run it in my package.
Wed, Nov 15 2006 3:40 PM
 

MP said:

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
Wed, Dec 20 2006 3:55 PM
 

wadhwanimanish said:

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.
Mon, Feb 19 2007 10:45 PM
 

Anonymous said:

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.
Mon, Mar 26 2007 8:23 AM
 

Vandana said:

Want to access the site
Wed, Apr 4 2007 7:05 AM
 

Rad said:

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)
Wed, Apr 4 2007 9:49 PM
 

ruby said:

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
Thu, Apr 5 2007 5:57 AM
 

ruby said:

Can you please provide the source code for this component. Thanks in advance
Thu, Apr 5 2007 12:38 PM
 

moderecall said:

the same problema ... help simon!!
Fri, May 11 2007 1:49 AM
 

swapnilvk said:

Thanks a lot gcousin ..It's working gr8 work simon.right now i m testing it on diferent transformation.please update your findings.
Mon, Jun 25 2007 10:24 AM
 

swapnilvk said:

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 :).
Mon, Jun 25 2007 12:47 PM
 

Ron McKay said:

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.
Thu, Jun 28 2007 6:16 PM
 

yang said:

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
Thu, Jul 26 2007 3:39 AM
 

Antoon said:

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/
Sun, Aug 5 2007 12:38 PM
 

Sherif.Magdi said:

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

Dilip Pungliya said:

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
Tue, Oct 2 2007 10:45 AM
 

DBDave said:

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.
Sat, Oct 13 2007 11:49 PM
 

forty3 said:

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.
Tue, Dec 4 2007 5:24 AM
 

simonsabin said:

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.
Mon, Dec 17 2007 12:40 PM
 

VincETL said:

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
Fri, Dec 28 2007 4:07 PM
 

VincETL said:

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.
Fri, Jan 11 2008 2:48 PM
 

AzDog said:

Hi Simon, I have registered so I can DL this component, I still havent recieved my password 3 hours after registering. Cheers Az
Thu, Mar 6 2008 4:37 AM
 

PwAusSQL said:

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
Tue, Jul 8 2008 12:05 AM
 

PwAusSQL said:

Akk, the format had blanks in the edit window... Sorry..
Tue, Jul 8 2008 12:10 AM
 

igi_f said:

please give us the source man!!!!
Thu, Jul 10 2008 5:29 PM
 

kellyg said:

Thanks for a solution Microsoft opviously left out! You component works great. I had it working in 5 minutes. Thanks again!
Tue, Nov 11 2008 9:49 PM
 

sv_srikanth said:

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.
Tue, Jan 27 2009 9:43 AM
 

Dima said:

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!
Fri, Feb 13 2009 4:35 PM
 

simonsabin said:

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
Wed, Jun 17 2009 2:27 AM
 

BobF said:

Got error message on attempting to add item, with SP3 on SQL 2005 developer :-( Guess better deinstall
Mon, Aug 2 2010 3:26 PM
 

tpeterson said:

This is working great for me, thanks so much.
Wed, Jan 19 2011 10:40 PM
Powered by Community Server (Commercial Edition), by Telligent Systems