Starting an SSIS Custom Component Project

Although each of the component types performs very different functions, the way you build each custom component type is very similar. To set up each of the component types, start with the following general steps:

1.	Create the solution.
2.	Add a strong name and key file.
3.	Reference SSIS and other assemblies.
4.	Define the class.
5.	Compile and install.

Creating the Solution

In this step, you'll create a new solution for the component to live in. This step is identical for all component types, but for this discussion you'll be building a task.

1.	Open Microsoft Visual Studio.
2.	Select File, New, Project. The New Project dialog box opens, as shown in Figure 24.1. Figure 24.1. Setting up a new class library solution
3.	Select the Visual C# project type and the Class Library template.
4.	Type in the name and location of the solution.
5.	Click the OK button.
6.	Right-click on the .cs source file in the Solution Explorer, and select the Rename menu option.
7.	Change the name of the source file to something meaningful. The sample source file is SampleTask.cs.

Adding a Strong Name and Key File

.NET requires that the component have a strong name. To do this, you must create a key file and link the key file to the component. This step is identical for all component types. Fortunately, Visual Studio makes this very easy, as follows:

1.	Right-click on the project in the Solution Explorer, and select Properties.
2.	Select the Signing tab in the Project Properties dialog box. Figure 24.2 shows the Signing tab with the settings input. Figure 24.2. Signing the assembly
3.	Put a check in the Sign the Assembly check box.
4.	Click the drop down to choose a strong name key file.
5.	Select New to create a new key file.
6.	The Create Strong Name Key dialog box opens. Type in a new key file name. The sample key file name is SampleKeyfile.snk.
7.	Click the OK button.
8.	Uncheck the option to protect the key file with a password for this sample.
9.	Click the OK button.
10.	Save the project ([Ctrl+S]) and close the Project Properties dialog box.

Referencing SSIS and Other Assemblies

All component types need this step, but depending on the type of component, the assemblies you reference will be different. For Foreach Enumerators, connection managers, log providers, and tasks, you need to reference the Microsoft.SqlServer.Dts.Runtime assembly.

Caution

In rare circumstances, you might need to reference the Microsoft.SqlServer.Dts.Runtime.Wrapper assembly as well. However, doing so can cause some confusion as there are many similarly named objects in both assemblies. As a rule, when building runtime components, you should not need to reference the wrapper assembly and should avoid doing so unless absolutely required.

For data flow components, you need to use the following namespaces and reference the associated assemblies:

• Microsoft.SqlServer.Dts.Runtime found in the Microsoft.SqlServer.ManagedDTS assembly

• Microsoft.SqlServer.Dts.Runtime.Wrapper found in the Microsoft.SqlServer.DTSRuntimeWrap assembly

• Microsoft.SqlServer.Dts.Pipeline found in the Microsoft.SqlServer.PipelineHost assembly

• Microsoft.SqlServer.Dts.Pipeline.Wrapper found in the Microsoft.SqlServer.DtsPipelineWrap assembly

To reference each of the assemblies in your project, follow these steps:

1.	Right-click on the References node in the Solution Explorer and select Add Reference. The Add Reference dialog box opens, as shown in Figure 24.3. Figure 24.3. Adding a reference to the ManagedDTS assembly
2.	Select the Microsoft.SqlServer.ManagedDTS assembly.
3.	At the top of the source file, for example SampleTask.cs, type in the USING statement. For Microsoft.SqlServer.Dts.Runtime, type in the following case-sensitive code: using Microsoft.SqlServer.Dts.Runtime;
4.	Repeat these steps for each assembly you want to reference.

Defining the Class

There are two parts to this step. One is to derive from the base component so that you get the default behavior of the base class. The other is adding attributes. The attributes provide information that the runtime examines when enumerating the components. This step is different for each component type.

Deriving from the Base Class

Integration Services provides an abstract base class for each of the component types. Following is a list of the base class names:

• Task

• LogProviderBase

• ConnectionManagerBase

