June 3, 2003 at 3:46 pm
Hi:
Trying to execute a DTS package using either of two methods:
1) job. Specifically
sp_start_job @job_name = 'ImportMAS90Data', @server_name = 'WA-Goldmine'
where the job type is an operating system command (CmdExec) and consists of only one step whose command is:
dtsrun /S WA-Goldmine /U jkk /P jkkghop /N ImportMAS90Data
or
2) xm_cmdshell to run dtsrun. Specifically:
EXEC xp_cmdshell 'dtsrun /S WA-Goldmine /U jkk /P jkkghop /N ImportMAS90Data'
However, both of these mechanisms fail:
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: Drop table [license].[dbo].[bomtemp] Step
DTSRun OnStart: Drop table [license].[dbo].[tempsupp] Step
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnError: Drop table [license].[dbo].[bomtemp] 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
Help file:
Help context: 0
and so on. The same errors appear if I run the job directly from Enterprise Manager.
However, if I run
dtsrun /S WA-Goldmine /U jkk /P jkkghop /N ImportMAS90Data
from the command line of my machine, it works as expected.
The specific task is to:
1) drop three tables SQL
2) re-create the three tables SQL tables
3) import data from three DBF tables into corresponding SQL tables.
The connections of the three tasks do have a property called 'Use Encryption for Data' as boolean and set to 0. However, I've never set or edited that value.
Using SQL Server 7.0, service pack 4
Any help is appreciated.
Thank you.
JK
June 3, 2003 at 5:09 pm
Hi:
It looks like I found the answer. I developed the package on a machine with 2.7. I'll check to see what version is on the target machine.
Microsoft Knowledge Base Article - 811906
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
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.
Thanks,
JK
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply