asked on

SSIS + FTP + The password was not allowed

Hi,
I have a SSIS package that also sends files over FTP
It works great inside BIDS..

But when i copy the Package across to to production it fails with the following

Code: 0xC001602A Source: Package Connection manager "FTP Connection Manager" Description: An error occurred in the requested FTP operation. Detailed error description: The password was not allowed .

I found this on the net
http://social.technet.microsoft.com/Forums/en-US/sqlintegrationservices/thread/912d43a5-975f-48b6-a9f1-660efeacc8ed/

But could get Protection level working

Any help is appreciated

Thanks

Theepan

tigin44

check the account permission under which the sql agent service runs have access right to the ftp site? You 99% having account permission problem.

justin_smith

ASKER

i am running it ith dtexec in a batch file

nmcdermaid

I beleive the issue is with the ProtectionLevel.
Basically your FTP login is considered 'sensitive' data,
The Protection level decides how this sensitive data is stored.
By default its set to 'EncryptSensitiveWithUserKey' (you'll need to verify this for me)
'EncryptSensitiveWithUserKey' means that your FTP login is encrypted with the current user.
In BIDS, the current user is whoever is logged in
When you run a DTS via SQL Agent (which I assume you are doing), the user is the SQL Agent service account... which is different (LOCAL\System by default).... which means that your FTP login details are unencrypted incorrectly.

So assuming you are running this in SQL Agent, your two basic workarounds are:
1. Use the 'EncryptSensitiveWithPassword' protection level, enter a password, and make sure you pass this password into DTEXEC using the /P parameter (I think its /P)
2. Set the SQL Agent service account to match whoever you're logged in as in BIDS (an OK idea if you are just doing development on your machine but not if you are thinking of deploying this to an external production server)
Anyway can you verify what ProtectionLevel you are using, and whether you are using SQL Agent to scheudule (or are you just double clicking the batch file?)

justin_smith

ASKER

hi
thanks for that
it is run from the scheduled task.. that calls the bat file..

And the protection level is EncryptSensitiveWithUserK

When i change that to EncryptSensitiveWithPassword i had to pass /P.. Then it complains no user specified or something like that..

am i missing anything here

thanks

justin_smith

ASKER

this is the actual error shown
The File option cannot be specified with the DTS, SQL, Server, User, or Password
options.

Steve Hogg

Hi,
First, this is not a Package Protection Level problem. This is occuring inside the FTP task. That is after the Package has been executed and validated. So, the problem is in your FTP task, post some screen shots of your FTP configuration.
Check your local path variable or typed property. Is it a UNC mapped location or a drive letter reference, needs to be UNC.

ASKER CERTIFIED SOLUTION

nmcdermaid

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nmcdermaid

Did that fix it??