THE SQL Server Blog Spot on the Web

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

Andy Leonard

Andy Leonard is CSO of Linchpin People and SQLPeople, an SSIS Trainer, Consultant, and developer; a Business Intelligence Markup Language (Biml) developer; SQL Server database and data warehouse developer, community mentor, engineer, and farmer. He is a co-author of SQL Server 2012 Integration Services Design Patterns. His background includes web application architecture and development, VB, and ASP. Andy loves the SQL Server Community!
Note: Comments are moderated. Spam shall not pass! </GandalfVoice>

SSIS Snack: The Trash Destination Adapter

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

 

Published Thursday, March 04, 2010 8:00 AM by andyleonard
Filed under:

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

Comments

 

jamiet said:

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

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:

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

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.

Keith

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 :)

--Siddharth.

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).

-Jamie

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

(required) 
(required) 
Submit

This Blog

Syndication

My Company


Other Blog

Check out my personal blog...
http://andyleonard.me

Contact Me

Twitter: @AndyLeonard
Email: andy.leonard@gmail.com

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement