Introduction
Our friends at Sqlis.com created a Data Flow Component I use an awful lot when building SSIS packages: the Trash Destination Adapter.
Adding to the Toolbox
After downloading and installing the version you wish to use, right-click the SSIS toolbox and click Choose Items:
When the Choose Toolbox Items window displays, click the SSIS Data Flow Items tab and then check the Trash Destination checkbox:
Click Ok and voila! You have added the Trash Destination adapter to your toolbox.
Putting it to Use
I’m using the Data Flow I started in SSIS Snack: Data Preview, adding a Trash Destination adapter below the OLE DB Source I’ve renamed “Dummy Source”. Connect the output of the OLE DB Source to the Trash Destination. What’s this good for? Well, for one thing, I can see how many rows are flowing from my source:
I can put it to other uses too. More in another SSIS Snack.
:{> Andy
Andy,
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.
-Jamie
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
Hmmm..should you NEED reminding?
We must agree to disagree on this one I’m afraid!
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.
Eric,
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.
-Jamie
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.
Keith
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
–Siddharth.
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).
-Jamie
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.
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.
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…