0

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

Baldie47
  • 1,148
  • 5
  • 16
  • 45

1 Answers1

1

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
Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52
  • and this can be done to create a file from a select of a table for example? like, in the "//Whatever you do to populate the workbook" how would I populate it with the information of a table? and all this would have to be set as powershell in the job configuration, right? – Baldie47 Aug 19 '19 at 10:21
  • Yes, for instance https://stackoverflow.com/questions/45758859/sql-extract-data-to-excel-using-powershell – Neville Kuyt Aug 19 '19 at 11:12
  • Password protected documents are **NOT** encrypted in any way, shape or form. Anyone with a decent Zip program can edit them to delete the or XML elements to un-password protect them. – AlwaysLearning Aug 21 '19 at 09:46
  • @NevilleKuyt Can this be used to pick up a file from a folder and password protect it? I'm asking as I'm now integrating the solution with SSIS and I' not being able to entire do it, I was thinking adding a powershell script in SSIS might be able, but I'll need to pick up a previously generated xlsx file, and via Powershell ecnrypt it. is this correct? – Baldie47 Aug 21 '19 at 10:07
  • There is something I'm not getting now, I'm executing a task for the powershell script, however I don't see where the call for my excel file is, I mean, how do I point to the excel file I want to password protect? – Baldie47 Aug 21 '19 at 12:18