Problem with login password when running a dtsx package

Question

Post reply

Problem with login password when running a dtsx package

neilnewton001

Mr or Mrs. 500

Points: 503
More actions
June 9, 2010 at 7:23 am

#140735

Hi,
Pretty new to SSIS. I tried to do a quick import from an excel file into a table in sql server. Normally this works fine but, in this case, it looks like some of the text fields in the excel file are larger than the default lengths given the destination fields when the import creates the table.
I opted to create the table myself in SSIS in a package step (with larger sizes than the defaults) and then use another step to import the excel data. I am using sql authentication because, despite a number of tries and some research I can't get windows authentication to work on my laptop (I'm also not much of an administrator :-).
Even though I add the password to the oledb connection properties and click "save password" when I run the package it looks like it's ignoring the saved password and it can't connect to the database. I've seen some articles on SSIS config files and dtexec but I'm not sure if I need to use those methods. This is a simple attempt to load a database table and will not be run from a job agent; it's a one time thing.
Any help would be appreciated.
Thanks,
Fig000

Viewing 7 posts - 1 through 6 (of 6 total)

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

It isn't supported:

http://msdn.microsoft.com/en-us/library/ms139836.aspx

Important line:

You cannot connect to a password-protected Excel file.

Sorry..

CEWII

neilnewton001 Mr or Mrs. 500 Points: 503 More actions · Answer 2

The password I'm discussing is saved in the oledb connection to the sql server database, not the connection for the excel spreadsheet. The excel spreadsheet isn't password protected. I'm using a sql server login to connect to the database.

I can add the password on the oledb properties screen and test the connection succesfully. It just doesn't seem to be there when I'm running the package even though I checked "save password".

steveb. SSC-Forever Points: 46733 More actions · Answer 3

have a look at the error log on the SQL sever, this will give the reason why the login failed.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

neilnewton001 (6/9/2010)
The password I'm discussing is saved in the oledb connection to the sql server database, not the connection for the excel spreadsheet. The excel spreadsheet isn't password protected. I'm using a sql server login to connect to the database.
I can add the password on the oledb properties screen and test the connection succesfully. It just doesn't seem to be there when I'm running the package even though I checked "save password".

Sorry, misread that..

I opted to create the table myself in SSIS in a package step (with larger sizes than the defaults) and then use another step to import the excel data. I am using sql authentication because, despite a number of tries and some research I can't get windows authentication to work on my laptop (I'm also not much of an administrator.

Hold up.. Are you using SQL authentication or trusted authentication? If you are using trusted then it will ignore password, if you are using SQL it should use it. I believe a later poster referenced looking at the SQL Errorlog, I concur, it should give you more of an indication as to WHY which I think might help.

Also, saving the password in the package itself may have some issues. The password (As well as any other "protected" information is encrypted in such a way that if any other user opens the package the password will not be available to them, they will have to rekey it and then if they save the package the protected information will be available only to them.. This is the reason that many (most?) people use package configurations in the form of tables or dtsconfig files (there are a couple others I'm not mentioning). The other useable option is to modify the connection in the SQL Agent job. Either of these options is REQUIRED if you have embedded passwords AND do not run the job as yourself, ie: your security context. Since most people run these jobs with a common account this comes up often on the forums..

CEWII

vani_r14 SSCrazy Points: 2843 More actions · Answer 5

Hi Neil

I just sent you a PM message... Let me know if you can try the option and see how it goes...

Cheers

Vani

vani_r14 SSCrazy Points: 2843 More actions · Answer 6

Hi all

I have tried this in a few packages and may help some one ...

Save the password as per normal in the Connection manager.

In the properties of the package - change Encrypt Sensitive from its default to a Encrypt sensitvie with a password and provide a password anything . Save the package.

Create another package with an Execute Package task (that executes this package) and in the execute package task add the password which you have created for the other package and test run it...

Lets say you have the package as "Main Package" which does all task - change the sensitive in here to "Encrypt Sensitive with Password" and enter a password anything.

Create another package called "Cover Package" or something like that - In this add a task "Execute Package task". In the execute package task under the package page provide the location of the Main Package and enter the password here that you set up above. Save the package. When executing execute the cover package and test run it.. This should work...

Cheers