In my previous post, I have mentioned using custom assemblies in SSIS script components/tasks without installing them into GAC. Still I was not fully happy with the solution, since you still needed to distribute SSIS packages separately from the custom assemblies, creating a possibility for mistake (version mismatch, invalid paths, etc).

Immediate idea was to embed required DLLs into the Script project. But because SSIS package writes everything into an XML document, any binary project items were immediately causing XML errors. So if I can’t embed anything that is not a text, what should I do? The solution is somewhat hack-ish, but it works. I can take an existing DLL, encode it using base64 to get ASCII body, make it build into an embedded resource, and then, before I need to resolve the assembly reference, read the resource as a string, decode it back to binary, save to a disk, and then return a path to a newly created DLL. This way I can deploy all of my external dependencies without worrying whether they exist on the destination server or not.

For testing, I used ICSharpCode.SharpZipLib.dll. Quickly googled the online base64 encoder, and picked this one: http://www.motobit.com/util/base64-decoder-encoder.asp. Uploaded SharpZipLib DLL and saved it as ICSharpCode.SharpZipLib.dll.bin locally.

Next I created a new SSIS Script component transformation. Added a reference to local ICSharpCode.SharpZipLib.dll, created project subfolder lib, and added ICSharpCode.SharpZipLib.dll.bin to that subfolder.

Now I opened the ICSharpCode.SharpZipLib.dll.bin properties and changed build action to “Embedded Resource”. Make sure that “Copy to output directory” is set to “Do not copy”.

Next was just adding code. Here is the code I had to write/modify inside of main.cs. All of the wiring-up, etc, are omitted.

public override void Input0_ProcessInputRow(Input0Buffer Row)  
{  
 /*  
  * Add your code here  
  */  
 ProcessRow();  
}  
private void ProcessRow()  
{  
 // just anything to use the assembly  
 Directory.CreateDirectory("C:\\temp");  
 var z = ZipFile.Create("C:\\temp\\lskdlskdlsk.zip");  
 z.Close();  
 File.Delete("C:\\temp\\lskdlskdlsk.zip");  
}  
static ScriptMain()  
{  
 AppDomain.CurrentDomain.AssemblyResolve += CurrentDomain_AssemblyResolve;  
}  
static Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)  
{  
 // retrieve just a name of this assembly
 var assemblyName = args.Name.Split(',')[0];  
 // retrieve the namespace of this project so I can use it to create a subfolder 
 // in the %Temp% folder.   
 // There is no reason to create a new random folder every time the package executes.  
 var declaringType = MethodBase.GetCurrentMethod().DeclaringType;  
 var currentPath = Path.Combine(Path.GetTempPath(), declaringType.Assembly.GetName().Name);  
 Directory.CreateDirectory(currentPath);  
 try  
 {  
   var executingAssembly = Assembly.GetExecutingAssembly();  
   var resourceName =  
	 executingAssembly.GetManifestResourceNames()
		.FirstOrDefault(mr => mr.Contains(assemblyName));  
   if (!string.IsNullOrWhiteSpace(resourceName))  
   {  
	 var base64StringStream = executingAssembly.GetManifestResourceStream(resourceName);  
	 var base64String = new StreamReader(base64StringStream).ReadToEnd();  
	 var dll = Convert.FromBase64String(base64String);  
	 File.WriteAllBytes(Path.Combine(currentPath, string.Format("{0}.dll", assemblyName)), dll);  
	 return System.Reflection.Assembly.LoadFile(Path.Combine(currentPath, Path.Combine(currentPath, string.Format("{0}.dll", assemblyName))));  
   }  
 }  
 catch (Exception e)  
 {  
   File.WriteAllText(Path.Combine(currentPath, "errors.txt"), e.Message);  
   return null;  
 }  
 return null;  
}  

That’s it. Now when you execute the code, the assembly will be placed into a subfolder in the profile’s %Temp% folder and referenced from there. Of course there is a lot of room for improvement. For example, we could check if the destination dll already exists and not perform all decoding/saving stuff. Also we could add a version to our embedded file names, and overwrite only if the version in the destination folder is not the same, but in general, as a proof of concept, it works great.