SQL DTS import transfers 0 rows
Posted on 2013-05-23
I have a dts import job that is launched by Dynamics GP VBA code, which calls a stored procedure. Here is the code in the stored proc:
DECLARE @COMMAND varchar(1000)
SELECT @COMMAND = 'DTExec /sq ' + @DTSPKG + ' /ser MANZANITA\BINARY'
EXEC master..xp_cmdshell @COMMAND
@DTSPKG is the DTS package name and is passed as an input parameter.
A proxy account is in place to allow non-admin users to use the xp_cmdshell extended procedure. All permissions have been given to the stored procedures, to xp_cmdshell and to the destination table. The proxy account has the sysadmin role in msdb and in the production and DYNAMICS databases.
The non-admin users are mapped to the production database in the public and DYNGRP (Dynamics GP users) roles and in the msdb database, they are mapped to the following roles:
In component services, in MsDtsServer, each of the non-admin users have the following permissions:
Launch and Activation:
This has been working for 2 years. Two days ago, it stopped working properly. What happens now is the stored procedure appears to successfully launch and the DTS package is executed. But 0 rows are transferred. No error is generated - it just doesn't bring any data into the destination table.
According to the client's IT department, no updates, upgrades or other processes have been done to the server which could account for this.
If I make the non-admin users SQL sysadmins, the process works and data is successfully imported. But as soon as I return their roles to what they were before, the process transfers 0 roles.
I need to get back to where these non-admin users can launch the DTS packages without having the sysadmin role.