Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SSIS using source of password-protected Excel Files Expand / Collapse
Author
Message
Posted Thursday, March 18, 2010 12:28 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 17, 2014 9:04 AM
Points: 2,075, Visits: 207
I need to import data from an Excel file into a SQL 2005 DB and thought I would use SSIS since this is a pretty straight forward task for SSIS. However, I just learned that the Excel file is password-protected and I have not found anything online that mentions how to do this since there is no way to distinguish this in the Connection to the Excel file.
I do know the password to open the file but just don't know how to automate this. Any ideas?
Post #885800
Posted Tuesday, March 23, 2010 3:34 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
You can't..

http://msdn.microsoft.com/en-us/library/ms139836.aspx
Important text in the article:
You cannot connect to a password-protected Excel file.

CEWII
Post #888553
Posted Wednesday, March 24, 2010 7:35 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 17, 2014 9:04 AM
Points: 2,075, Visits: 207
Shoot! Anyway to programatically remove the password protection so that it can be used in SSIS?
Post #888961
Posted Wednesday, March 24, 2010 9:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
Maybe, but unfortunately I don't have any information on that.

CEWII
Post #889014
Posted Thursday, March 25, 2010 8:23 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 4, 2014 7:46 AM
Points: 566, Visits: 1,596
My first thought was that you may be able to use Poweshell. A quick look around and I found this:

http://www.eggheadcafe.com/software/aspnet/34092087/can-i-open-a-password-pro.aspx


-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible.
Post #889852
Posted Thursday, March 25, 2010 9:07 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 17, 2014 9:04 AM
Points: 2,075, Visits: 207
Great, I was able to open the Excel file using Powershell, thanks! Since I'm very new to Powershell, is there a way to save the Excel file without the password now?
Here's my thoughts. Open the Excel file and save it without the password. Then use SSIS to reference this newly saved file as a source for data flow to get around the limitation of not being able to open it in SSIS with a password.
Any thouhts?
Thanks!
Post #889898
Posted Thursday, March 25, 2010 9:31 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 4, 2014 7:46 AM
Points: 566, Visits: 1,596
Exactly that. Set password to empty ("") and SaveAs. Then the connection manager shouldn't complain.

$comments = @'
Script name: Remove-Password.ps1
Created on: Tuesday, July 03, 2007
Author: Kent Finkle
Purpose: How can I use Windows Powershell to
Remove the Password When Opening an Excel Spreadsheet?
'@
#-----------------------------------------------------
function Release-Ref ($ref) {
([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
[System.__ComObject]$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
}
#-----------------------------------------------------
$xl = new-object -comobject excel.application

$xl.Visible = $True

$xl.DisplayAlerts = $False

$wb = $xl.Workbooks.Open("C:\Scripts\Test.xls",0,$False,1,"%reTG54w")

$wb.Password = ""

$a = $wb.SaveAs( "C:\Scripts\Test.xls")

$a = Release-Ref($wb)
$a = Release-Ref($xl)



-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible.
Post #889927
Posted Thursday, March 25, 2010 9:51 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 17, 2014 9:04 AM
Points: 2,075, Visits: 207
I try this and I'm getting the following error on the saveas line:
Exception calling "SaveAs" with "1" arguments(s): "Cannot access read-only document

I have "$False" for the readonly parameter on the open method so I don't understand why I'm getting this error.
Here's the Open statement
$wb = $xl.Workbooks.Open(<file>,1,$False,5,"abc123","abc123")
Here's the SaveAs statement
$a = $wb.SaveAs(<file>)

Thoughts?
Post #889943
Posted Thursday, March 25, 2010 10:12 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 17, 2014 9:04 AM
Points: 2,075, Visits: 207
Nevermind, I was able to get this to work. Thanks for your insight on this!
Post #889971
Posted Wednesday, June 9, 2010 9:26 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 5:27 PM
Points: 1,117, Visits: 2,219
aber (3/18/2010)
I need to import data from an Excel file into a SQL 2005 DB and thought I would use SSIS since this is a pretty straight forward task for SSIS. However, I just learned that the Excel file is password-protected and I have not found anything online that mentions how to do this since there is no way to distinguish this in the Connection to the Excel file.
I do know the password to open the file but just don't know how to automate this. Any ideas?


If you can use third-party solutions, check the commercial CozyRoc Excel components. These are the relevant components:

* Excel Source component - for reading data from Excel worksheet.
* Excel Destination component - for writing data in Excel worksheet.
* Excel Task - for manipulating Excel workbooks.
* Excel Connection - used by the components above and also for implementing custom scripts based on it.

The Excel Connection supports opening of password-protected workbooks and doesn't require installation of Office. Cheers!


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

Post #935112
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse