Microsoft KB Archive/811906

= PRB: The &quot;Use Encryption for Data&quot; Property Is Not Recognized When You Run a DTS Package on a Computer with MDAC 2.5 or Earlier =

Article ID: 811906

Article Last Modified on 5/25/2006

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



SYMPTOMS
When use DTS Designer to create a Data Transformation Services (DTS) package and you schedule the package as a job, the DTS package might not run and an error message is generated. This behavior also occurs if you use the DTSRun utility to run the package on the server.

This behavior occurs under the following conditions:
 * The computer that you used to create the package is running Microsoft Data Access Components (MDAC) version 2.6 or later.
 * The computer that used to run DTS Designer is running SQL Server with MDAC 2.5 or earlier and it is a Microsoft Windows 2000- or Microsoft Windows 98-based computer.

The following error message is generated:

DTSRun: Loading...

DTSRun: Executing...

DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1

DTSRun OnError: DTSStep_DTSExecuteSQLTask_1, 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: Errors occurred

Error source: Microsoft OLE DB Provider for SQL Server

Help file:

Help context: 0

DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1

DTSRun: Package execution complete. Process Exit Code 1. The step failed.

The same DTS package may run successfully if you use SQL Server Enterprise Manager to run the DTS package manually.



CAUSE
This behavior occurs because you cannot use the Use Encryption for Data property with MDAC 2.5 or earlier. The Use Encryption for Data property is included with MDAC 2.6. When you create packages on a computer that is running MDAC 2.6 or later, they automatically include this property. When you run or schedule DTS packages on a computer that is running SQL Server with MDAC 2.5 or earlier, the package fails because MDAC 2.5 does not recognize this property. 

WORKAROUND
You can run DTS packages successfully if you run them manually. When you use SQL Server Enterprise Manager to run the packages manually, they are executed in the context of the computer on which they are executed. However, a scheduled DTS package is executed in the context of the computer on which SQL Server is installed.

For more information about DTS package execution, click the following article number to view the article in the Microsoft Knowledge Base:

269074 INF: How to Run a DTS Package as a Scheduled Job

To work around this behavior, use either of the following methods:

Method 1
Install MDAC 2.6 (or later) on the computer on which you will run the DTS package.

For current and earlier releases of MDAC, visit the following Microsoft Web site: http://www.microsoft.com/data

Method 2
 Open the DTS package on a computer that has MDAC version 2.5 or earlier. Change the data source driver to something other than the Microsoft OLE DB Provider for SQL Server (such as Microsoft ODBC Driver for SQL Server). Save and re-open the package. Revert back to the original settings:  In SQL Server Enterprise Manager, expand SQL Server Group. Expand  . Expand Data Transformation Services, and then click Local Packages.</li> Right-click  , and then click ackage.</li> Right-click  , and then click Properties. The Connection Properties dialog box opens.</li> In the Data Source list, click Microsoft ODBC Driver for SQL Server.</li> Click OK.</li> Right-click  , and then click Properties.</li> In the Connection Properties dialog box, click Microsoft OLE DB Provider for SQL Server in the Data source list</li> Select  , and then click OK.</li></ol> </li></ol>

<div class="moreinformation_section">

MORE INFORMATION
You can run DTS packages successfully if you run them manually. When you use SQL Server Enterprise Manager to run the packages manually, they are executed in the context of the computer on which they are executed. A scheduled DTS package is executed in the context of the computer on which SQL Server is installed.

For additional information about DTS package execution, click the following article number to view the article in the Microsoft Knowledge Base:

269074 INF: How to Run a DTS Package as a Scheduled Job

<div class="references_section">