Trash Destination Adapter

by Darren Green 6 Sep 2014 15:41

The Trash Destination and this article came from early experiences of using SSIS and community feedback at the time. When developing a package it is very useful to have a destination adapter that does nothing but consume rows with no setup requirement. You often want run a package part way through development, or just add a path so you can set a Data Viewer. There are stock tasks that can be used, but with the Trash Destination all columns are treated as selected automatically (usage type of read-only), so the pipeline knows they are required. It is also obvious that this is for development or diagnostic purposes, and is clearly not a part of the functional design of the package. It is also ideal for just playing around and exploring concepts in SSIS, and is often used in conjunction with the Data Generator Source. Using these two components it is easy to setup a test of an expression in the Derived Column Transformation for example. The Data Generator Source provides some dummy data, and the Trash Destination allows you to anchor the output path and set a Data Viewer to examine the results.

It can also be used when performance tuning packages. It is a consistent and known quantity that has no external influences, so it is ideal as a destination when breaking the data flow into sections to isolate a bottleneck.

The adapter is really simple to use and requires no setup. Simply drop it onto the pipeline designer and use it to terminate your data flow path.

Installation

The component is provided as an MSI file which you can download and run to install it. This simply places the files on disk in the correct locations and also installs the assemblies in the Global Assembly Cache as per Microsoft’s recommendations.

You may need to restart the SQL Server Integration Services service, as this caches information about what components are installed, as well as restarting any open instances of Business Intelligence Development Studio (BIDS) / Visual Studio that you may be using to build your SSIS packages.

Finally, for 2005/2008, you will have to add the transformation to the Visual Studio toolbox manually. Right-click the toolbox, and select Choose Items.... Select the SSIS Data Flow Items tab, and then check the Trash Destination transformation in the Choose Toolbox Items window. This process has been described in detail in the related FAQ entry for How do I install a task or transform component?

We recommend you follow best practice and apply the current Microsoft SQL Server Service pack to your SQL Server servers and workstations.

Downloads

The Trash Destination is available for SQL Server 2005, SQL Server 2008 (includes R2) and SQL Server 2012. Please choose the version to match your SQL Server version, or you can install multiple versions and use them side by side if you have more than one version of SQL Server installed.

Trash Destination for SQL Server 2005

Trash Destination for SQL Server 2008

Trash Destination for SQL Server 2012

Trash Destination for SQL Server 2014

Version History

SQL Server 2014

Version 4.0.0.34 - SQL Server 2014 release. Includes upgrade support for 2005, 2008 and 2012 packages to 2014.
(6 Sep 2014)

SQL Server 2012

Version 3.0.0.34 - SQL Server 2012 release. Includes upgrade support for both 2005 and 2008 packages to 2012.
(5 Jun 2012)

SQL Server 2008

Version 2.0.0.33 - SQL Server 2008 release. Includes support for upgrade of 2005 packages. RTM compatible, previously February 2008 CTP.
(4 Mar 2008)

Version 2.0.0.31 - SQL Server 2008 November 2007 CTP.
(14 Feb 2008)

SQL Server 2005

Version 1.0.2.18 - SQL Server 2005 RTM Refresh. SP1 Compatibility Testing.
(12 Jun 2006)

Version 1.0.1.1 - SQL Server 2005 IDW 15 June CTP. Minor enhancements over v1.0.1.0.
(11 Jun 2005)

Version 1.0.1.0 - SQL Server 2005 IDW 14 April CTP. First Public Release.
(30 May 2005)

Troubleshooting

Make sure you have downloaded the version that matches your version of SQL Server. We offer separate downloads for SQL Server 2005, SQL Server 2008 and SQL Server 2012.

If you an error when you try and use the component along the lines of The component could not be added to the Data Flow task. Please verify that this component is properly installed.  ... The data flow object "Konesans ..." is not installed correctly on this computer, this usually indicates that the internal cache of SSIS components needs to be updated. This is held by the SSIS service, so you need restart the the SQL Server Integration Services service. You can do this from the Services applet in Control Panel or Administrative Tools in Windows. You can also restart the computer if you prefer. You may also need to restart any current instances of Business Intelligence Development Studio (BIDS) / Visual Studio that you may be using to build your SSIS packages.

The full error message is shown below for reference:

TITLE: Microsoft Visual Studio
------------------------------
The component could not be added to the Data Flow task.
Please verify that this component is properly installed.
------------------------------
ADDITIONAL INFORMATION:
The data flow object "Konesans.Dts.Pipeline.TrashDestination.Trash, Konesans.Dts.Pipeline.TrashDestination, Version=1.0.1.0, Culture=neutral, PublicKeyToken=b8351fe7752642cc" is not installed correctly on this computer. (Microsoft.DataTransformationServices.Design)

For 2005/2008, once installation is complete you need to manually add the task to the toolbox before you will see it and to be able add it to packages - How do I install a task or transform component? This is not necessary for SQL Server 2012 as the new SSIS toolbox automatically detects components.

If you are still having issues then contact us, but please provide as much detail as possible about error, as well as which version of the the task you are using and details of the SSIS tools installed.

Comments (15) -

2/10/2009 12:43:31 PM #

Steve Culshaw

I can't get the component to install properly
- VS2005 Team Suite SP1
- SQL Server SP3
- NET 3.5 SP1

I've installed, added it to the toolbox, but when I attempt to drag it onto the design surface, I get ...

