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.
samprgAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent DyerIT Security Analyst SeniorCommented:
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
samprgAuthor Commented:
I created it in sql 2005, It works fine in BI
Simone BSenior E-Commerce AnalystCommented:
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
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

samprgAuthor Commented:
Buttercup1:
I want to use "DontSaveSensitive" ,If I save the Password in variable , how can I make ftp connection  use a password variable
samprgAuthor Commented:
Which ProtectionLevel I need to use it, I need just the package work in sql agent
Simone BSenior E-Commerce AnalystCommented:
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.
samprgAuthor Commented:
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.



Simone BSenior E-Commerce AnalystCommented:
From MSDN, EncryptAllWithUserKey means "Encrypts the whole package by using a key based on the user profile. Only the same user using the same profile can load the package. The package is encrypted by using a key that is based on the user who created or exported the package. Only the user who created or exported the package can open the package in SSIS Designer or run the package by using the dtexec command prompt utility."

SQL Agent is not you, and so it doesn't have your user information when it tries to run the job. Try using the protection level DontSaveSensitive, and add the configuration files. That way, SQL Agent always uses the connection strings you set within the config files to run the package.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
samprgAuthor Commented:
If administrator add the package in sql , will it be work?
samprgAuthor Commented:
Awesome,
 I need your answer for my last question please.
Thanks
Simone BSenior E-Commerce AnalystCommented:
Sorry for the delay, too many meetings today. Regardless of who deploys the package you should have the same result.
samprgAuthor Commented:
Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.