This project is read-only.

DatasetDestination Component Sample

The DatasetDestination sample demonstrates how to implement a custom destination component that is integrated into Business Intelligence Development Studio and can be added to a Data Flow task. The component stores the rows it receives during execution in a dataset. At the end of execution, depending on the configuration of the component, the dataset is assigned to a run-time variable and may also be saved to an XML file. The XML file that is created from the dataset can be used as the XML source for the XML Source. This design allows the results of one data flow execution to be saved externally and used as the source of another component. This sample is not supported on Itanium-based operating systems.

The Integration Services Data Flow Programming code samples are intended to demonstrate the core functionality that you must implement to create a custom data flow component. The samples do not include full support for customization in the Advanced Editor. For example, you cannot use the Advanced Editor to add or remove inputs and outputs or to configure columns. Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples.

Running the Sample

If you already know how to locate, build, and install code samples, you can go directly to the section testing ., and read about how to configure and run the code sample.


This sample requires that the following components be installed.
  • Microsoft Visual Studio 2005
  • Microsoft SQL Server 2005 Integration Services


If the code sample was installed to the default location, the sample is located in the following directory:
C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Programming Samples\Data Flow\ DataSetDestination Component Sample\
The C# solution for the code sample is located in the CS directory, and the Visual Basic solution is located in the VB directory.
For information about the two-step process required to install the samples, see Installing Samples. To obtain the latest version of the samples, including new samples released after the original release of SQL Server 2005, see SQL Server 2005 Samples and Sample Databases (April 2006).

Building the Sample

If you have not already generated a strong name key file in the Samples folder, use the following procedure to generate this key file. The sample projects are configured to sign assemblies at build time with this key file. You can view the signing properties on the Signing tab of the Project Properties dialog box.
To generate a strong name key file
  1. To open a Microsoft Visual Studio 2005 command prompt, click Start, point to All Programs, point to Microsoft Visual Studio 2005, point to Visual Studio Tools, and then click Visual Studio 2005 Command Prompt.- or -To open a Microsoft .NET Framework command prompt, click Start, point to All Programs, point to Microsoft .NET Framework SDK 2.0, and then click SDK Command Prompt.
  2. At the command prompt, use the change directory (CD) command to change the current folder of the Command Prompt window to the Samples folder. The key file that you create in this folder will be used by all SQL Server 2005 code samples. Note: To determine the folder where samples are located, click Start, point to All Programs, point to Microsoft SQL Server 2005, point to Documentation and Tutorials, and then click Samples Directory. If the default installation location was used, the samples are located in <system_drive>:\Program Files\Microsoft SQL Server\90\Samples.
  3. At the command prompt, run the following command to generate the key file: * sn -k SampleKey.snk *
  4. Important For more information about the strong-name key pair, see "Security Briefs: Strong Names and Security in the .NET Framework" in the .NET Development Center on MSDN.
To build the sample in Microsoft Visual Studio 2005
  1. From the File | Open menu, click Project and open the DataSetDestination.slnin your preferred programming language.
  2. On the Build menu, click Build DatasetDestination to build the project.

Installing the Sample

This sample is provided in both Visual Basic and C#. To distinguish the assemblies for each version of the sample, the name of the output assembly has CS or VB appended. After successfully building the component, follow these steps to add it to a Data Flow task in Business Intelligence Development Studio.
To copy the component to the PipelineComponents folder
  1. Open Windows Explorer or your preferred application for working in the file system.
  2. Copy the assembly (DatasetDestinationCS.dll or DatasetDestinationVB.dll) to the PipelineComponents folder located at %system%\Program Files\Microsoft SQL Server\90\DTS.
To install the component into the global assembly cache (GAC) by dragging the assembly
  1. Open Windows Explorer or your preferred application for working in the file system.
  2. Drag the assembly from the PipelineComponents folder to the folder where the global assembly cache (GAC) is located, at %system%\assembly.
To install the component into the global assembly cache (GAC) by using gacutil.exe
  1. Open a Command Prompt window.
  2. Type the following command to run gacutil.exe and install the C# version of the component into the GAC:** gacutil.exe -iF "c:\Program Files\Microsoft Sql Server\90\DTS\PipelineComponents\DataSetDestinationCS.dll " - or - Type the following command to run gacutil.exe and install the Visual Basic version of the component into the GAC:** gacutil.exe -iF "c:\Program Files\Microsoft Sql Server\90\DTS\PipelineComponents\DataSetDestinationVB.dll "
To add the component to the Toolbox
  1. Open Business Intelligence Development Studio.
  2. Right-click the toolbox and then click Choose Items.
  3. In the Choose Toolbox Items dialog box, click the SSIS Data Flow Items tab.
  4. Click the check box next to your component, and then click OK.
  5. Note If the component is not displayed in the list, you can click Browse to locate the component yourself. However in this case it may not be installed correctly.After you complete these steps, the component is visible in the Data Flow Items tab of the Toolbox, and can be added to the Data Flow task in SSIS Designer.
Testing the Sample
After you have added the sample component to a Data Flow task in a package, you can configure it in SSIS Designer. The DataSet Destination component has three custom properties that control its execution behavior. The component executes successfully if you leave these properties at their default values. In this case, the dataset is not saved at the end of execution.

Property Description
RuntimeVariable Specifies the name of a run-time variable to which the dataset is written after execution.
DataTableName Specifies the name of the DataTable that is either created (for a new dataset) or appended to (for an existing datasets).
SaveDataSetToXml Specifies whether the dataset is saved to an XML file at the end of execution. When true, a File connection manager must be specified for the component.

© 2007 Microsoft Corporation. All rights reserved.

Last edited Nov 7, 2008 at 12:07 AM by bonniefe, version 15


No comments yet.