SSIS with Excel sheets

Question

I have created ssis package to loda data from excel to database. My problem is Those excel files proteced with password( I know the password) at the minute i am removing password mannually. Is there any way to automate in ssis, Because i have 200 excel files.

Answer 1

Vinnies answer gave me another idea - what if you would use an apdater like excel file.

Opening protected-files with excel (vba) is easy, and then, you could import your files all to this workbook or create copies of them, that are unprotected or you might even copy the data directly into ssis/sql-server.

However, this would be my suggestion. Use excel to create a new source of your input-data, then use whatever to import this new data.

Answer 2

According to MSDN, "You cannot connect to a password-protected Excel file."

http://msdn.microsoft.com/en-us/library/ms139836(v=sql.105).aspx

There may be a way to access it through custom script where you read out what's in the excel sheet to a csv to be processed. Or maybe into an object. But, I haven't found anything to help yet.

Here is a forum post from SQL Server Central that might help. It has a powershell script sample (login required)

http://www.sqlservercentral.com/Forums/Topic885800-148-1.aspx

No matter what, I think you're going to have to get the data out of the protected excel sheet before you can process it.

UPDATE: CozyRoc sells a connection manager that will let you use password protected excel sheets as connections.

http://www.cozyroc.com/ssis/excel-connection

Answer 3

This may or may not work based on your company's security policy (or monitoring thereof), but you could write a quick macro that opens all 200 files and saves them in a temp "unprotected" directory without a password. Then run your SSIS script, and when that completes, delete all 200 files (with a secure deleter such as Eraser)

Sub ManagePWords()

Dim f(1 To 200) As Variant
Dim i As Integer
Dim origpath As String, temppath As String
Dim wb As Excel.Workbook

   origpath = "c:\where_files_are_now\"
   temppath = "c:\where_files_are_now\unprotected\"

   f(1) = Array("filename1", "password1")
   f(2) = Array("filename2", "password2")
   'keep going for all 200 files

   For i = 1 To UBound(f)
      Set wb = Application.Workbooks.Open(origpath & f(i)(0), , , , f(i)(1))
      wb.SaveAs temppath & f(i)(0) & ".xlsx", , ""
      wb.Close
   Next i

End Sub