Password Protect Excel Destination File

Question

Post reply

Password Protect Excel Destination File

Scott-361039

SSC Enthusiast

Points: 120
More actions
September 9, 2010 at 7:55 am

#238044

Hi
I am looking for some help with the following please:
I am passing out an excel file that will be emailed to a recipient and i would like to password protect it if at all possible.
Any ideas gratefully received.

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

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

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

2 options come to mind.

1 - Use a .NET (VB or C#) script to password the file via Excel.

2 - Password the file via a file compression program (Winzip, WinRar, etc ...) using the Exec Process Task.

The second option is the easier one.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

COZYROC One Orange Chip Points: 28499 More actions · Answer 2

Scott-361039 (9/9/2010)
Hi
I am looking for some help with the following please:
I am passing out an excel file that will be emailed to a recipient and i would like to password protect it if at all possible.
Any ideas gratefully received.

Hi Scott,

The standard Excel destination component doesn't support encryption. If you can use third-party solutions, check the commercial CozyRoc Excel Destination Plus component. It is based on CozyRoc Excel Connection manager, which supports Excel workbook encryption.

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

Scott-361039 SSC Enthusiast Points: 120 More actions · Answer 3

Thanks guys i have gone down the Winzip route and it works great.

Scott-361039 SSC Enthusiast Points: 120 More actions · Answer 4

Actually just to go back on my statement above - it works fine with Winzip2.0 encryption but i am trying to get it encrypted at AES128 or AES256 level. I have installed the CMD LINE Add On as required and the entry i have in the execute Process Arguments Expression is:

"-min -a -spa55w0rd123" + " -ex U:\\Test\\OutputNew-" + @[User::FilePath] + ".zip U:\\Test\\OutputNew-" + @[User::FilePath] + ".xls"

and it works fim and evaluates as the following:

-min -a -spa55w0rd123 -ex U:\Test\OutputNew-001.zip U:\Test\OutputNew-001.xls

According to the CMD LINE Add Onsupport text file the AES encryption level should be appended after the password as follows:

-spassword

Zip 2.0 encryption and a password of password.

-spassword -yc

AES 128 (default method for -yc option) encryption and give all files in test.zip a password of password.

-spassword -ycAES

AES 128 encryption and give all files in test.zip a password of password.

-spassword -ycAES128

AES 128 encryption and give all files in test.zip a password of password.

-spassword -ycAES256

AES 256 encryption and give all files in test.zip a password of password.

I have tried all these options but with no luck. Each time i try i am placing the above entry in italic into the same space below in italics

"-min -a -spa55w0rd123" + " -ycAES128" + " -ex U:\\Test\\OutputNew-" + @[User::FilePath] + ".zip U:\\Test\\OutputNew-" + @[User::FilePath] + ".xls"

this evaluates as

-min -a -spa55w0rd123 -ycAES128 -ex U:\Test\OutputNew-TestUpload_001_070910212735.zip U:\Test\OutputNew-TestUpload_001_070910212735.xls

the error i get from winzip during execution is a pop up box with a hazard symbol and "WinZip Parameter Validation Error"