Search code examples
sql-serverpowershellssisetljobs

Export password protected xlsx file and e-mail it

Currently I'm working with SSIS package that is executing a Stored Procedure and generating an .XLSX file with the results of the query.

What I'm needing to do is to encrypt the .xlsx file. it could be done either encrypting the file after being populated with the SSIS package, or by putting a password on the .xlsx file beforehand and opening it (reading password protected file) and exporting data to it.

*I know that password protected files are not super safe, but for this case I only need it to be password protected for compliance.

I was investigating with SSIS and I believe I can do it with a powershell script that can be run using an "Execute Process Task" tool from SSIS, please correct me if I'm wrong on this.

Update: I'm executing with an "Execute Process Task" a PowerShell script (script.ps1):

Set objExcel = CreateObject(“Excel.Application”)

objExcel.Visible = True

objExcel.DisplayAlerts = FALSE

Set objWorkbook = objExcel.Workbooks.Add

Set objWorksheet = objWorkbook.Worksheets(1)


objWorksheet.Cells(1, 1).Value = Now

objWorkbook.SaveAs “C:\Scripts\Test.xlsx”,,”Password123”

objExcel.Quit

However here I don't knowhow to point to the Excel file I created with the package to password protect it, am I missing something?

this is what my package design looks in SSIS: enter image description here

And this is the detail of the "Execute Process Task" called "Lock excel file generated":

enter image description here

*This comes from source: https://techcommunity.microsoft.com/t5/SQL-Server-Integration-Services/Run-PowerShell-scripts-in-SSIS/ba-p/388340

Solution

  • You can create encrypted Excel spreadsheets in Powershell.

    You'd create a scheduled task to fire of a Powershell script, along the lines of 

    Set objExcel = CreateObject(“Excel.Application”)

objExcel.Visible = True

objExcel.DisplayAlerts = FALSE

Set objWorkbook = objExcel.Workbooks.Add

Set objWorksheet = objWorkbook.Worksheets(1)

//Whatever you do to populate the workbook


Set filename = [System.IO.Path]::GetRandomFileName()

objWorkbook.SaveAs filename,,”%reTG54w”

objExcel.Quit