Which is faster for SSIS, a View on a table or a Conditional Split?

Question

I have an SSIS project where one of the steps involves populating a SQL Server table from an Oracle Table.

The Oracle table has a column ssis_control_flag. I want to pull across all records that have this field set to 'T'.

Now, I was wondering which would be the best way of doing this, and the two options as I have detailed in the question presented themselves.

So really, I am wondering which would be faster/better. Should I create a conditional split in the SSIS package that filters off all the records I want? Or should I create a view in Oracle that selects the records based on the criteria, and utilise that view as the data source in SSIS?

Or is there an even better way of doing this? You help would be much appreciated!

Thanks

Answer 1

Why don't you use a WHERE clause to filter the records, instead of creating a view? May be I am not getting your question correctly.

Then in general, bringing all the data to SSIS and then filtering out is not recommended. Especially when you can do the filtering at the source DB end itself. Consider the network bandwidth costs as well.

Then this particular filter that you are talking about here, cannot be done with a better efficiency in SSIS than that can be done at DB. Hence better do it in the Oracle DB itself.

Answer 2

You can use a query using openrowset as the source for the dataflow instead of directly accessing the Oracle table.