[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Sql Server Agent

Posted on 2011-10-04
12
Medium Priority
?
524 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:samprg
  • 7
  • 4
12 Comments
 
LVL 17

Expert Comment

by:Kent Dyer
ID: 36914684
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
0
 

Author Comment

by:samprg
ID: 36914752
I created it in sql 2005, It works fine in BI
0
 
LVL 11

Expert Comment

by:Simone B
ID: 36917218
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
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

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

Author Comment

by:samprg
ID: 36918328
Which ProtectionLevel I need to use it, I need just the package work in sql agent
0
 
LVL 11

Expert Comment

by:Simone B
ID: 36919446
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.
0
 

Author Comment

by:samprg
ID: 36920044
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.



0
 
LVL 11

Accepted Solution

by:
Simone B earned 2000 total points
ID: 36920212
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.
0
 

Author Comment

by:samprg
ID: 36920257
If administrator add the package in sql , will it be work?
0
 

Author Closing Comment

by:samprg
ID: 36920563
Awesome,
 I need your answer for my last question please.
Thanks
0
 
LVL 11

Expert Comment

by:Simone B
ID: 36921167
Sorry for the delay, too many meetings today. Regardless of who deploys the package you should have the same result.
0
 

Author Comment

by:samprg
ID: 36921309
Thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question