Article ID: 302828 - View products that this article applies to.
This article was previously published under Q302828
BUG #: 354674 (SHILOH_BUGS)
BUG #: 101842(SQLBUG_70)
When a user who is not a member of the sysadmin role executes a Data Transformation Services (DTS) package by using the xp_cmdshell extended stored procedure and the Dtsrun utility, the package may fail to transfer data from the source to the destination. Even though no data transfers, the package execution may report a successful completion (such as in the following error message) and not display any errors:
You may experience similar behavior when you run packages as scheduled jobs. The jobs may report success (although no data is transferred) or failure.
Dtsrun: Package execution complete.
This problem occurs because:
SQL Server 2000To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/290211/EN-US/ )INF: How to Obtain the Latest SQL Server 2000 Service Pack
SQL Server 7.0To resolve this problem, obtain the latest service pack for Microsoft SQL Server 7.0. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/301511/EN-US/ )INF: How to Obtain the Latest SQL Server 7.0 Service Pack
To work around this problem, grant the SQL Agent proxy account read and write permissions to the SQL Server service startup account's TEMP folder.
SQL Server 2000
For Microsoft SQL Server 2000:
For SQL Server 7.0, give the SQLAgentCmdExec account read and write permissions to the C:\Temp folder.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.
SQL Server 2000
This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.
SQL Server 7.0
This problem was first corrected in Microsoft SQL Server 7.0 Service Pack 4.