Using C1PDF in an SSIS Script Task
This is the second half of a customer's question regarding 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. I do need to day this is not an officially supported usage, so support won't have too many answers. Having said that, you can make this work. Here's how to use the PDF component:
Prerequisites (the hard part)
Before we get too far, I hope no one is using this technique to generate any sort of reports. We have actual reporting controls and a report designer which can output PDFs and are much more suited for data display than the PDF component. I'm not sure why our customer wanted to manipulate PDFs is SSIS, but they did.
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 PDF 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):
- Visual Studio 2005 = C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\ (ref 1)
- Visual Studio 2008 = C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\ (ref 1)
- Visual Studio 2010 .NET 3.5 = C:\Program Files\Microsoft SDKs\Windows\v7.0A\bin\ (ref 1)
- 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.c1pdf.2.dll"
Success looks like the output below.
Once the assembly is listed in the GAC, we need to generate a license file. The BIDS Script Editor cannot generate a license file, but the project won't work correctly without the license file. We apologize for the extra step, but BIDS wasn't designed for additions to its toolbox, so it doesn't know what to do when it can't find a license file.
To create a proper license file, go into a regular version of Visual Studio, create a new Windows Forms Application for the 2.0 Framework, drag a C1PDFDocument onto the form designer, and compile the project. In the My Project folder, you'll find a licenses.licx file (see below). This is the file we need.
We're now ready to create our script task.
Using the PDF Component (the easy part)
- Open BIDS, and add a Script Task to the canvas.
- Double-click to open the Script Task Editor, then click the Edit Script button.
- In the code editor, expand the Project Explorer. Right-click on the project name (it will start with "st_", followed by a guid). Choose Add >> Existing Item. The existing item should be the licenses.licx file we created above.
- The license file will be added to the project, so now we have to drag-and-drop it into the Properties folder.
- Now we need to add a Reference to the C1.C1Pdf.2 library. Make sure to do this from the .NET tab, which reads fro the GAC, rather than using a local DLL file.
- You're set, you can now test your task by adding the following code and running the package. Make sure to change any paths as necessary.
public void Main()
{_ // TODO: Add your code here
C1.C1Pdf.C1PdfDocument pdf = new C1.C1Pdf.C1PdfDocument();__ RectangleF rect = pdf.PageRectangle;
rect.Inflate(-72, -72);
Font font = new Font("Arial", 12);
pdf.DrawString("Hello World!", font, Brushes.Black, rect);__ pdf.Save(@"c:\temp\hello world.pdf");_
_ Dts.TaskResult = (int)ScriptResults.Success;
}_
GrapeCity