Custom Control Guidelines


Creating Custom SSIS Components for SSIS

Create a new Class Library in Visual Studio (C#)

Make sure you have all the appropriate references and assemblies to create a SSIS Component

C# References
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

Assemblies
Microsoft.SqlServer.Dts.Design
Microsoft.SqlServer.DTSPipelineWrap
Microsoft.SQLServer.DTSRuntimeWrap
Microsoft.SQLServer.ManagedDTS
Microsoft.SqlServer.PipelineHost

The Attribute and base class need to be defined in the class

namespace Converter

{

[DtsPipelineComponent(DisplayName = “Converter”,ComponentType = ComponentType.Transform)]

public class ZonedDecimalConverter : PipelineComponent

{}

}

Add the Run time Methods, Design Time Methods, and the Component Methods (More Documentation on this Later)

We need to Sign the Assembly – In visual studio go to Projects->{Project}->Properties->Signing. Choose a strong name key file (new). (add picture)

Once the Class is complete we want to make sure the class compiles and the build is created. The Final .DLL will be in projects bin/debug file or where ever you specify. (Ex: \\Project\bin\debug\converter.dll

Once the class is ready we need create the assembly. We want to use the visual studio 2008 command prompt. Add the converter.dll file to the following location(C:\program files\Microsoft Visual Studion 9.0\vc).

run the following in the command prompt:

“C:\Program Files\Microsoft Visual Studio 9.0\VC\gacutil.exe” -u
converter.dll

“C:\Program Files\Microsoft Visual Studio 9.0\VC\gacutil.exe” -iF
converter.dll

Note: If you look in the C:\Windows\Assembly folder you should see the assembly you created called converter.

Copy the converter.dll into the pipelinecomponents directory for SQL Server 2008. The path is generally (C:\program files\SQL Server 2008\100\DTS\PipelineComponents\)

Open BIDS to a Integration Services Project and add the new dataflow component to the toolbox. Open a dataflow and go to Tools->Choose toolbox items->go to SSIS Data flow Items. Search for the item you want to add and close the window. Check the toolbox to see if the new component was added.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s