Hello Guys, We have the following requirement. We need to create a custom component, which will be put in our existing ETL Packages. This component will call the Rules engine and will send the data to the destination. I want something which can override the Existing OLEDB Destination component. i.e. a container control which will call Rules engine + the custom destination component. The idea is that i will need to modify the existing ETL Packages. The only change i need to do is to replace the OLEDB destination component will this Custom Component and no other change should be required. Can you please suggest, which is the best approach and any articles or code for direction. thanks

Hello Guys, We have the following requirement. We need to create a custom component, which will be put in our existing ETL Packages. This component will call the Rules engine and will send the data to the destination. I want something which can override the Existing OLEDB Destination component. i.e. a container control which will call Rules engine + the custom destination component. The idea is that i will need to modify the existing ETL Packages. The only change i need to do is to replace the OLEDB destination component will this Custom Component and no other change should be required. Can you please suggest, which is the best approach and any articles or code for direction. thanks What do you mean under "override the existing OLEDB connection"? What is your custom destination?Arthur My Blog

You can not create this functionality. Existing components are not "overrideable" (in the programming sense). If you create this custom component, you will have to recreate the desired functionality of the Destination component by yourself. (This shouldn't be particularly hard.) I would suggest that you start by using a Script as a Destination to work out your major issues, then convert that script to a custom component. Talk to me now on

Thanks Todd... Can you please point me to any good examples in this area?

Hi, Just add to Todd, there is a sample for your reference: http://msdn.microsoft.com/en-us/library/ms135939.aspx Thanks, Jin ChenJin Chen - MSFT

Thanks Jim and Todd. I am currently stuck in this dilemma. I have created samples for the Script Transform Component, where say 100 rows come in, i do some transformation - like making those chars UpperCase and the data goes to the Destination. i.e. all 100 rows. This is fine. What we need is say 100 rows come in, we call the rules engine and the rules engine gives us say 80 rows, i want to create two output rows - one for 80 rows, other for 20 rows. Since i am new to SSIS, would like your help. For a start, even if i am able to manipulate the output rows to send only 80 rows it is fine for now. If there is any sample of a custom component, which shows this, it is appreciated. Please let me know. thanks

If you wish to change the number of rows flowing through, you will need to follow the pattern for an asynchronous transformation - Creating an Asynchronous Transformation with the Script Component I thought the plan was to do all the work in a destination, which would mean you could decided internally what rows to insert or not. Jin Chen posted the link to the help for creating a destination in the script component above.http://www.sqlis.com | http://www.konesans.com

Thanks Darren. Yes, we wanted to do all the work in the destination. We were trying to override the new ADO.net Destination component and manipulate the rows sent to the destination but we were stuck there since we could not manipulate the output getting sent to the destination. So, we tried to do it through the script transform component. Also, the challenge, is that the component needs to be generic, we need to put into existing etl packages. We haven't still figured out what is the right strategy. On a separate note, is it possible to create a composite component - i.e. scripting component +conditional split into one? thanks

You cannot create a composite component, but peforming the split like behaviour is quite easy with a script component, e.g. http://www.sqlis.com/sqlis/post/Using-the-Script-Component-as-a-Conditional-Split.aspxhttp://www.sqlis.com | http://www.konesans.com

Thanks Darren. This example does show how to split the component. Also, good to know that we cannot create composite component. However, in my case, the component i need to create is a generic script component. This component will be put before the destination component or can be a destination package as long as it is able to validate the data. (So all components will receive data + will have data validated + spit output.) The input to this component will differ from package to package. i.e. 100 rows comes in, we do some validation and only 80 rows needs to be sent to this destination. I am sorry for asking you guys over but i am new to SSIS and unable to get a grip around it. I would appreciate if you could mention what strategy to follow ? thanks

What do you mean by generic? The script component is very specific, you write code to do what you need. Like all components, it cannot cater for changing columns. It is also not re-usable, other than copy and paste. For easier reuse you need to write a custom component. A custom component was originally suggested, but also that you could prototype ideas in a script component as it is often faster. You talk about filtering rows, 100 come in 80 go out, so why does my split sample not do this for you? Just like the real conditional split you do not have to connect all, or any, outputs, so the 20 row output just leave alone.http://www.sqlis.com | http://www.konesans.com

Thanks Darren. Your script component did work as i said but what i was looking at is a solution which can work for all our existing packages. The sample seemed to fit for a package specific scenario not a generic scenario. By Generic i mean, we have existing SSIS packages. I want this component to be placed into this existing packages so that it can read the data coming in and then apply the rules to that data and send the data to the destination. If the component is specific to a package, i need to write it for all the packages isn't it? Basically, i am looking for externalizing the rules so that i can put this component in all package. This way the component might do the following 1) Read the incoming data (e.g. 100 rows) 2) Call other external component which will validate the rules. We will pass the PackageIdentifier + Recordset 3) Send the filtered data to the destination (e.g. 80 rows) I just need to put this into all our existing packages and that is the reason i want it to be generic. If the script component cannot do it, which is an alternative i should be looking into? Since i am new to ssis, i am unable to get a grip on how this can be done. Your input is appreciated as always. Thanks...

You can do this via a custom component. The earlier suggestion was to prototype your functionality in a script component - then port that to a custom component (which is perfectly valid advice). I think you need to spend some time looking at some of the sample components available, so you have a better framework for asking your questions. Everything that you have asked about is possible in a custom component, but you're going to have to learn enough about them to implement it. We're not going to be able to send you an exact code sample for this, as it sounds like you have specific and unique requirements - so it's going to take a lot of effort on your part. A good place to look at samples is http://ssisctc.codeplex.com. Many open source components are listed there - find one that seems to have some of the functionality you are looking for, and study it to understand what it is doing.John Welch | www.varigence.com | www.agilebi.com | ssisUnit.codeplex.com