Solved

SQL DTS  import transfers 0 rows

Posted on 2013-05-23
2
333 Views
Last Modified: 2013-05-28
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:

public
db_datareader
db_dbdatawriter
db_ssisadmin
db_ssisltduser
db_ssisoperator

In component services, in MsDtsServer, each of the non-admin users have the following permissions:

Launch and Activation:
    Local Launch
    Remote Launch
    Local Activation
    Remote Activation

Access Permissions
    Local Access
    Remote Access

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.
0
Comment
Question by:thoecherl
2 Comments
 
LVL 40

Accepted Solution

by:
lcohan earned 500 total points
ID: 39195542
There must be some errors in SQL Logs or Event viewer because there is definitely a permission/rights issue is you say that "If I make the non-admin users SQL sysadmins, the process works and data is successfully imported".
And speaking about that - where do you "make the non-admin users SQL sysadmins"?
Are all databases on that same SQL Server?
0
 

Author Closing Comment

by:thoecherl
ID: 39202205
I made the non-admin users sysadmins in SQL Management Studio | Security | Logins.  Yes, all db's are on the same SQL server.

I have since figured out the problem.  When we first setup the program - unknown to us - on the folder in the Windows file system where the source file was located, someone had given Full Control to the folder to the Everyone group.  None of us realized that and when the program worked, we didn't bother to check, security on the folder.

The day that my program stopped working, the System Administrator discovered that hole in the security and took away the Everyone group Full Control.  To resolve that problem I added the Windows account that had been used as the SQL proxy account to the folder and granted Read and Write permission.  That solved the problem.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question