Home » .Net Framework

SQL Server agent job will not retain package password (encrypt sensitive with password)

I have a package protected by a password - I am already unhappy that to get it to use the configuration file to change connection strings for the production servers I have had to hardcode the password into the config file - very insecure!

However, the package now deploys correctly to the production server and will run from there OK, but NOT if scheduled as a SQL Server Agent Job.  Thus is because however often I edit the command line to include the password after the DECRYPT switch (which it has prompted me for when I click on the command line tab), the Job Step will not retain it.

If I open it up after I have edited it and closed it, the password has disappeared.

I know that if I run dtexec plus the code in the Command Line tab (with the password), the package runs OK.

This is driving me insane! 

I have read all the other posts and so I tried replacing the SSIS package step with a CmdExec step and pasting that code into there - then I get an OLEDB error..

The code I use is:

DTEXEC /SQL "\ImportRateMonitoringTables" /SERVER servername /DECRYPT password /CONFIGFILE "D:\Microsoft SQL Server\SSIS\Deployments\RateMonitoringImportTasks\Deployment\ImportRateMonitoringTables_Production.dtsConfig"  /MAXCONCURRENT " -1 " /CHECKPOINTING OFF  /REPORTING E

and I get

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF

although the same code executes perfectly from a command prompt.

Please does anyone have any experience with a similar problem and if so, how did you get round it?

Thank you


4 Answers Found


Answer 1


I had the same issue with the getting the package  to run successfully. I have an SSIS package with 'Encrypt sensitive  with password'. I was to able to get it working with the following steps:

1. Connect to your Integration Services instance on the same server  where the package is located.

2. Right click on the MSDB folder and click on the option to "import package".

3. The package location will be file  system, browse to the package name. It should have a dtsx extension. Click on the elipise button next to protection level. Select encrypt  sensitive data with password. Type in your password  for the package. Click ok. Enter your password for the package once more.

4. Under sql  Server Agent right click the jobs folder and select new job. Create a new job  step. The Type will be SQL Server Integration Services Package. The package source will be SSIS Package Store. Select the correct server name. Select the the elipise button next to the Package: option and select the package in step #3 under the MSDB folder.

5. You shouldn't need the configuation file because the password is already stored in Step #3. You should also be able to schedule the job now.

Give it a try and let me know if it works. No more configuation file needed.


Answer 2

You rock, thank you.  I actually went through your instructions once, and it's this part that I was missing:

Click on the elipise button next to protection level. Select encrypt  sensitive data with password. Type in your password  for the package. Click ok. Enter your password for the package  once more.

Answer 3

Thank you
I used this and it is working perfectly


Answer 4


I have a package  with encrypt  sensitive with password. I followed your instructions. It gives me below error with out configuration  file. Any clue ?


Executed as user: XXX\XXXX. Microsoft (R) SQL server  Execute Package Utility  Version 10.0.2531.0 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  1:54:57 PM  Error: 2010-07-29 13:54:57.81     Code: 0xC001405F     Source:       Description: Failed to decrypt an encrypted XML node because the password  was not specified or not correct. Package load will attempt to continue without the encrypted information.  End Error  Error: 2010-07-29 13:54:58.08     Code: 0xC001602A     Source: FTP 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: 2010-07-29 13:54:58.09     Code: 0xC002918F     Source: Arello FTP FTP Task     Description: Unable to connect to FTP server using "FTP Connection Manager".  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  1:54:57 PM  Finished: 1:54:58 PM  Elapsed:  0.343 seconds.  The package execution failed.  The step failed.


how to run ssis 2008 package on sql server agent 2005 jobs

Hi All

I have designed a SSIS package to encrypt a file using Cipher (EFS) it works fine from BIDS & Package Execution Utility.

But SQL Server Agent job is failing to do this. I am getting the error as

" In executing C:\Windows\System32\Cipher.exe /e /a C:\Test.txt at "",The Process Exit code was 1 while the expected was 0." 


Please help me.






I have run into a strange problem and hoping someone may have a suggestion.

We have a set of SSIS packages that are developed in BIDS 2005. The packages are all executed by SQL Agent Jobs on a scheduled basis. These packages have been running on numerous SQL 2005 instances for the last year or so with no issues being experienced.

We recently has our first customer start running SQL 2008 R2 and we have internally upgraded to SQL 2008 R2 in our development environment. We are not converting the packages to SQL 2008 as we still need to support customers who are running SQL 2005. The problem is that in both our dev environment and at this particular customer, we have seem a random issue where the SQL Agent job never seems to end. Most of our jobs normally execute in less than 2 minutes, but occasionally (one or twice a week) a random job will just not end until we manually cancel it. We don't know if it is the SSIS package that is not ending or the SQL job.

We never experienced this on SQL 2005.

Anyone have any ideas what may be causing this? Any suggestions for troubleshooting?

Any help will be much appreciated



The SSIS packages have been developed and must be run on every month working date which depend on holidays in that month.

