Wednesday, April 17, 2013

Adding a custom dataflow control to SSIS 2008 and 2012 (Visual Studio BIDS 2008 and 2010)

So recently i wanted to create a custom SSIS data source that could use an Azure Table storage as the data source. i found an excellent (possibly the only) implementation of this here:

http://www.software-architects.com/devblog/2010/11/12/Custom-SSIS-Data-Source-For-Loading-Azure-Tables-Into-SQL-Server

The biggest problem I ran in to, being a SSIS NOOB, was getting this custom source to BIDS (Business Intelligence Development Studio)

I was using both SSIS 2008 and 2012. which meands BIDS 2008 and 2010 respectively.

Here's a copy of the thread on this here for convenience (thread already covers all the keywords which i don't want to type :))


QUESTION:


I've created a custom data source using the instructions given in the following link:http://msdn.microsoft.com/en-us/library/ms136088.aspx
However once i build the project and try to add the new datasource to the toolbox, i get the following error:
There are no components in 'C:\Somepath\bin\Debug\CustomDataSource.dll' that can be placed on the toolbox
following are the tools i've used:
To create and build the custom datasource; Visual Studio 2010 & build in Framework 3.5
To create the SSIS package SSIS - SQL Server 2008 R2 & SQL Server Business Intelligence Development Studio (VS2008)

ANSWER:

i found the answer to my problem; here's the solution and hope it helps someone.
  1. copy the dll to the following folder(s). the exact folder depends on whether your on x64 or x86 (doesn't hurt to have it on both):
    C:\Program Files (x86)\Microsoft SQL Server\100\DTS\PipelineComponents
    C:\Program Files\Microsoft SQL Server\100\DTS\PipelineComponents
  2. Install the dll to your GAC. you will need to have it signed to do this.
  3. In BI Dev Studio, go to Tools > Choose Toolbox Items. you will find your data source in the "SSIS Dataflow Items" tab
For steps 1 & 2 you might find it helpful to have a post build event like follows:
"C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\bin\NETFX 4.0 Tools\gacutil.exe" -u $(TargetName) 
"C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\bin\NETFX 4.0 Tools\gacutil.exe" -iF $(TargetFileName) 
copy $(TargetPath) "C:\Program Files\Microsoft SQL Server\100\DTS\PipelineComponents" 
copy $(TargetPath) "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\PipelineComponents"

If you're using SSIS 2012 with BIDS 2010, the toolbox items get added automatically (i.e. u don't have to go to Tools > Choose Toolbox Items) you just need to copy it to the dll right places (ref - http://fendy-huang.blogspot.com/2011/09/how-to-add-ssis-custom-component-to.html):
C:\Program Files\Microsoft SQL Server\110\DTS\PipelineComponents   
C:\Program Files (x86)\Microsoft SQL Server\110\DTS\PipelineComponents   
C:\Program Files\Microsoft SQL Server\110\DTS\Tasks   
C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Tasks

No comments:

Post a Comment