Post reply

Can SSIS Excel output be encrypted without Office installed?

  • December 3, 2010 at 3:02 pm

    #232145

    I have a client request to return a password protected Excel file from an automated ETL process.

    Does anyone know of a way to password protect an Excel file when Office is not installed? We don't install it on our production servers.

    I can do this with the following script task from my development box, because I have Office installed:

    Dim xlApp As Object

    Dim xlWB As Object

    xlApp = CreateObject("Excel.Application")

    xlApp.Visible = False

    Dim sSourceFile As String

    sSourceFile = Variables.ExcelFileName

    xlWB = xlApp.Workbooks.Open(sSourceFile)

    xlWB.Password = Variables.EncryptionPassword

    xlWB.SetPasswordEncryptionOptions( _

    "Microsoft RSA SChannel Cryptographic Provider", _

    "RC4", 128, True)

    xlWB.Save()

    xlWB.Close()

    xlWB = Nothing

    xlApp.Quit()

    xlApp = Nothing

    Dts.TaskResult = ScriptResults.Success

  • December 16, 2011 at 8:37 pm

    #1422781

    This reply is a one-year late, but still worth knowing. If you can use third-party solutions, check the commercial CozyRoc Excel components. These are the relevant components:

    * Excel Source component - for reading data from Excel worksheet.

    * Excel Destination component - for writing data in Excel worksheet.

    * Excel Task - for manipulating Excel workbooks.

    * Excel Connection - used by the components above and also for implementing custom scripts based on it.

    The enhanced adapters provides ability to add/remove Excel encryption password.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply