Microsoft SSIS 2005 removing columns from an XML source

Question

I have an XML source in a Microsoft SSIS 2005 package and when I debug the package I am receiving warnings like:

[DTS.Pipeline] Warning: The output column "AccBasicRateDesc" (15229) on output "AccFwdDetail" (303) and component "XML Source" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.

So I tried to remove all of these but this seems to cause a problem as there will be whole xml outputs that I will not be using and this gives me the error:

Error at Contact Insert [DTS.Pipeline]: "output "AccFwdDetail" (19)" contains no output columns. An asynchronous output must contain output columns.

Is there a neat way to define which outputs I want from the xml file? I have tried removing the whole output in the advanced editor but this gives me the error:

Error at Contact Insert [XML Source [1]]: An output cannot be removed from the outputs column.

Answer 1

Looks like Catch 42.

I would just ignore the original warning in this case - it is just a performance warning, it would not cause any real problems. And the performance implications are minor in this case, since nobody will consume data from this output.

Answer 2

After the data source, use a Union All component (with your data source as the only input) in your data flow. Remove any unused columns here.

As far as I know that's the only way to get rid of those warnings.

Here are some really useful links about SSIS performance:

http://blogs.msdn.com/michen/archive/2007/06/11/katmai-ssis-data-flow-improvements.aspx

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx

http://www.simple-talk.com/sql/sql-server-2005/sql-server-2005-ssis-tuning-the-dataflow-task/