One simple thing that xlsgen can do in your Sql Server Integration Services (SSIS) package is convert files. In the SSIS package we are creating, we iterate over a folder of arbitrary Excel files and convert them as PDF files. This SSIS package could be part of a larger package where, after PDF files are created, they are sent by email to a list of known recipients.
- Start SQL Server Business Intelligence development studio.
- Select File / New / Project, pick Integration Services Project and click OK.
- From the Toolbox, drag and drop the
Foreach Loop Container item onto the main work surface :
What the Foreach Loop Container does for us is allow to iterate over a collection of objects, from files or from a database, then pass each such object name down to a data flow object for transformation purposes. What we need to do then is to configure the Foreach Loop Container, actually define the root filepath where our Excel files are stored.
- In the C:\temp folder, we have a bunch of Excel files standing there :
- Double-click on the Foreach Loop Container, click on the Collection tab on the left and make sure that in front of the Enumerator drop down, it reads
Foreach File Enumerator, which means this container will enumerate files in a folder (and sub-folders) that is going to be set right away.
- Click on the
Browse button and navigate towards C:\temp and click OK. Back in the Foreach Loop Container dialog, edit the
Files edit box so that it filters out anything other than *.xls files :
- Before we are done, we must associate a variable for passing the object identifier, in this case an Excel filepath, so it can be passed to tasks and components for processing. Click on the
Variable mappings tab on the left, then even though it is not obvious, click on the white cell below the Variable header so that a drop down appears and lets you select
New Variable....
- Click on it and then the
Add Variable dialog appears. Make sure the
Name of the variable is ExcelFilepaths and click OK.
- The variable name User::ExcelFilepaths appears in the list of variables. Click OK.
- The rest of the setup is a matter of creating a new
Script Task and making sure that this script task creates an instance of
xlsgen and then uses the User::ExcelFilepaths variable to obtain the current Excel file being processed and then exporting to a PDF file in the C:\temp folder. So in a nutshell, drag and drop a
Script Task item from the Toolbox into the Foreach Loop Container :
- Double-click on the Script task being selected. In the
Script Task Editor dialog, click on the
Script tab on the left, then edit the
ReadOnlyVariables box so that our Excel::UserFilepaths is referred to :
- Click on the
Design Script... button to enter the script editor and then replace the template source code with the following :
VB.NET code |
' Microsoft SQL Server Integration Services Script Task ' Write scripts using Microsoft Visual Basic ' The ScriptMain class is the entry point of the Script Task.
Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Imports xlsgen = Interop.xlsgen
Public Class ScriptMain
' The execution engine calls this method when the task executes. ' To access the object model, use the Dts object. Connections, variables, events, ' and logging features are available as static members of the Dts class. ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure. ' ' To open Code and Text Editor Help, press F1. ' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Dim engine As xlsgen.CoXlsEngine = New xlsgen.CoXlsEngine
Dim wbk As xlsgen.IXlsWorkbook wbk = engine.Open(Dts.Variables("User::ExcelFilepaths").Value.ToString, _ Dts.Variables("User::ExcelFilepaths").Value.ToString & ".pdf") wbk.Close()
System.Runtime.InteropServices.Marshal.ReleaseComObject(engine)
Dts.TaskResult = Dts.Results.Success End Sub
End Class
|
- Before you quit the script editor, we must let SSIS know of
xlsgen as a component. Right-click on References on the left and choose
Add Reference....
- In the
Add Reference dialog, click on Interop.xlsgen, click on the Add button and click OK. If you don't see Interop.xlsgen in the list of .NET assemblies, read our
Setup.
- As you do this, all compile errors originating from
xlsgen being unknown as a component are resolved, click on File / Save and then quit the script editor.
- Click OK which gets you back to the main SQL Server window.
- We are now ready to run. Just click on Debug / Start without debugging. The items become yellow and then green when the package execution is complete :
- Just open-up the C:\temp folder, and the PDF files are there if all went well :