Using C1Zip in an SSIS Script Task

A customer question bubbled up to Steve Basl and I. This particular question regarded the use of ComponentOne's Zip and PDF components in SSIS Script Tasks. Fortunately, it's not that hard to use either component in SSIS, but the steps vary slightly, so I'll have two separate blog posts. Here's how to use the Zip component:

Prerequisites (the hard part)

SQL Server 2005 supports only the .NET CLR 2.0, while SQL Server 2008 and 2008 R2 support versions 2.0. 3.0 and 3.5 (ref 3). Our Zip component is targeted to either the 2.0 CLR or the 4.0 CLR, so the only option we have in SSIS is the version for the 2.0 CLR. I f you have a studio version for the 4.0 CLR, contact your salesperson-our licenses are retrograde, and they can provide you with a key for the 2.0 studio.

In order to call .NET assemblies from an SSIS Script Task, we have to load them into the CLR, using gacutil.exe. You need to do this on both the development machine and the server on which SSIS runs. Your .NET version matters here, since the install path differs. You can find gacutil.exe in the following locations (note that gacutil.exe is part of the Windows SDK, not the .NET SDK, for VS 2008/2010):

  1. Visual Studio 2005 = C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\ (ref 1)
  2. Visual Studio 2008 = C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\ (ref 1)
  3. Visual Studio 2010 .NET 3.5 = C:\Program Files\Microsoft SDKs\Windows\v7.0A\bin\ (ref 1)
  4. Visual Studio 2010 .NET 4.0 = C:\Program Files\Microsoft SDKs\Windows\v7.0A\bin\NETFX 4.0 Tools (ref 2)

If you're using Windows 7, you'll need to run gacutil.exe in a command prompt run as Administrator. You'll use a command similar to this, making path changes based on the SDK and DLL version:

gacutil.exe /i "C:\Program Files (x86)\ComponentOne\Studio for WinForms\bin\v2\c1.c1zip.2.dll"

image

If the assembly is successfully added to the GAC, you're set for the next part.

Using the ZIP component (the easy part)

  1. Start BIDS, and drag a Script Task onto the canvas.
  2. Double-click the Script Task, and click the Edit Script button.
  3. Expand the Project Explorer, right-click on References, and choose Add Reference.
  4. On the .NET tab, find ComponentOne C1Zip
    SS-2010-10-24_20.50.15
  5. Click OK, and you should now see a reference to C1.C1Zip.2 in the References list.
    SS-2010-10-24_20.52.28
  6. Change the Main() method to read as follows:

_public void Main()
{
// TODO: Add your code here 

 C1.C1Zip.C1ZipFile zip = new C1.C1Zip.C1ZipFile();  
 zip.Create(@"c:\\temp\\test.zip");  
 zip.Entries.Add(@"c:\\temp\\test.txt");  

Dts.TaskResult = (int)ScriptResults.Success;

}_

Make sure you change the file paths to be something on your machine (or create c:\temp\ and add test.txt to it). You should be set to go-run the package and make sure it works. You can deploy the SSIS package to either MSDB or file system.

References

(1) http://gthill.blogspot.com/2010/01/visual-studio-wheres-my-gacutilexe.html

(2) http://community.dotnetwork.it/Excentric/archive/2010/06/18/this-assembly-is-built-by-a-runtime-newer-than-the.aspx

(3) http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/73b1a2af-d35b-4cdf-81b2-a1020ffbc696

MESCIUS inc.

comments powered by Disqus