asked on 10/5/2011

Sql Server Agent

Hi,
I have SSIS package in sql server 2005, it works fine, but in sql server agent gives me error
that SSiS Package has script task and FTP task, Thanks
Executed as user: t1\SYSTEM. ...on 9.00.4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 4:14:58 PM Error: 2011-10-04 16:14:58.49 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2011-10-04 16:14:59.18 Code: 0xC001602A Source: Members Connection manager "FTP Connection Manager" Description: An error occurred in the requested FTP operation. Detailed error description: The password was not allowed . End Error Error: 2011-10-04 16:14:59.20 Code: 0xC002918F Source: FTP M1XML FTP Task Description: Unable to connect to FTP server using "FTP Connection Manager". End Error DTExec: The pa... The package execution fa... The step failed.

Microsoft SQL Server 2005 Microsoft SQL Server 2008

Last Comment

samprg

10/5/2011

Kent Dyer

10/5/2011

Here is where you get into the "sticky wicket" scenario. If you had a 3rd party FTP setup, which I do know is available for SQL 2000 and you brought this forward to SQL 2005/2008, you will most likely run into issue. The other issue is that if you are running into a Security policy where you cannot use the XPCMD in SQL server (it is a vulnerability, by the way).. I would check change controls and security to see what has gone in lately to address issues such as this.

HTH,

Kent

samprg

10/5/2011

ASKER

I created it in sql 2005, It works fine in BI

Simone B

10/5/2011

If it runs fine in BIDS, but not in SQL Agent, then it could be the protection level in your package. Have a look at this link, it's very helpful:

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

samprg

10/5/2011

ASKER

Buttercup1:
I want to use "DontSaveSensitive" ,If I save the Password in variable , how can I make ftp connection use a password variable

samprg

10/5/2011

ASKER

Which ProtectionLevel I need to use it, I need just the package work in sql agent

Simone B

10/5/2011

Yes, use Don't Save Sensitive, and deploy the package to SQL server. Then, go back to the package, right click and add whatever configurations are required, and save. Each configuration is saved in a separate file. You will need a configuration file for each connection that requires a login. It's a good idea to double check each file in Notepad to make sure the string is correct. Also you may need to type in the password in the config file.

Then in your Solution properties under Deployment Utility, make sure that Allow Configuration Changes is set to True. Then you won't have to rebuild the configurations every time you have to redeploy.

samprg

10/5/2011

ASKER

I did not create configurations.
I import the package in sql integration,, I can run the package in sql Integration, but still sql agent gives me rror?!
I use protictionLeve :EncryptAllWithUserKey

Message
Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2011-10-05 12:27:13.31 Code: 0xC001602A Source: Members Connection manager "FTP Connection Manager" Description: An error occurred in the requested FTP operation. Detailed error description: The password was not allowed . End Error Error: 2011-10-05 12:27:13.31 Code: 0xC002918F Source: FTP FTP Task Description: Unable to connect to FTP server using "FTP Connection Manager". End Error DTExec: The p... The package execution fa... The step failed.

ASKER CERTIFIED SOLUTION

Simone B

10/5/2011