• ForEachEnumerator

• PipelineComponent

By deriving from one of these base classes, you can create the corresponding SSIS class. For this sample, derive the SampleTask from the Task base. When you have done so, the SampleTask.cs file should contain the following code:

using System; using System.Collections.Generic; using System.Text; using Microsoft.SqlServer.Dts.Runtime; namespace SampleTask {    public class SampleTask : Task    {    } } 

Adding Attributes

The second part of this step is to identify the new class to Integration Services. Integration Services enumerates components based on two criteria: location and attributes.

Location

Integration Services components are installed in the DTS subfolder of the SQL Server installation location. The default location is in the Program Files directory.

C:\Program Files\Microsoft SQL Server\90\DTS 

Under this folder, there are five folders of interest to this discussion, as follows:

• Connections

• ForEachEnumerators

• LogProviders

• PipelineComponents

• Tasks

This is where Integration Services looks when enumerating components and does not detect managed components in any other location. In the Compile and Install step described later, you'll see how to set up the project to directly compile the component into the correct folder.

Attributes

Integration Services opens each of the assemblies in those folders and reflects on the attributes of the contained classes. This is how the TaskInfos, PipelineComponentInfos, and other info objects on the Application object are enumerated.

Table 23.1 shows the attributes and their purpose.

Table 23.1. SSIS Custom Component Attributes

Attributes Where Found	Parameters	Description
All Attributes
(DtsTask, DtsPipelineComponent, DtsForEachEnumerator, DtsConnection,
DtsLogProvider)	Description	Contains the description for the task that is displayed in various designer locations
All Attributes	DisplayName	Specifies the name used to build the component name in the designer; for example, FTPTask, FTPTask 1, FTPTask 2, and so forth and shown in ToolTips
All Attributes	IconResource	Points to the icon for the task
All Attributes	LocalizationType	Provides the class type that provides values for the DtsLocalizableAttribute.
All Attributes	UITypeName	Specifies the qualified name of the User Interface assembly and class for the component
DtsPipelineComponent	NoEditor	Indicates if the component supports the Advanced Editor
DtsPipelineComponent, DtsTask	RequiredProductLevel	Indicates the edition of SQL Server that must be installed for this component to be available
DtsTask	TaskType	Specifies what version of SSIS the task was created; typically "DTS90"
DtsPipelineComponent	ComponentType	Specifies the type of component: Transform, Destination, or Source
DtsLogProvider	LogProviderType	Specifies what version of SSIS the task was created; typically "DTS90"
DtsConnection	ConnectionType	Specifies the type of connection manager, for example FILE, FTP, or SMOServer
DtsPipelineComponent	CurrentVersion	Specifies the version of the component and is used when automatically upgrading components
DtsTask	TaskContact	Specifies contact information
DtsLogProvider	LogProviderContact	Specifies contact information
DtsForEachEnumerator	ForEachEnumeratorContact	Specifies contact information
DtsConnection	ConnectionContact	Specifies contact information

For the sample task, in the line above the class declaration, add the following attribute and parameters:

[ DtsTask(    DisplayName="SampleTask",    Description="A sample task",    TaskContact="SampleTask, Copyright © Your Name") ] 

With the attribute added, everything necessary for the designer to identify and load the component is in place. The next step is to compile and install the component.

Compiling and Installing

To make a component visible to Integration Services, you need to compile it, place it in the GAC, and move the component assembly to one of the enumeration folders under the DTS folder mentioned previously. For the sample task, that is the DTS\Tasks folder.

Setting the Output Path

To automatically move the component to the correct folder when you build it, set the output path on the Project Properties Build page.

Note

It's important to remember that, when correctly installed, there is a copy of the component in two locations. The following location, on the file system under the SQL Server folder, is for enumeration only. In fact, if you never do any design work on a machine, but only run packages, there is no need for the components in the special folders.

When loading packages, SSIS only references the copies of the components in the GAC.

The following steps show how to set up the output path for the compiled component:

1.	Right-click on the project in the Solution Explorer.
2.	Select the Properties menu option to open the Project Properties dialog box.
3.	Select the Build tab.
4.	In the Output Path field, type in the folder where the tasks are stored. Typically, it is C:\Program Files\Microsoft SQL Server\90\DTS\Tasks.

Figure 24.4 shows the correct settings in the Project Properties dialog box.

Figure 24.4. Setting the output folder for the sample task

Installing into the GAC

The next step is to install the component into the GAC:

1.	Select the Build Events tab in the Project Properties dialog box.
2.	In the Post-Build Event Command Line field, enter the following command: gacutil /if "C:\program Files\microsoft sql Server\90\DTS\Tasks\ $(TargetFileName)"
3.	Ensure that the Run the Post-Build Event option is set to On Successful Build.
4.	Close the Project Properties dialog box.
5.	Save the project ([Ctrl+S]).

Caution

Make sure that the gacutil.exe utility is on the path so that this command will succeed.

Building the Component

Now you need to build the component. If you've got everything right in the sample, the assembly compiles directly to the folder location where it can be enumerated by the designer and another copy gets placed into the GAC. Then, you can open the Business Intelligence Development Studio (BIDS) and add the task in the Toolbox.

1.	From the Build menu, select Build Solution.
2.	Right-click on the Solution node in the Solution Explorer.
3.	Select Add, New Project to open the Add New Project dialog box.
4.	In the Name field, type in SampleTaskTest.
5.	Click the OK button.
6.	The Add New Project dialog box closes and a new SSIS project is created with the package open.
7.	If the Toolbox isn't visible, select View, Toolbox ([Ctrl+Alt+X]).
8.	Right-click anywhere on the Toolbox and select Choose Items to open the Choose Toolbox Items dialog box. It might take a few seconds to open because it has to enumerate all the components.
9.	Choose the SSIS Control Flow Items tab to see the available tasks.
10.	Check the SampleTask component check box, as shown in Figure 24.5. Figure 24.5. Setting the output folder for the sample task
11.	Click the OK button.
12.	Notice that the SampleTask is now available in the Toolbox.
13.	Drag the task over to the designer.
14.	Double-click the task and get the message shown in Figure 24.6. Figure 24.6. The SampleTask has no task UI

The message box is telling you that there is no TaskUI for the task. That's because you haven't built a TaskUI for the sample task yet. However, if you run the package, the task will still successfully execute because the base class provides the default Execute methods. Also, even though there is no task UI, you can still modify properties through the property grid.

How the Runtime Finds Your Installed Tasks

To understand how the runtime finds your custom task and other components, take a look at the Registry by opening regedit.

1.	Click the Start menu.
2.	Select Run.
3.	Type regedit in the Open text box.
4.	Click the OK button.
5.	Find HKEY_LOCAL_MACHINE\Software\Microsoft\MSDTS\Setup.

A number of subkeys are in the Setup key. Click on the DTSPath key and look at the Default value. For most installs, this is C:\Program Files\Microsoft SQL Server\90\DTS\ and is the fully qualified path to the base directory where tasks are stored. If this key isn't there, the application object searches: HKLM\Software\Microsoft\MSSQLServer\Setup for the fully qualified base directory. If neither of these values is found, the enumeration fails.

After retrieving the base directory, the runtime appends "\tasks" to the base path to create the final directory where tasks assemblies are stored to generate the folder path. The runtime reflects on all assemblies in this folder and tries to find classes with the DtsTask attribute. If you look in that folder, you'll notice that the stock task assemblies are also stored there. Again, Microsoft has no special location or process for including stock components. The same rules apply to stock and custom components, so all components are enumerated and included in the designer in the same way.

This sample task is an example of the most rudimentary component you can write. It shows how simple it is to start, code, install, add to the designer, and execute with very little effort. However, it is only a simple component with no real purpose other than to serve as a sample. The following discussion describes how to expand on the basic sample and make it a full-featured SSIS citizen.