PRB: The "Use Encryption for Data" Property Is Not Recognized When You Run a DTS Package on a Computer with MDAC 2.5 or Earlier (811906)


The information in this article applies to:

  • Microsoft SQL Server 7.0

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

  1. Open the DTS package on a computer that has MDAC version 2.5 or earlier.
  2. 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).
  3. Save and re-open the package.
  4. Revert back to the original settings:
    1. In SQL Server Enterprise Manager, expand SQL Server Group.
    2. Expand SQL Server.
    3. Expand Data Transformation Services, and then click Local Packages.
    4. Right-click package name, and then click ackage.
    5. Right-click Connection object, and then click Properties. The Connection Properties dialog box opens.
    6. In the Data Source list, click Microsoft ODBC Driver for SQL Server.
    7. Click OK.
    8. Right-click Connection object, and then click Properties.
    9. In the Connection Properties dialog box, click Microsoft OLE DB Provider for SQL Server in the Data source list
    10. Select Database, and then click OK.

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

REFERENCES

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

255900 DTS Package Created on Uplevel (2.5/2.6) MDAC May Fail on Computer with Downlevel (2.1) MDAC

Modification Type:MinorLast Reviewed:5/25/2006
Keywords:kbprb kberrmsg KB811906 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.