Running  a DTS Package

Posted on 2007-10-11
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

    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
    >>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

    Well, I appreciate your sense of humour, and I apologize for the lack of 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.
    LVL 75

    Expert Comment

    by:Anthony Perkins
    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

    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

    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

    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

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Find data in sql table column which is not integer 5 19
    sql sproc 2 22
    query question 4 20
    vb6 connector to SQL Server 2 20
    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    732 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

    21 Experts available now in Live!

    Get 1:1 Help Now