In one of my previous posts SSIS 2008 Columns To XML Data Flow Transformation I have presented a custom SSIS Data Flow Transformation.
As mentioned in the post, to be able to use the component, it has to be put into
PipelineComponents folder of the SSIS Installation (For SQL Server 2008 common location is
C:Program FilesMicrosoft SQL Server100DTSPipelineComponents) and also it needs to be installed into GAC(Global Assembly Cache). The location in the
PipelineComponents is important for the BIDS designer and the GAC installation is important for SSIS execution.
Placing the component assembly into the
PipelineComponents folder is very straightforward as you simply copy/past the assembly .dll file into this directory.
On the other side, installation into the GAC can be a little bit tricky and depend on the OS and .Net Framework version for which the assembly is built.
Components for SQL Server 2005 and 2008 (.Net < 4.0)
If the component is build for SQL Server 2005 or 2008, then yo use .Net framework lower than 4.0 and the installation relatively easy.
Installation on Windows Server 2003 and Windows XP
Installation of the .Net 2.0 or .Net 3.5 assemblies on the Windows Server or Windows XP is relatively easy as the GAC is located in
C:Windowsassembly directory and can be easily managed by the windows Explorer. Simply navigate into this directory and Drag-Drop the assembly .dll file(s) here.
If you need to uninstall the assembly, simply find your assembly in the GAC, right-click on it and choose Uninstall. You will need to uninstall the assembly if you would like to deploy an updated version (eg. because of bug fix or for whatever reason). In that case you have to uninstall such assembly first and the install it again.
Installation on Windows Server 2008, Vista ans Windows 7
Installation of assembly into GAC on the Windows Server 2008, Vista and Windows 7 can be a little bit trickier. However the assemtrickierbly has the same location as on Windows Server 2003 and Windows XP (
C:Windowsassembly), those systems are missing the explorer integration for Installation purposes (you cannot Drag-Drop assemblies into that directory).
For that purpose you can use the
gacutil.exe but this is not part of the standard installation. You have to install the Windows SDK. Once installed, then this utility is located in
C:Program Files (x86)Microsoft SDKsWindowsv7.0ABin directory. You do not need to install the SDK on all the machines where you need to deploy the assembly. It is enough to copy the
gacutil.exe.config files to the target machines and use it to install/un-install the assembly. You have to execute it from command prompt with administrative privileges, so in case of UAC enabled, you have to run the command prompt AS Administrator.
To install use the
gacutil.exe with the
gacutil.exe /i "C:Program FilesMicrosoft SQL Server100DTSTE.SSIS.DataFlow.dll"
To list all the assemblies in GAC you can use
Eventually you can put assembly name filter after the
/l switch (it will list all installed versions of the assembly, as GAC support multiple versions of the same assembly to be installed at the same time):
gacutil.exe /l TE.SSIS.DataFlow
To uninstall an assembly from GAC use the
gacutil.exe /u TE.SSIS.DataFlow
Components for SQL Server 2012 (.Net 4.0) and .Net 4.0 GAC Structure
With SQL Server 2012 the things start to be a little more tricky than with previous versions as components needs to be compiled under .Net framework 4.0.
In .Net 4.0 the GAC location has been changed and is located under
As we can see, the structure has changed and there are separate directories for each platform. When building the components for SSIS we build them for MSIL (as it is platform independed and allow us to run the packages in both x32 and x64 runtime). In fact the structure is the same as for
c:windowsassembly, but for the
c:windowsassembly it is hidden in explorer. If you list the
c:windowsassembly directory by
dir command, you should see the same three directories on x64 system.
Each assembly has another directory in the GAC corresponding to the assembly name and under this directory there are directories for each version of the assembly and are named using the assembly strong name. Then under this strong name directory are located the assembly .dlls
Installing .Net 4.0 assembly into GAC
As mentioned above, the GAC location and structure has changed in .Net 4.0. Also it is not possible to use the tools for .Net 3.5 and lower (I men gacutil.exe for older framework) and for security reason, you cannot also use simply Copy-Paste into GAC directory as it was for .Net < 4.0 on Server 2003 and Windows XP.
Again in you install Windows SDK and go through the directory structure, you will find tools for .Net 4.0 under the
C:Program Files (x86)Microsoft SDKsWindowsv7.0ABinNETFX 4.0 Tools
In this directory you will find also the
gacutil.exe for .Net 4.0. I was able to use it and install the assembly into GAC without problems on Windows 7 in the same way as it was mentioned above.
But when I try to copy it and use it on Windows Server 2008, I was not able to get the
gacutil.exe for .net 40 working on it.
gacutil.exe only prints a copyright information and that’s all.
PowerShell the ultimate GAC installation solution
As I was not able to install the assemblies using the
gacutil.exe I was starting to search for alternate solution of GAC installation and found the PowerShell.
You are able to install the assemblies to GAC on all above mentioned systems using a simple PowerShell Script. For this purposes PowerShell will utilize
System.EnterpriseServices.Internal.Publishh class which is able to install and uninstall the assemblies from GAC.
Only note to the
Publish class. When you use it and make some typo or something, the
GacRemove methods do not print any error message. So you have to be careful when typing the paths etc. To verify that the assembly was successfully installed /uninstalled to/ from GAC, simply browse to the GAC directory using Explorer and search for your assembly.
Install script we will use
GacInstall method of the
#Load System.EnterpriseServices assembly as it contain classes to handle GAC [Reflection.Assembly]::LoadWithPartialName("System.EnterpriseServices") #Create instance of Publish class which can handle GAC Installation and/or removal [System.EnterpriseServices.Internal.Publish] $publish = new-object System.EnterpriseServices.Internal.Publish; #Install dll into GAC using GacInstall method (Provide full path to the assembly) $publish.GacInstall("C:Program FilesMicrosoft SQL Server110DTSTE.SSIS.DataFlow.dlll");
To uninstall an assembly you can use the
GacRemove method of the
Publish class. When uninstalling, you have to provide the complete path to the assembly dll.
#Remove from GAC using GacRemove method (Provide full path to the assembly in GAC) $publish.GacRemove("C:WindowsMicrosoft.NETassemblyGAC_MSILTE.SSIS.DataFlowv4.0_126.96.36.199__0eedace497e8d607TE.SSIS.DataFlow.dll")
You can save the script into a script file e.g.
GACInstall.ps1 and execute it from command line:
powershell.exe -noprofile -executionpolicy RemoteSigned -file c:scriptsGACInstall.ps1
Alternatively you can run the PowerShell console and run the command by command. Again PowerShell needs to be executed with Administrative privileges so take UAC in mind.
If you would like to use the PowerShell ISE (Integrated Scripting Environment) and execute the script form there, don’t forget set the Execution policy appropriately:
#Enable Local not signed Script Execution Set-ExecutionPolicy RemoteSigned;
If you want to use the ISE also on Windows Server 2008, you have to enable that feature on the server first, as the PowerShell ISE is not installed and enabled by default on Windows Server 2008. So go to Server Manager, choose Add Features and add the PowerShell ISE Feature.
Using the PowerShell, you are independed of any tools like
gacutil.exe etc. and you are able to install whatever assembly on whatever system which has PowerShell available.
Hope this post will help you saving troubles when deploying custom SSIS packages to server.