SSIS 2012 Custom Component Development Process

The Primary purpose of this article is to give a step by step process to converting existing SSIS 2008 components to SSIS 2012 components. With this process you can also create your own from scratch as well. All of this is done using Sql Server 2012 and Microsoft Visual Studio 2010.

Our first Issue that can be helpful for any project is converting a visual studio solution from 2008 to 2012. Using Visual Studio 2010 the Project was converted from Visual Studio 2008 to 2010 with no problems.

The main element of concern is the .NET version which for visual Studio 2008 was in .NET 3.5. What we want to do is convert to 4.0 to take advantage of the SQL Server 2012 Assemblies. So we go into each of the Projects in the solution and convert them to .NET 4.0.

When you change the .NET Framework from 3.5 to 4.0 you will get a warning screen asking if this is what you want to do.

Once all the project is converted this will give us ability to now grab the appropriate SQL Server 2012 DLLs that we need for the components.

If you look in the directory C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies we will notice all the DLLs that are needed to be referenced in our component design. If you notice that this directory is now 110 for SQL Server 2012 whereas the SQL Server 2008 directory was 100.

Since we converted to the .NET 4.0 Framework we will now see the references that we need to add for our component design. If you are using Visual Studio 2008 .NET 3.5 framework then you need to use the 2008 assemblies which need to be added from a SQL Server 2008 install into the C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies. These will not be there in a SQL Server 2012 environment. So it is recommended to convert to 4.0 and use the 2012 assemblies which will be there.

We now want to add references to our project by the add reference property.

What we notice at the top is a filtered to: .NET Framework 4 and that is in association with which version of the project. So it is looking for the assemblies in the C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies directory. If the project were in .NET 3.5 then it would be filtered by that and would be looking for the assemblies in the C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies directory.

Each of the projects needs to have the new assemblies from the 110 directory so we delete all the old assemblies from 2008 and add the 2012.  We can determine that its a SQL Server 2012 assembly by highlighting the dll and looking at the properties path so for our example below we have the Microsoft.SqlServer.DTSPipelineWrap we can see its path is C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.DTSPipelineWrap.dll

After each of these is converted we want to compile the entire project so that we can find any errors with the conversions.

One thing we can look to tell if the Assemblies are a current SQL Server 2012 is by checking the directory of the assembly. We are looking for the 110 directory which tells us it is a SQL Server 2012 .NET 4.0 assembly.

Once all the projects compile and the assemblies are up to date we can now register the assemblies with the GACUTIL. This demonstration was done on Windows 2008 R2 so it can be different on other operating systems so this process may need to be refined for each OS scenario. I noticed that the assemblies are not added to the C:\Windows\assemblydirectory as in previous years it adds them the specific .NET assembly folder specified below.

First thing we want to do is locate the assembly folder and the .NET GAC directory so we can register the DLLs. The .NET assembly directory is located in C:\Windows\Microsoft.NET\assembly\GAC_MSIL and is where we will start to see all the assemblies load as we register them.

Add all the DLLs to the main visual studio directory were the GACUTIL is located for ease of registering.

So we now go into the visual studio command prompt to run the GACUTIL to register our assemblies. We use the gacutil /i Mariner.Pamlico.ETL.Components.dll (the command followed by the dll which is located in the same directory)

Once the assembly is registered we will now see this in the .NET assembly directory as follows.

The final step in getting the assembly configured is to add it the DTS directory so that it will show up in the SSIS toolbox. There are 2 types of objects in the SSIS toolbox which are tasks and components of which we are concerned. The tasks are what show in the control flow and the components are what show in the dataflow. So we need to add the DLLs to these different directories. The components we will add to C:\Program Files (x86)\Microsoft SQL Server\110\DTS\PipelineComponents directory. The Tasks we will add the C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Tasks directory.

Once we register and add all the DLLs we can now open a SSIS projects and update the toolbox.

Simply enough to update the SSIS toolbox we just right click on the SSIS Toolbox and choose refresh toolbox to see the new component. (HelloWorld).

4 thoughts on “SSIS 2012 Custom Component Development Process

  1. Pingback: Convert SSIS 2008 custom components to SSIS 2012. | jessekraut

  2. Has any had problems doing this with Visual Studio 2013 and SQL 2014. I believe I have followed all the above, but VS013 SSIS toolbox does not show my component

    • There is a lot involved. Make sure for SQL Server 2014 you add the DLLs to the C:\Program Files (x86)\Microsoft SQL Server\120\ folder just like you did I did with the 2012 but a new folder for 2014. It all depends on the GAC Util. If you have done this on a development machine then you can use the Visual Studio Command Prompt and use the GAC util from there. If you have this on a window server the get the windows server SDK and use the GAC util from there.

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s