Solved

SQL DTS  import transfers 0 rows

Posted on 2013-05-23
2
321 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 39

Accepted Solution

by:
lcohan earned 500 total points
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now