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:
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
SQL Server. - Expand Data Transformation Services, and then click Local Packages.
- Right-click
package name, and then click ackage. - Right-click
Connection object, and then click Properties. The Connection Properties dialog box opens. - In the Data Source list, click Microsoft ODBC Driver for SQL Server.
- Click OK.
- Right-click
Connection object, and then click Properties. - In the Connection Properties dialog box, click Microsoft OLE DB Provider for SQL Server in the Data source list
- 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
Keywords: kbprb kberrmsg KB811906
