SQL and the like

Dave Ballantyne's blog. Freelance SQL Server database designer and developer at Clear Sky SQL

April 2011 - Posts

#SqlBits and SSIS custom components

If you are in anyway interested in SQL Server and live in the UK,  you cant of failed to of noticed that last weekend (07 – 09 April 2011) was SqlBits 8.  This is a truly awesome conference, and I'm honoured to of been able to present there for the second time. 

It was pleasure to be able to meet Sql Server professionals from around the UK, Europe and the world ,some for the first time and some who i have known over a number of years.  Hello to you all far to numerous to even attempt to list.

A massive “thank you” to the committee, who put in so much hard work and to the sponsors for their continued support.

I presented on SSIS Custom Components, a subject for which an hour slot is way to short given that as "data people” we are not even familiar with some of the base concepts of a .Net development.

My slides and examples are here :


and here is my “Cheat Sheet”, even if you didnt attend my session , you may find it useful to start out.  Please ,let me know how you get on.

1)      New Project (duh J)

2)      Sign with a strong name (Project properties)

3)      Advanced compiler option (Check you are targeting 3.5 Framework)

4)      Add references to:

a)      Microsoft.SqlServer.DTSPipelineWrap.dll

b)      Microsoft.SqlServer.DTSRuntimeWrap.dll

c)       Microsoft.SqlServer.ManagedDTS.dll

d)      Microsoft.SqlServer.PipelineHost.dll


These will exist into the Directory “C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies”.

5)      Paste in the code from cheat.txt and change class name and description to something appropriate

6)      Create a new “new” sub something like

Sub New()

        MessageBox.Show("Hello SQLBits8")

End Sub


7)      Override an existing member function , I would suggest OnInputPathAttached


Public Overrides Sub OnInputPathAttached(ByVal inputID As Integer)


        MessageBox.Show("Oninput attached")

 End Sub


8)      Build Project , all being well you should now have a .dll file in your Release directory

9)      Copy .DLL to C:\Program Files (x86)\Microsoft SQL Server\100\DTS\PipelineComponents

10)   In a command prompt , you will need administrative privileges , CD to the above path then “GACUTIL –I <yourdllname>.dll”.  All being will your class will now be registered

11)   Run BIDS , new project , Right click on toolbox and choose “Select Items”

12)   Select the “Pipeline Components” Tab and find and tick your component.   If you cant find it recheck the above steps, you HAVE TO start a new version of bids.

13)   Drag your new component onto the work surface ,  This will now show the message box in the “New” sub.

14)   Connect any output to your component, this will show the message box in OnInputPathAttached.

Congratulations , you have built your first custom component.