SSIS FTP Not Passing Password

Question

SSIS FTP Not Passing Password

Jolly Ollie

SSC Journeyman

Points: 99
More actions
January 14, 2009 at 6:42 pm

#129466

I must have missed a setting. I have an SSIS SQL 2005 package written in the Visual Studio that first outputs an ASCII file, sets up an FTP connection to a remote server, then sends the ASCII file to the remote server. Nothing major but it does not work and I must have missed something regarding the FTP Password. When the job runs in SQL Agent the file is exported as and ASCII file just fine but the file is not passed to the remote server. I checked the remote server and I see where the correct Logon was used but the password was rejected so the FTP file does not work. When I run this file in Visual Studio in debug mode the output and FTP runs without a hitch. But the FTP Password fails every time when it runs in SQL Agent. Have I missed a setting on the Password? Is there something special with the password before it's left to run in SQL Agent? Maybe the password is being sent encrypted or something? I have not had time to put WireShark on it to capture the packets but that's my next move if someone here can't tell me what's going on so if that happens I will post the results and maybe someone will benefit from my agony.
You folks are awesome when it comes to help. I am very impressed! It's great to see folks who are willing to help. Bryan

Viewing 4 posts - 1 through 3 (of 3 total)

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

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

Jolly Ollie (1/14/2009)
I must have missed a setting. I have an SSIS SQL 2005 package written in the Visual Studio that first outputs an ASCII file, sets up an FTP connection to a remote server, then sends the ASCII file to the remote server. Nothing major but it does not work and I must have missed something regarding the FTP Password. When the job runs in SQL Agent the file is exported as and ASCII file just fine but the file is not passed to the remote server. I checked the remote server and I see where the correct Logon was used but the password was rejected so the FTP file does not work. When I run this file in Visual Studio in debug mode the output and FTP runs without a hitch. But the FTP Password fails every time when it runs in SQL Agent. Have I missed a setting on the Password? Is there something special with the password before it's left to run in SQL Agent? Maybe the password is being sent encrypted or something? I have not had time to put WireShark on it to capture the packets but that's my next move if someone here can't tell me what's going on so if that happens I will post the results and maybe someone will benefit from my agony.
You folks are awesome when it comes to help. I am very impressed! It's great to see folks who are willing to help. Bryan

What kind of protection level do you use for your package?

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

Jolly Ollie SSC Journeyman Points: 99 More actions · Answer 2

I want to be sure I am in the right place to answer your question. I opened the package, then F4 key, scrolled to the bottom of the properties,

Security Section

Package Password - (this is blank)

Protection Level - EncryptSensitiveWithUserKey

I am new to SQL 2005 SSIS so please let me know if this is not the correct place to answer your question.

Thanks very much for helping me! Bryan

COZYROC One Orange Chip Points: 28499 More actions · Answer 3

Jolly Ollie (1/15/2009)
I want to be sure I am in the right place to answer your question. I opened the package, then F4 key, scrolled to the bottom of the properties,
Security Section
Package Password - (this is blank)
Protection Level - EncryptSensitiveWithUserKey
I am new to SQL 2005 SSIS so please let me know if this is not the correct place to answer your question.
Thanks very much for helping me! Bryan

You are welcome Bryan. This is the default option. It doesn't work very well, unless you use proxy account in your SQL Job Agent. Another approach would be to change the protection level to encrypt all sensitive with password. And then provide the password in the SQL Job Agent. Instead of setting up the default "Integration Services Package" step, setup OS ExecProcess and provide the DTEXEC command line parameters there. You can find good description about the available execution options here.

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