A Custom component for use as a VIEW in SSIS- Is it possible to create one MERGE like component

Hi all

I'm into a project which uses a lot of views for joining 2 or more tables. Using the MERGE component in SSIS will be a huge effort coz it only has 2 inputs and I gotta SORT the input too.

Isnt it possible to have a VIEW like component that joins more than 2 tables and DOESNT need sorting?

(I've thought about creating views in database engine but it breaks my data floe in SSIS and is'nt a practical solution)

You have a few options. You can implement views in the database engine (not sure why it breaks your data flow, but it's perhaps the best option), you can join your tables via SQL in an OLE DB Source component, or you can use multiple OLE DB Source connections and then use one or more merge join transformations.

Just an FYI - If your data is sorted with an ORDER BY clause in the SQL statement, you can set the ISSORTED flag to true (1) on the OLE DB source and a SORT component won't be required.|||

Thanks Phil for the quick response..

I said "using Views breaks my data flow" - I meant all views cannot be represented in SSIS data flow right? so it becomes difficult later on to understand/change the data flow coz I'll have to check views in database manager and other stuff in SSIS. OR is there a way we can export the whole data flow into some kinda diagram so I can see it all at once? (I don't think there is right?)

So my problem boils down to creating a VIEW type custom component. I have seen other custom component samples but don't know how to do JOINs via code. Can you give me a brief idea for, say, if I have to join 2 tables how many PipelineBuffers I'll need (2 for input and 1 for output?) and how do I match values between the two joining columns(col1.value==col2.value?)?

Guess this is a big question but Im sure it'll help a lot of developers.

thanks

Ravi

|||

If you want to power and performance gain of views and T-SQL, then use T-SQL. Use the relational engine for what it is good for is my opinion. A component to avoid having a view/SELECT when it would do the job best of all is just daft.

If you want some of the re-use of a view but without the object itself, you may want to look at Data Source Views. They are a design-time only feature, but are stored in your SSIS project so may meet your requirement. So you would use the same SELECT statement as in the view, but it would not be a SQL object.

|||

Hi Darren, I think u didnt get my problem right?

I just want help creating a MERGE type component which can join more than 2 inputs. I understand I can use more than one MERGEs to do the same, which ill do if creating the component is a pain ..