THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is an author and engineer who enjoys building and automating data integration solutions. Andy is co-host of the Data Driven podcast. Andy is no longer updating this blog. His current blog is

SSIS Snack: The Trash Destination Adapter

This blog has moved! You can find this content at the following new location:

Published Thursday, March 4, 2010 8:00 AM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



jamiet said:


Given that a data path can be terminated in exactly the same way using a Union All component do you really think that there is a need for a Trash Destination component?

Using Trash Destination just gives you another 3rd party component that you have to distibute - and that is a PITA (in my opinion).

I must hold my hand up and say it was I that originally asked Darren and Allan to build this thing so I must take the blame - I regret it now.


March 4, 2010 7:55 AM

andyleonard said:

So it was *you* Jamie! ;)

I look at it the other way around: If I choose to use third-party SSIS components, the Trash Destination "reminds" me I need to account for them in my SSIS application life cycle.

:{> Andy

March 4, 2010 8:05 AM

jamiet said:

Hmmm..should you NEED reminding?

We must agree to disagree on this one I'm afraid! :)

March 4, 2010 8:25 AM

ewisdahl said:

I'm with Jamie... Except I must say that rather than a union all I go ahead and terminate with a row count.  No reason really, just something I got into the habit of doing when I first started out.

March 4, 2010 8:37 AM

jamiet said:


Yeah a Rowcount works well, maybe better in fact as the Union All may attempt to do some work (maybe wrong about that). I choose not to use a Rowcount cos I know my absent-minded self will forget to remove the variable that I have to create for it.


March 4, 2010 9:08 AM

Keith Mescha said:

Having this component has actually burned us a few times. Developers pushing code to production with this in there and wondering why it's failing. Well the trash component was not installed on the prod machine.

Had to make them all unlearn this and use other tricks like stated in the other comments above.

I do realize the bigger issue was why are the pushing code with this in there but that is another story.


March 4, 2010 9:27 AM

Siddharth Mehta said:

I have gone thru the stack of freeware components that SQLIS offers, and I have always struggled to understand the use of this one. Multicast and UnionAll can be easily used for the same purpose.

It has just one good use, an interview question to ask some beginner on which transform can be used instead of this one :)


March 4, 2010 9:35 AM

jamiet said:

Its also worth saying that in actual fact its better to have no component at all; there's nothing in SSIS that stipulates that an output has to be hooked up to something.

The only reasons that I know of for actually using a Trash Destination component (or, indeed, a Union All/Rowcount component for the same purpose terminating a datapath) is so that (1) you can display a data viewer or (2) see how many rows are in a datapath (which Andy has pointed out above).


March 4, 2010 10:03 AM

Chris Randall said:

I've been using a Multicast component for this purposes in my SSIS courses for several years. Having a named "Trash" component is a nice idea, but not necessary.

March 4, 2010 6:27 PM

Jack Corbett said:

Interesting discussion.  I like the Trash Destination.  Granted I don't do a ton of SSIS, but I like it because when I see it I know I don't have any plans for that transform, where if I use a valid transform I may waste time wondering why I have it in the flow.  In the event that I go away from the package for a time and then go back to it.

I don't know of any reason to have a data flow that doesn't use the data so I wouldn't think you'd want the trash destination on your production servers.  I'd rather have the package fail.

March 5, 2010 11:45 AM

Kevin Lewis said:

What's the difference of using the trash destination and merely sending the output to a script file destination?

i.e the script being set as:

public override void Input0_ProcessInputRow(Input0Buffer Row)


       int i = 0;


I think it's useful to use a trash type destination to get rid of those ruddy XML warnings on the XML source adaptor for result sets you're not using. Sure I could use an XML Task, run out loads of different new XML files via XSLT and then load... but that just seems as wastefull...

April 30, 2010 6:24 AM

Leave a Comment


This Blog



My Latest Book:

Community Awards

Friend of Red Gate

Contact Me


Privacy Statement