TITLE: Microsoft Visual Studio
------------------------------

The component could not be added to the Data Flow task.
Please verify that this component is properly installed.

------------------------------
ADDITIONAL INFORMATION:

The data flow object "Konesans.Dts.Pipeline.TrashDestination.Trash, Konesans.Dts.Pipeline.TrashDestination, Version=1.0.1.0, Culture=neutral, PublicKeyToken=b8351fe7752642cc" is not installed correctly on this computer. (Microsoft.DataTransformationServices.Design)

For help, click: go.microsoft.com/fwlink

Steve Culshaw United Kingdom

3/18/2009 2:34:17 AM #

Kristen

I'm having the same problem - I receive the error when I drag the component onto the canvas

"The data flow object "Konesans.Dts.Pipeline.TrashDestination.Trash, Konesans.Dts.Pipeline.TrashDestination, Version=1.0.1.0, Culture=neutral, PublicKeyToken=b8351fe7752642cc" is not installed correctly on this computer. (Microsoft.DataTransformationServices.Design)"

Uninstalled & reinstalled but same issue occured.

Kristen

3/18/2009 2:36:59 AM #

Kristen

restarting the SSIS service resolved this issue.

Kristen

3/18/2009 4:33:16 PM #

Bill Parrott

Help!

I installed the MSI and added the Task to the toolbox, but when I try and drop this task on the workspace I get the following:

TITLE: Microsoft Visual Studio
------------------------------

The component could not be added to the Data Flow task.
Please verify that this component is properly installed.

------------------------------
ADDITIONAL INFORMATION:

The data flow object "Konesans.Dts.Pipeline.TrashDestination.Trash, Konesans.Dts.Pipeline.TrashDestination, Version=1.0.1.0, Culture=neutral, PublicKeyToken=b8351fe7752642cc" is not installed correctly on this computer. (Microsoft.DataTransformationServices.Design)

For help, click: go.microsoft.com/fwlink

------------------------------
BUTTONS:

OK
------------------------------

The help link from MS leads to an empty page.

Bill Parrott United States

7/21/2009 10:38:46 AM #

Darren Green

I have added details of the "Please verify that this component is properly installed" error to the troubleshooting section. The quick answer is to restart the SSIS service or machine.

Darren Green United Kingdom

8/6/2009 7:53:46 PM #

Rick

Thanks guys, this is super-handy, reliable, and simple. Just what I want in my free stuff!

Rick

12/10/2009 5:30:30 PM #

Mark

I just use the multicast . It does nothing, and doesn't need anything installed

Mark

12/22/2009 10:44:13 PM #

chris reeve

Will the 2008 version work as is on a 64bit server (when package scheduled to run in 64 bit)?

I noticed various database SSIS drivers are 32 bit or 64 bit specific and on a 64 bit server SSIS scheduled packages normally run in 64 bit and all drivers need to be 64 bit (unless force package to be scheduled through agent in 32 bit mode).

I do not know if components have to match the bit of the drivers/ssis package when running in 64 bit mode.

thanks

chris reeve United States

1/4/2010 8:12:35 AM #

Darren

Chris, this component will work on both 32 and 64 bit, just pick the one for your version of SQL Server. It is a .NET component which has been compiled using the Any processor architecture option, so works on either. Most drivers target one processor architecture or the other hence you need to match them explicitly.

Darren United Kingdom

6/10/2010 3:42:52 PM #

Mark

Another good reason for it is that if you have a dataflow that doesn't have a destination at all, it will work when run interactively, but it won't work when run from the package execution utility, as the dataflow components get automatically removed if not used.  We had this problem with some packages that did all their work via partial lookup transformations calling a stored proc, but that didn't have any destination.  Problem solved now with this component.  Thanks!

Mark United States

1/6/2012 3:21:06 PM #

Paul

Hi,

Is there news when the trash destination adapter is available for sql2012?
I tried to convert it from 2008 to 2012 without success, is there a workaround ?

Paul

Paul Netherlands

4/17/2012 8:50:29 PM #

Speedbird186

I've found the Konesans components highly useful and would like to continue using them in SQL 2012.

Without sounding presumptious and expecting the Konesans is going to make SQL Server 2012 compatibility a priority, I am wondering if there are plans for SQL 2012 compatible versions of this (and other) components.

Speedbird186 United States

6/5/2012 2:33:12 PM #

Daren Green

The 2012 download is now available, sorry it took so long.

Daren Green United Kingdom

6/8/2012 7:22:18 PM #

duanecwilson

I have heard that you can use a multicast component that would do the same thing and I have done that? What is the advantage of your trash container or the pitfall of using a multicast or other SSIS standard component?

duanecwilson United States

6/12/2012 11:34:55 PM #

Darren Green

Duane, The multicast will do exactly the same job just as well. I would suggest however that it is a job that only needs doing in development, such as when you want to attach a data viewer into a path that otherwise would not be anchored. Using the Trash Destination has one side benefit, it would fail when you deployed it you server because there is no need to deploy it your server since there is no need to terminate paths. It is really a development and demo tool only.

A quick history lesson, during one of the 2005 beta phases there was a bug that stopped us using the Multicast for this, and other alternatives like Row Count were more work. If it wasn't for that I don 't think it would have ever have been written. I for one was always happy with the Multicast until the interruption caused by that bug.

Darren Green United Kingdom

Pingbacks and trackbacks (1)+

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

RecentComments

Comment RSS