DTS runs but Scheduled Job will not run the DTS

[ibethea]

The job ran previously. I only changed two field transformations and tried to re-run the job. It failed. I ran the DTS and it succeeded.

I get the following error when I try to run the job.
... DTSRun: Executing... DTSRun OnStart: Delete from Table [XXXXXXXX].[dbo].[YYYYYYYY] Step DTSRun OnError: Delete from Table [XXXXXXXX].[dbo].[YYYYYYYY] Step, Error = -2147217887 (80040E21) Error string: The property 'Use Encryption for Data' is not supported. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 1100 Error Detail Records: Error: -2147217887 (80040E21); Provider Error: 0 (0) Error string: The property 'Use Encryption for Data' is not supported. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 1100 Error: -2147217887 (80040E21); Provider Error: 0 (0) Error string: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. Error source: Microsoft OLE DB Provider for SQL Server Hel... Process Exit Code 1. The step failed.

 

[Bernadette]

Hi There,

The most common reason for this failure is login credentials. When you execute a DTS package the package is started with the user who starts SQL Server Agent. If you use Windows NT Authentication for any of your connections inside your package, the login used to start Agent must also exist on this remote system.
Make sure the user has proper rights to the UNC paths, files, and shares needed to execute the package.
If you start the Agent with the Local System account, remember that this account does not have any access to remote systems.
Also never use a mapped drive inside your packages ... use UNC paths instead.

Hope This Helps

Bernadette

 

[tlbroadbent]

This doesn't appear to be a credentials problem. The message provides a hint.

Error string: The property 'Use Encryption for Data' is not supported.

This error is usually due to MDAC incompatibility. 'Use Encryption for Data' is only available in MDAC 2.6. What version of SQL Server is running? What version of SQL tools are installed on the PC where the package was modified? What version of MDAC is on the server and the client?

Check out the following sources:

http://www.sqldts.com/main.asp?nav=1,6,223,0

http://www.sqldts.com/main.asp?nav=1,6,227,0

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[ibethea]

Terry L. Broadbent

I am inclinded to believe you are correct. I am running SQL Server 7.0 and my local MDAC version is 2.5. I will need to check the MDAC on the Server side. I have had MDAC problems before. However, if the dts runs on the server and from my local machine, but the job does not run from either, I am still perplexed. Might it be credentials?

 

[tlbroadbent]

It doesn't seem likely that it is a credentials issue due to the error mesage. However, it would be worth checking into.

You might try adding a password to the package when you save it. Modify the job to include the package pasword and see if it runs then. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.