Welding in custom assemblies in SSIS with configuration variables.
(I will pop a GAC in your CACHE !!!!)
Pre-requisites
SQL Server 2005 (including SSIS), Microsoft Visual Studio 2005 / 2008, .NET Framework v2.0-3.5
Prep the Assembly
In order to register the assembly in the Global Assembly Cache (GAC), it is necessary that the assembly has a Strong Name. There is a utility called sn.exe that can be used for this purpose. However, for this project, navigate to the Signing tab, select Sign the assembly option, and select <New> from the drop-down list. Type a name for the strong name key file. Finally, uncheck the option to protect my key file with a password. Click OK and save the project file. Build the file.The final step is to register the component in the GAC. The simplest way is to copy your assembly into the C:\WINDOWS\assembly folder by dragging and dropping. The other method involves creating bat files or using the command prompt to register your component.
Adding a reference to the Assembly in SSIS script designer
Intuitively one would think your next step is to open your SSIS script editor and within your project explorer simply add a reference to your newly GAC’ed assembly. However when you right click on References -> Add Reference and browse the list of available assemblies - yours is not there.I will not spend time trying to explain the intricacies of why this is, instead I will focus on how to get your .dll registered so you can get up and working. The bottom line is your going to have to modify the Package.dtsx manually edit the *.dtsx script file to add additional paths to look for your defined assemblies. For example: Open the file and first search for “<Build>”.
Add yours to the end of the ReferencePath attribute.
<Build>
ReferencePath = "C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.ScriptTask\9.0.242.0__89845dcd8080cc91\;
C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.ManagedDTS\9.0.242.0__89845dcd8080cc91\;
C:\WINDOWS\assembly\GAC_MSIL\ myAssembly \1.0.0.0__5d6caf3fc6396ac3\"
Then you can define you assemblies in the References Tags a little further down:
<References>
<Reference Name=" myAssembly " AssemblyName=" myAssembly " />
*Next time you fire up the script in Visual Studio of Applications it will be referencing Next Search for “<References>”.
<References>
<Reference Name = "myAssembly" AssemblyName = " myAssembly "/>
</References>
Using a configuration varibles.
When you are using a .NET assembly that reference external config variables, the hosting executable “owns” the App.config file. Therefore in this instance, SSIS has two executables that it uses one for the development and debugging and one for the production release. To see these files browse to:
C:\Program Files\Microsoft SQL Server\90\DTS\Binn
If you’re going to use configuration variables you need to add them to these two .config files: DTExec.exe – The main .exe SSIS uses for production release.
DTExec.exe.config
DtsDebugHost.exe – The .exe SSIS uses when developing and debugging.
DtsDebugHost.exe.config
<configuration>
<startup>
<requiredRuntime version="v2.0.50727"/>
</startup>
<appSettings>
<add key="Test1" value="test1" />
<add key="Test2" value="test2" />
</appSettings>
</configuration>