Running  a DTS Package

Posted on 2007-10-11
Medium Priority
Last Modified: 2013-11-30
I have a stored procedure that runs a DTS Package with the following syntax:

exec master..xp_cmdshell 'dtsrun /Smyserver /NFile Import For 2011 /Usa /Pmysapassword'

It's supposed to import a text file into a table that I have in my DB. If I try to run this command from a query window, it does not work, but if I go to Data Transformation Services, right click on it and select Execute, it does work. Why is this happening?

Question by:ONYX
LVL 18

Expert Comment

ID: 20061352
what error do you get from the query ?
I guess it has to do with permissions ...

I think (not completely sure) this is similar as with jobs. When you run a DTS it will run under the credential of the SQL Server Agent account. When you try to run it from a query window, you wil try to run it with your credentials ... and your credentials are probably not sufficient to run a master..xp_cmdshell ...

Hope this helps ...
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20061736
>>it does not work<<
Don´t you hate when that happens.  But is there any chance you can tell us if one of the following applies:
1. You get an error message.  If so, what is it?
2. You do not get the right results.  If so, what are they and what are the desired results.
3. Smoke comes out of your monitor.

Author Comment

ID: 20061768
Well, I appreciate your sense of humour, and I apologize for the lack of detail...my bad.

the odd part is, I don't really see an error message, on the query resulst window it displays the following:

DTSRun:  Loading...
DTSRun:  Executing...
DTSRun:  Package execution complete.

I'm assuming that the NULL is that it didn't import any records, but can't figure out why. Indeed, no records were imported into the table, I did verify this.

Thanks For your help.
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

LVL 75

Expert Comment

by:Anthony Perkins
ID: 20061864
Than you need to start by adding logging.  This will produce a text file that you can inspect and verify that Yveau is absolutely correct and you have a permission problem.

Author Comment

ID: 20061911
Okay, one last question. How do I add logging? I have SQL 2000. Is it logging my query? Or is it for a particular DB? Or for the SQL Server as a whole? Thaniks for your feedback.

LVL 75

Accepted Solution

Anthony Perkins earned 100 total points
ID: 20061982
I was referring to DTS Logging.  Right click on the package,  Properties and then select the Logging tab.  There you will find a place to add a complete path to the log file.  Make sure the path is available to both the client as well as the server.
LVL 30

Assisted Solution

nmcdermaid earned 100 total points
ID: 20062015
Running a DTS via xp_cmdshell runs it on the server, as a the SQL Server service user (generally speaking)

Running a DTS interactively via EM runs it on the local machine, as yourself.

So there are two main differences:

1. The drive letters that you can see are different (Use UNC's - never use a mapped drive letter)
2. The user that it is runs as is different. (again two different users will usually have two different sets of security access to drives)

The default service account that SQL is set to is LOCAL\System which doesn't have access to ANY remote drives.

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

850 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