How to Encrypt the password to Excel using SSIS package? Is it possible?

Question

How to Encrypt the password to Excel using SSIS package?

I thought, we can use script task to achieve this. I tried the below code by using the Spire.xls nugget package but the respective nugget dll reference are not adding to the solution.

Script task Code:

using Spire.Xls;

namespace ProtectExcel
{
    class Program
    {
        static void Main(string[] args)
        {
            //Load Workbook
            Workbook book = new Workbook();
         book.LoadFromFile(@"C:\Test\Test.xlsx");
            //Protect Workbook
            book.Protect("vinay-123");
            //Save and Launch
            book.SaveToFile(@"C:\Test\ProtectExcel.xlsx", ExcelVersion.Version2010);
            //System.Diagnostics.Process.Start("ProtectExcel.xlsx");
        }
    }
}

I tried the same code in c# console application it worked well but not working in SSIS script task.

Can anyone help me on this how to fix this or any other approach??

Script task Code:

using Spire.Xls;

namespace ProtectExcel
{
    class Program
    {
        static void Main(string[] args)
        {
            //Load Workbook
            Workbook book = new Workbook();
         book.LoadFromFile(@"C:\Test\Test.xlsx");
            //Protect Workbook
            book.Protect("vinay-123");
            //Save and Launch
            book.SaveToFile(@"C:\Test\ProtectExcel.xlsx", ExcelVersion.Version2010);
            //System.Diagnostics.Process.Start("ProtectExcel.xlsx");
        }
    }
}

Excel should be protected with password by using SSIS.

Is the third party dll deployed to GAC ? – Venkataraman R Apr 23 '19 at 09:29 — Venkataraman R, Apr 23 '19 at 09:29

score 0 · Answer 1 · answered Apr 23 '19 at 09:39

0

In SSIS, the dll has to be deployed to GAC to refer in the Script task code. Refer to the post on deploying to GAC for SSIS script task reference. For workaround on avoiding deploying to GAC, refer here

I would suggest you to generate exe out of the console application & use Execute Process task to call the console application with parameters, as explained here

answered Apr 23 '19 at 09:39

Venkataraman R

12,181
2
31
58

I deployed respective dlls to GAC, its working. Thanks Venkat. – T.Vinay Kumar Reddy Apr 25 '19 at 06:11
Hi Vinay, accept this as answer. so, it will be helpful for others in future to understand. Glad that it helped. – Venkataraman R Apr 25 '19 at 06:12

How to Encrypt the password to Excel using SSIS package? Is it possible?

1 Answers1