Does anyone get any ideas to create this customized schedule job on sql server agent? Thanks.


Which one of them is better to just run a set of Strored Procedures on a daily basis.

Can I have some Adv and Dis advantages. Please.




My Requirement: Extract data from JDE AS400 Server through ODBC connection in SSIS that requires password to connect. (via ADO.Net)

Things I did for that :

1. Created the package with protection level as : EncryptSensitiveWithPassword
2. Added Password (pwd=********) to the ODBC Connection manager's connection.
3. Added Package configuration (XML file) to include the connection string of  the ODBC connection manager.
4. Deployed the package in SQL Server msdb as
     a. Gave the credentials of SSIS package.
     b. Updated the connection string of the ODBC connection manager to include the password.

5. Then created an SQL job and ran this package which it did successfully!    Hurray !!

Now Quetion:

1. Not sure if this is the right approach to do , though it worked !
2. If the password for the Source (through ODBC connection) account expires then the above process needs to be re-deployed with the correct password.
    I am sure this is not how it should be.

3. Is there a way where we create some kind of proxy account that uses the credentials of the AS400 server required for the ODBC ?




I have an SSIS 2008 package running on Win 2008 64-bit server. The package has an Execute Process Task that runs a net use command using cmd.exe to make a connection.

This runs successfully when tested from the command prompt and runs successfully when the package is run in Visual Studio 2008. However, when the package is called from a SQLAgent job the package fails with the error (actual servernames and passwords removed):

Failing Task Name = EPT Connect mapped drive Error Code = -1073573551 Error Detail = In Executing "C:\WINNT\system32\cmd.exe" "/C net use
\\servername\foldername /USER:userid password" at "", The process exit code was "1" while the expected was "0".

I have determined that a successfull connection should provide a exit code of 0.

The job runs under a proxy account but as explained when the package has run from within VS 2008 it was also tested to run under the proxy account and is successful.

It is not a 64-bit issue as I have tested it by running the package using the 32-bit version of dtexec.exe and the same problem occurs.

Also I have found that by running a simple command process within the Execute Process Task such as dir *.* it is successful when run from the job indicating that it is in fact something related to the net use command itself when run from inside a job.

I have run out of ideas to pursue this - would appreciate anyone's help ?



I scheduled a SSIS package as a SQL Server Agent Job. This job completes successfully every now and then and fails every now and then with encryption error and trying for a fix ever since changing the password security level and nothing seems to be working. How do I fix this?



i have an ftp fetch package in bus development studio 2008.  it runs from that interface.  i imported it into Integration Services, set the password along with the EncryptSensitiveWithPassword and if i manually enter password each time i can run successfully from sql integration services.  but i cannot schedule as a job because the password won't stick.

any help out there would be great.

Hello All,

On our network we have a large number (40 odd) SQL Servers varying from SQL Server 7.0 through to SQL Server 2008, presently we are having to log on directly every morning to each server and check the job history, or, analyse the Agent emails we receive in our inboxes and then enter the results on a "montoring" spreadsheet.

This takes a large amount of time every day, somewhere in the order of 1 1/2 hours. 

I wondered if it was at all possible to write some sort of procedure that searches the domain for all SQL Servers , polls each one it finds and then copies over to a spreadsheet or some other file all the jobs that have failed. Then, drop a checkpoint, or timestamp so that when next run you don't get all the previously recorded history..

I'd be interested to hear people's solutions.

Many Thanks


 I am using SQL Server 2008 and I want to create one job in that.

 Job should be like that it will run any T-SQL and the output of that query

should be mail to me. But I am not able to do this.

Please help me...


I have developed an SSIS 2005 package but the client was reluctant to install SSIS on their SQL Server and instead installed it (SSIS) on an IIS server which doesn't have SQL Server on it all. Microsoft documentation surprisingly indicated this would work. http://technet.microsoft.com/en-gb/library/cc966389.aspx states "SSIS packages can also be run on systems where SQL Server is not installed. However, the .NET Framework and the SSIS runtime must be installed in order to execute the packages on systems that do not have SQL Server 2005 installed."

The package can be run from within BIDS and by invoking from the SSIS server fine and runs to completion. It fails when invoked by the SQL Server Agent on the actual SQL Server (i.e. NOT the SSIS server) (which IS Enterprise version) and raises a DTS_E_PRODUCTLEVELTOLOW error. This is the third "step" out of three and is a fairly basic data flow task where the results of a SQL Server proceedure are output to a text file.

I have logging on which doesn't tell me anything more than I already knew
OnError,<ServerName>,<SQLServerAgentUser>,<PackageName>,{C88F42DE-E3A8-40CB-A140-F10E270C5F1F},{1D2B421E-E067-4458-A908-23C7F081EF28},9/30/2010 4:52:21 PM,9/30/2010 4:52:21 PM,-1073450754,0x,SSIS Error Code DTS_E_PRODUCTLEVELTOLOW.  The product level is insufficient for component "Delegates Extract File" (73).

