Hello All, We wanted to extend the existing OLEDB Destination component. We want to create a User control with this component in it so that we can also add some scripting to validate the data before sending it to destination. Is this possible? Please let us know. thanks

While in theory it is possible to use the Component and create you custom Component around it I would not suggest this . If I understand what you want to do is to do some data cleansing , validation etc etc . You should be able to do all this using the standard SSIS components. You can use the derived column task to do most cleansing, use data conversion task to do type checking , use script task to do other stuff if you want a C# way and dont mind iterating over rows in the buffer on a row by row basis , use Data profiling and fuzzy grouping tasks for other more complex scenarios. I would also recommend that if you can all this validation using Execute SQL task or some other set based approach then it is better than row by row because performance can be hit big time for a row by row type transform. HTHAshwani Roy Blog Twitter Please click the Mark as Answer button if a post solves your problem!

Ashwani is correct - you can't "extend" existing components. They aren't overrideable classes. You'll have to code the full thing up by hand. What I would suggest as an alternative is as Ashwani says - use standard components. However, if you're still looking for a "drag and drop" type solution, I suggest you code up your "cleansing" logic only into a component. Only push "clean" items through to a standard Destination component. If you have specific things you want to do to "dirty" items - you can do them inside this component, or you can create different outputs on your component and redirect them as appropriate. Talk to me now on

Thanks Guys. Is it possible to create or write a new component which functions like the OLE DB Destination Component? We want to create our own OLEDB Destination Component but add our own rules to it. Please let me know. Thanks ravi

"We want to create our own OLEDB Destination Component but add our own rules to it." Are you sure. Either you should be able to get all you want done by using standard components or if you going VIA the C# custom SSIS component route why dont you write the whole thing in C# (maybe a console application ) and kick off this using execute process task of SSIS. You dont need to re invent a OLEDB destination because it is very complicated , might not work on different server hardware , unless you work with a company that sells custom components (eg data direct ) you dont have to do this. That will be a completely wrong design decision. Can it be done ? Offcourse . it is C# code in the end where you can creat a Dll and register in GAC and add it to SSIS . My final answer I dont think you would need it . Ashwani Roy Blog Twitter Please click the Mark as Answer button if a post solves your problem!

<!-- [if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:TrackMoves/> <w:TrackFormatting/> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:DoNotPromoteQF/> <w:LidThemeOther>EN-US</w:LidThemeOther> <w:LidThemeAsian>X-NONE</w:LidThemeAsian> <w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:SplitPgBreakAndParaMark/> <w:DontVertAlignCellWithSp/> <w:DontBreakConstrainedForcedTables/> <w:DontVertAlignInTxbx/> <w:Word11KerningPairs/> <w:CachedColBalance/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> <m:mathPr> <m:mathFont m:val="Cambria Math"/> <m:brkBin m:val="before"/> <m:brkBinSub m:val="--"/> <m:smallFrac m:val="off"/> <m:dispDef/> <m:lMargin m:val="0"/> <m:rMargin m:val="0"/> <m:defJc m:val="centerGroup"/> <m:wrapIndent m:val="1440"/> <m:intLim m:val="subSup"/> <m:naryLim m:val="undOvr"/> </m:mathPr></w:WordDocument> </xml><![endif]--> We need to create a component ( mostly like a custom pipeline component) which will sit just above the destination component which does the validation for the input rows. We need to apply the rules to the incoming rows and puts it into the separate output buffer collection based on good and bad data. The issue we are having is to create a separate output buffer for good data and separate output buffer for bad data. If you have any hands on lab for creating such kind of component, please let me know. Also, i see that the ADO.net Destination component seems extensible. Can we add rules to this component before sending data to the destination? Thanks

We need to apply the rules to the incoming rows and puts it into the separate output buffer collection based on good and bad data. The issue we are having is to create a separate output buffer for good data and separate output buffer for bad data. This is exactly what the data flow in SSIS is for. If you have "good data" and "bad data" determine what the rules are for this and use a conditional split transofmration ( http://msdn.microsoft.com/en-us/library/ms137886.aspx) to create separate destinations. If you're rules are too complex for the conditional split expressions you can use a script component to read the rows and write some VB/C# to determine the output. Unless you have some extremely complex and obscure logic I really dont think you need a custom component for this task. I've seen this "I need a custom component" approach before, it resulted in an data flow task with 24 transformations several of which were custom. I rebuilt it with a proper design into 4 transformations and it performed 250% faster.My Blog "Karl Beran's BI Mumble"

No existing component is extensible. There are no hands-on labs. There are only docs and samples, and the occasional session at a conference to help with how to build custom components. It's not a particularly large area of interest for many, because it requires knowledge of .Net and SSIS, and some persistence in learning the object model of SSIS. If you really want this done, you're going to have to learn the object model by reading docs or sample code. Or you could pay a third party to develop it for you. Talk to me now on