I am very new to SSIS, so finding difficulty in achieving this goal. My scenario is as below. I am having a table where the data is present – connected to a OLEDB Source. ID Premium Share Status 1 10000 3435252 Submission 2 5000 5454454 BOUND 3 3000000 231455 BOUND 4 3000 76887 BOUND In the ETL package, I need to create a custom component which does the following 1. Process the input ( row by row) 2. Identify Premium is greater than or equal to 10000, if yes then create a buffer and add these rows and puts it into the destination. Source-->My Custom Component--> Destination From the above example, The rows which are coming to the custom component will be 4 rows, but the rows which are going to the final destination component will be 2 ( since there are only two rows which satisfies the validation). How can I achieve this. Please help (I am not interested in conditional splitter, since I have a wcf service also to be called from my component). Thanks in advance -Sajin

Please use a SQL Task with SQL like select ID Premium Share Status from MyTable where Premium > 10000 and have it outputted to the "buffer" which probably could be a flat file destination. No need for a custom component IMHO. Then another SQL Task with SQL which is < 10000 outputting to another destination. Help on the above: http://decipherinfosys.wordpress.com/2008/07/23/ssis-exporting-data-to-a-text-file-using-a-package/ Arthur My Blog

What does you "Custom Component" do? Could it be a Script component set as a Transform,a nd in there, use the appropriate fields for input, and do a row-by-row check of the Premium column? I con't quite understand why a Conditional Split would not work. If you need to take action (WCF) on all the rows, do it BEFORE the split.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Hi All, Why I am creating the custom component. The purpose is explained below. My component will be inserted in the existing ETL packages just before the destination where the component calls a rule engine ( some wcf service) and filter the rows. I want to club this two functionality together in one component. So I would like to have within the custom component (Making the component as generic) Hope you got the scenario! -Sajin

I've been doing SSIS ever since 2005 hit the streets, 5 years ago this month, and have yet to need a custom component. A custom componenet is good if you need to be able to re-use it across a myriad of packages. Designing a custom component for use in one SINGLE package, when the same functionality could be gained from two easily configured stock componenets is a waste of time (IMHO). Is this custom componenet of yours going to be used in other places as well?Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

I agree with Todd, and i like to add 1- what is this custome component? 2- can it be used or called from a Stored Procedure (SP)? or some other way outside the SSIS package? 3- maybe you can change the flow and objects of the SSIS and implement this component somewhere elase and use it, 4- I dont see why they have to be processed one by one , Todds suggestion about the Conditional Split makes more sence good luck Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Hi, This component is used in multiple packages. The purpose of this component is to do a data validation on input rows. We will be using some proprietary logic to filter the rows based on some rules. Why we are not using the existing components is because we don't want to change/set the properties of the existing component in all the ETL packages. We need to have the logic and rule engine validation in one place. so that even if any changes come we will be able to do it in one place. Thanks -Sajin

This component is used in multiple packages. The purpose of this component is to do a data validation on input rows. Fair enough. Here's my thoughts on a Custom componenet: You could design complex 'static' logic into it, then deploy it to multiple packages. Now what happens when you need to change that logic? All referencing packages need to be edited and re-deployed. Probably better to write a Script Component that interfaces with a CLR (Common Language Run-Time) dll, and have all your custom logic stored OUTSIDE the packages. So I have to ask, what kind of validation are your doing? Your original post only mentions that the field [Premium] needs to be over 10,000. (I'll assume there HAS to be more but you used a simple example for clarity). How complex is your "rules engine"? How many inputs? Another thought: Separate the 'rules engine' logic from the WCF. These two perform two different functions (IMHO). Unless, of course, the call to the WCF is actually PART of the validation process.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

I tried many examples from microsoft - Which is looping through the input rows and creating the output buffer. I am stuck in where I need to create a output buffer. If you can send me some code snippet that does the same, then it will be great!. The example that I have give at the starting is just to show you the input and what needs to be the output. Actually the rule engine is pretty complex and there are N number of rules defined for fields. I tried the following, http://msdn.microsoft.com/en-us/library/ms135931.aspx but didn't work. It doesn't allow me to connect to the destination because there are no input columns from the custom component I am focusing now on doing the following things in the custom component. 1. My component should be act as a transformation component . 2. Lets say for example I am having 100 rows which is coming from the source, this component should spit out only 50 rows. 3. But I want to achieve this in a custom component with code written for adding the rows in to the output buffer. Thanks -Sajin

Hi All, I got the code to work as I decided. This is just for a demo purpose I did, but routing to different output based on some rule engine. Thanks to all. using System; using Microsoft.SqlServer.Dts.Pipeline; using Microsoft.SqlServer.Dts.Pipeline.Wrapper; using Microsoft.SqlServer.Dts.Runtime.Wrapper; namespace Microsoft.Samples.SqlServer.Dts { [ DtsPipelineComponent(DisplayName = "AsynchronousOutput")] public class AsynchronousOutput : PipelineComponent { public override void ProvideComponentProperties() { IDTSInput100 input = ComponentMetaData.InputCollection.New(); input.Name = "DTSSampleInput"; IDTSOutput100 output1 = ComponentMetaData.OutputCollection.New(); output1.ExclusionGroup = 1; output1.Name = "Output1"; output1.SynchronousInputID = input.ID; IDTSOutput100 output2 = ComponentMetaData.OutputCollection.New(); output2.ExclusionGroup = 1; output2.Name = "Output2"; output2.SynchronousInputID = input.ID; } public override void ProcessInput(int inputID, PipelineBuffer buffer) { IDTSOutput100 output1 = ComponentMetaData.OutputCollection[0]; IDTSOutput100 output2 = ComponentMetaData.OutputCollection[1]; int rowIndex = 1; while (buffer.NextRow()) { // TODO: Insert if/then block here. // If criteria is met, direct the buffer row to output1. if (rowIndex <= 10) { buffer.DirectRow(output1.ID); } else { // Otherwise, direct the row to output2. buffer.DirectRow(output2.ID); } rowIndex += 1; } } } }