SSIS: How to export Ms-Excel data into PDF?

Today while exploring SSIS, I learn a very good feature of exporting Ms-Excel data to PDF. In case our requirement is to print the Ms-Excel data into PDF and mail it to someone SSIS provides very good feature with Script Task.

For demonstration purpose let us consider we have following Ms-Excel file. We will export this excel file into PDF.

• SSIS: How to load multiple excel files into multiple SQL tables?
• SSIS: How to loop through Multiple Excel sheets and load them into a SQL Table?
• SSIS: How to resolve Excel Import 255 character Truncation issue?
• SSIS: Read and Export Excel data from nth Row
• SSIS: How to generate HyperLink in Excel output?

In SSIS designer, I have added one Script Task.

I have added one Package level variable with data type as string and store the Ms-Excel file path and file name into it.

In Script Task, I have added following VB.NET code to export the Ms-Excel file to PDF. In this code snippet, I am using reference of Microsoft.Office.Interop.Excel

The code snippet in Script Task is following:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Excel = Microsoft.Office.Interop.Excel

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum
    

    Public Sub Main()

        Dim myExcelApp As New Excel.Application
        Dim myExcelWb As Excel.Workbook
        myExcelWb = myExcelApp.Workbooks.Open(Dts.Variables("ExcelfilePath").Value, , True)
        myExcelWb.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, Dts.Variables("ExcelfilePath").Value)
        myExcelWb.Close()
        myExcelApp.Quit()

        Dts.TaskResult = ScriptResults.Success
    End Sub

End Class

After running the SSIS package, the SSIS package runs successfully and it exported the data into PDF.

So just in case you requirement is to export Ms-Excel data into PDF, you can achieve this without using any third part tool.