The SQL Server is 2005 Enterprise at SP2. SSIS is patched to SP3 and KB970892 Security Updates for SQL2005 SP3.

I have added the package to SSIS as a file system package. On the SQL Server I created the job in SQL Server Agent selecting SQL Server Integration Integration Services Package as the type, then providing all the appropriate configuration which gives the following command line


This isn't permissions. The package runs with a SSIS Package configuration file, can read and write to the required folders when run in the context of the SQL Server Agent account, can access the database to execute stored procedures and persist data and only fails with the above error at the final Data Flow Task step which is attempting to output the results from a stored procedure to a text file.

Has anyone managed to execute an SSIS 2005 package located on another machine where the SSIS server does not have SQL Server on it from SQL Server Agent on a SQL 2005 Enterprise server?

I've read so many articles but am no closer to a solution. I have recreated the package within BIDS from the SSIS server. This didn't work. Same error. Any suggestiojns gratefully received. This is driving me mad.

Despite assertion above does SSIS need to be installed on the same server as the SQL Server Agent will execute the package from?

Thanks in advance



hi...am a beginner ..kindly help me ....am working in asp.net application... i have got two pages.... register page and sign in page...

so In a Register Page, when am registering username and password , the password which i enter in that textbox should be encrypted and gets stored in the sql server database...

In the signin page , while i enter username and password , the password should be checked with the database and to be directed to another page.... should i decrypt the password for that??.... i searched the web for this , am really confused....

Kindly help me with the simplest technique to do this....Thanks in advance.....




I'm unable to change Job owner of the SQL Agent Job, even for newly created job as well. Once (at the time of job creation) job in placed job owner is fixed, I can't change the owner even to 'sa' as well.

This is happening on particular instance for other's its fine, I can change the Job Owner.

Thanks in Advance.


I have searched for long time, the following 2 links can script all jobs via SSMS manually, or use T-SQL to create stored procedure for each job. Both were not satisfied for me. I need to setup a job which can script all jobs into a flat file everyday. We can do it in sql 2005, but SQL 2008 does not support SMO and VB anymore. Can anybody gives me a clue about the issue? for example, if it's possible to use powershell to do it?






I know that you can use sp_update_job to enable a job. But I'm talking about enabling a particular schedule of a certain job. I don't see a parameter to sp_update_job that would indicate that you can do this.

I'm using a SQL 2000 32bit on a Windows 2003 box.  I have a job which executes the a stored procedure called procDbaInsertProcess.  The logic for the store proc follows:

insertinto dbo.DbaProcessMonitor

























       'Set FmtOnly OFF; EXEC dbo.sp_Who2')

This job fails when owned by the agent account (sysadmin and for troubleshooting purposes temporarily elevated to domain admin).  The error follows:

Sql Severity  16
Sql Message ID  7410
Operator Emailed  
Operator Net sent  
Operator Paged  
Retries Attempted  0

Executed as user: domain\*******. Remote access not allowed for Windows NT user activated by SETUSER. [SQLSTATE 42000] (Error 7410).  The step failed.


Any help is greatly appreciated.


Re: SQL Server Agent

I'd like to write a script that sends specific information on job steps.

I know how to query from system tables (master.sys.sysprocesses) to find out what the job is that's currently running. The script executes in a job step and sends the name of the job in a notification to my inbox when a certain event occurs. But how do I know what step is running? Would job steps also register in the master.sys.sysprocesses table during execution?

I'd like to have the script execute during the step and send information via sp_send_dbmail to my inbox if certain errors or warnings occur - as I find the error notification service to be a little shortcoming.



PS: this is running on a SQL 2008 server instance


I have an issue with a SQL2005; I have a query that when I ran it in management studio it ran in 8 mins, it's ok, this is an acceptable time for me, but when I put this query in a job to be executed by SQL Agent, it takes more than one hour to complete.

Does anyone have any hint or recommendations on what can be the cause if this issue?


I have four SQL Agent jobs calling SSIS packages, all work fine, all permissions set ok.

I wanted to set them up to run sequentially so I created a 5th job with 4 steps, each step running one of the original 4 jobs. The first three were set up to 'Quit the job' on Failure, and 'Goto the next step' on success. The final step is set to 'Report success' on success and 'Quit the job' on failure. Logical or so I thought.

What I'm getting is all 4 steps starting at the same time. This causes one step to fail as it can't run at the same time as one of the other steps (I could code round it but that doesn't fix the issue). The failed step DOESN'T fail the main job even though it is (supposedly) set up to do so, it completes and reports as a success.

I am using SQL 2008 SP1 but I am not sure that is the issue in itself, I wouldn't be surprised if there's a switch somewhere that I am missing.

(The jobs run overnight so if I rerun the failed step/job the following morning, it runs and completes like a dream!)

Any ideas anybody?


<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure