Share via

Readme_Sql Server Custom Connection Manager Sample

  • Article

This sample works only with SQL Server 2005 and SQL Server 2008. It will not work with any version of SQL Server earlier than SQL Server 2005.

The SQL Server Custom Connection Manager sample demonstrates how to create a custom connection manager, the Sql Connection Manager, and its associated user interface.

This sample is not supported on Itanium-based operating systems.

For more information about creating a custom connection manager, see Creating a Custom Connection Manager. For general information about the use of connection managers in Integration Services, see Integration Services Connections.

This custom connection manager connects to an available instance of Microsoft SQL Server by using the SqlClient .NET Framework Data Provider, and returns an open SqlConnection object from its AcquireConnection method. When you create a new instance of the Sql Connection Manager in a package, its custom user interface prompts you for the server name and a database name. The sample connects only to a default instance of SQL Server, and uses only Windows Authentication.

This code sample implements the following functionality:

Important

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 the Sample, and read about how to configure and run the code sample

Important

Most of the tasks, sources, and destinations that have been built into Integration Services work only with specific types of built-in connection managers. Therefore, this custom sample cannot be tested with the built-in tasks and components.

Prerequisites

This sample requires that the following components are installed.

  • Microsoft Visual Studio
  • Microsoft SQL Server Integration Services

Location

If the code sample was installed to the default location, the sample is located in the following directory:

C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Programming Samples\Control Flow\SqlConnectionManager 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.

To obtain the latest version of the samples, including new samples released after the original release of SQL Server, see the Microsoft SQL Server Samples and Community Projects Web site.

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 on the Signing tab of the Project Properties dialog box to sign assemblies at build time with this key file.

To generate a strong name key file

  1. To open a Microsoft Visual Studio command prompt, click Start, point to All Programs, point to Microsoft Visual Studio 2008, point to Visual Studio Tools, and then click Visual Studio 2008 Command Prompt.

    - or -

    To open a Microsoft .NET Framework command prompt, click Start, point to All Programs, point to Microsoft .NET Framework SDK v2.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 code samples.

    Note

    To determine the folder where samples are located, click Start, point to All Programs, point to Microsoft SQL Server 2008, point to Documentation and Tutorials, and then click Samples Directory. If the default installation location was used, the samples are located in <drive>:\Program Files\Microsoft SQL Server\100\Samples.

  3. At the command prompt, run the following command to generate the key file:

    sn -k SampleKey.snk

    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.

  4. You will need the public key token from the key file in a subsequent step. To obtain the public key token, first extract the public key from the key file to a new file by running the following command at the command prompt:

    sn -p SampleKey.snk SampleKeyPublic.snk

    Now display the public key token from the new file by running the following command at the command prompt:

    sn -t SampleKeyPublic.snk

    Copy the public key token to the clipboard or save it for later use.

To build the sample in Microsoft Visual Studio 2008

  1. From the File | Open menu, click Project and open the solution file SqlConnMgrVB.sln or SqlConnMgrCS.sln.

  2. In the Solution Explorer, select the SqlConnMgrVB or SqlConnMgrCS project.

  3. Locate the DtsConnection attribute before the class declaration in the SqlConnMgrCS.cs file or the SqlConnMgrVB.vb file and replace the alphanumeric value of the Public Key Token in the UITypeName property of the attribute with the public key token obtained earlier from the key file.

  4. On the Build menu, click Build Solution.

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 in order to add it as a connection manager in a package in Business Intelligence Development Studio.

To copy the component to the Connections folder

  1. Open Windows Explorer or your preferred application for working in the file system.

  2. Copy the assemblies (SqlConnMgrCS.dll and SqlConnMgrUICS.dll, or SqlConnMgrVB.dll and SqlConnMgrUIVB.dll) to the Connections folder located at %system%\Program Files\Microsoft SQL Server\100\DTS.

To install the component into the global assembly cache (GAC) by dragging the assemblies

  1. Open Windows Explorer or your preferred application for working in the file system.

  2. Drag the assemblies from the Connections 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 commands to install the C# version of the component and the user interface into the GAC:

    gacutil.exe -iF " C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Programming Samples\Control Flow\SqlConnectionManager Sample\SqlConnMgrCS\bin\Debug\SqlConnMgrCS.dll"
gacutil.exe -iF " C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Programming Samples\Control Flow\SqlConnectionManager Sample\SqlConnMgrUICS\bin\Debug\SqlConnMgrUICS.dll"

    - or -

    Type the following commands to install the Visual Basic version of the component and the user interface into the GAC:

    gacutil.exe -iF " C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Programming Samples\Control Flow\SqlConnectionManager Sample\SqlConnMgrVB\bin\Debug\SqlConnMgrVB.dll"
gacutil.exe -iF " C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Programming Samples\Control Flow\SqlConnectionManager Sample\SqlConnMgrUIVB\bin\Debug\SqlConnMgrUIVB.dll"

Testing the Sample

You can now test the custom connection manager in a package.

Important

Most of the tasks, sources, and destinations that have been built into Integration Services work only with specific types of built-in connection managers. Therefore, this custom sample cannot be tested with the built-in tasks and components.

To use the sample connection manager in a package

  1. In the connection managers area of SSIS Designer, right-click, select New Connection, and in the list of available connection types, select SQL. Click OK.

  2. Edit the new connection manager. In the Sql Connection Manager Editor, provide the name of an available default instance of the SQL Server Database Engine and of an available database.

Change History

Updated content

Added a note that explains why this custom sample cannot be tested with the built-in tasks and components in Integration Services.