?
Solved

How do I execute a DTS package from a stored procedure?

Posted on 2005-03-22
50
Medium Priority
?
247 Views
Last Modified: 2013-11-30
How would I execute DTSexample from a stored procedure?

JP
0
Comment
Question by:gleznov
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 28
  • 16
  • 6
50 Comments
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13600339
exec master..xp_cmdshell 'DTSrun.exe ...'

see example:
http://www.databasejournal.com/features/mssql/article.php/3404001
0
 

Author Comment

by:gleznov
ID: 13600363
so then I have to make it a saved-to-disk dts file and run it with DTSrun.exe?   I'm a little unclear on your code - what are the two periods between master and xp_cmdshell?

JP
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13600370
one more:

http://www.sqldts.com/default.aspx?210

about MS SQL SERVER MS-DOS utility - dtsrun.exe see BOL
0
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
LVL 43

Expert Comment

by:Eugene Z
ID: 13600400
see sample:
-------------
Create procedure usp_Younameit
as
---To execute a DTS package saved in the SQL Server msdb database, use:




exec master.dbo.xp_cmdshell 'dtsrun /Sserver_name /E /Npackage_name'
          ^        ^               ^
        DB |  owner of db's object
-----------------

C:\WINNT>dtsrun.exe /?
Usage:  dtsrun /option [value] [/option [value]] ...
Options ('/?' shows this screen; '-' May be substituted for '/'):

  Package retrieval:
    /~S Server Name
    /~U User Name
    /~P Password
    /E <Use trusted connection instead of /U /P>
    /~N Package Name
    /~M Package Password
    /~G Package Guid String
    /~V Package Version Guid String
    /~F Structured Storage UNC filename (overwritten if /S also specified)
    /~R Repository Database Name <uses default if blank; loads package from repo
sitory database>

  Package operation (overrides stored Package settings):
    /~A Global Variable Name:typeid=Value <may quote entire string (including na
me:typeid)>
    /~L Log file name
    /~W Write Completion Status to Windows Event Log <True or False>

  DTSRun action (default is to execute Package):
    /!X <Do not execute; retrieves Package to /F filename>
    /!D <Do not execute; drop package from SQL Server (cannot drop from Storage
File)>
    /!Y <Do not execute; output encrypted command line>
    /!C <Copies command line to Windows clipboard (may be used with /!Y and /!X)
>

  Notes:
    ~ is optional; if present, the parameter is hex text of encrypted value (0x3
13233...)
    Whitespace between command switch and value is optional
    Embedded whitespace in values must be embedded in double-quotes
    If an option is specified multiple times, the last one wins (except multiple
 /A)



0
 

Author Comment

by:gleznov
ID: 13600861
is that /S server_name /E /N package_name  or /Sserver_name /E /Npackage_name?

JP
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13600894
no spaces...
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13600897
/Sserver_name /E /Npackage_name
0
 

Author Comment

by:gleznov
ID: 13600908
thx
0
 

Author Comment

by:gleznov
ID: 13600915
OK, so I have to do something (that that first link above showed) that exports my DTS packages, or is that calling them as they are in Enterprise Manager?

JP
0
 
LVL 21

Assisted Solution

by:Kevin3NF
Kevin3NF earned 600 total points
ID: 13600993
There is an extended sp in the master database called xp_cmdshell.  Executing this has the same effect as opening a DOS window.  What follows that is what you would type in that DOS window if you were running your package manually.  DTSRun.exe is a SQL Server provided executable that you feed in parameters for, such as Server name (/S), how to authenticate (/E) and the name of the DTS package name that you created (/N).

Using EugeneZ's recommendation, you just fill in the rest of the parameters.  Obviously, you have to have your DTS package created first.  No need to export anything.  The DTSRUN.exe finds the package and executes it.

Clearer?
0
 

Author Comment

by:gleznov
ID: 13601131
Sweetness - I'm gonna go see what I come up with
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13601148
My first link was just sample how to wrap dtsrun in sp!
There is no need for your question to export (unless you'd like to do it)

how Create DTS package:
http://www.devguru.com/features/tutorials/DTS/DTS1.html
http://www.sys-con.com/coldfusion/articleprint.cfm?id=657
0
 

Author Comment

by:gleznov
ID: 13601229
ok I have:

database called 4Randy, and the stored procedure is called 4Randy in that database.

I tried this in query analyzer:

exec master.dbo.xp_cmdshell 'dtsrun /S4Randy /E /N4Randy'

and got this (After a long pause) in an output windows box:

-------------
DTSRun:  Loading...


Error:  -2147467259 (80004005); Provider Error:  17 (11)
   Error string:  [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
   Error source:  Microsoft OLE DB Provider for SQL Server
   Help file:  
   Help context:  0

NULL
----------------


???
JP
0
 

Author Comment

by:gleznov
ID: 13601284
I'm sorry, the DTS package is also called 4Randy, not the stored procedure.

JP
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13601531
Is your Query Analyzer session connected to 4Randy?
0
 

Author Comment

by:gleznov
ID: 13601709
Yes, or I can have a

use [4Randy]

if I need.

JP
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13601766
Can you get any query to run against the 4Randy server?  Seems odd that you get a SQL server does not exist or access denied error if you are actually connected to the server in question...
0
 

Author Comment

by:gleznov
ID: 13601798
One thing that throws off query analyzer is the 4 in 4Randy - could the same be true on the command line for DTSRun.exe?  can I try something like:

/S"4Randy" /E /N"4Randy"?

JP
0
 

Author Comment

by:gleznov
ID: 13601836
well I truncated the tables necessary to receive the DTS'd in data without a problem.

JP
0
 

Author Comment

by:gleznov
ID: 13601978
Anyone still around?

JP
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13602043
E- it is for trusted connection - what connection do you use?
--try /U User Name    /P Password

BTW: in EM if you right click your DTS ->  Execute-> post result of execution
0
 

Author Comment

by:gleznov
ID: 13602070
I can try that... but I always use the integrated windows security, which I'd imagine is the same as E?  if not, nothing wrong with sa.

I'll get the results of execute DTS on here in just a moment.

JP
0
 

Author Comment

by:gleznov
ID: 13602147
Your suspicions were right, I've got some problems in the DTS packages I hadn't noticed before.  I'll get back to ya.
JP
0
 

Author Comment

by:gleznov
ID: 13602314
My final issue that I can see is that the DTS package pulls a file from my C drive in a certain directory.  When I right-click/execute it in enterprise manager, no problem, but try to call it from query analyzer and no-go.    I think if I redo the DTS packages using a network path to the file, maybe it'll work.

JP
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13602432
>network path to the file - it is good idea - in DTS design - you see your PC drives but sql server...
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13603007
More than likely, you would be better off having the source file that currently resides on your PC on a network share on a file server.  If your hard drive crashes...what is the recovery plan?

Also, I would use UNC over a mapped drive every time...better results.
0
 

Author Comment

by:gleznov
ID: 13603913
Backup plan:

     Well this program will be a one-button pusher deployed to a coworker to take two text database outputs and combine them on one field and reoutput it in a text file.  Should his machine crash, my development machine is backup - should mine crash, I have redundant hard drives.  Should that also fail, we have nightly backups :)

JP
0
 

Author Comment

by:gleznov
ID: 13604000
Well, I've used the UNC to the file in the DTS package.  I've also changed it from integrated security to the sa user with correct password, but I still get the same results no matter what I'm doing.  Any other ideas?

JP
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13604112
what error?: when you are trying execute DTS pack from EM?
0
 

Author Comment

by:gleznov
ID: 13604191
DTS from EM works, DTS called from Query Analyzer doesn't (or command prompt on server).  Error is listed above, but here again:

-------------
DTSRun:  Loading...


Error:  -2147467259 (80004005); Provider Error:  17 (11)
   Error string:  [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
   Error source:  Microsoft OLE DB Provider for SQL Server
   Help file:  
   Help context:  0

NULL
----------------

I get this each time I try to run one of the DTS packages this way.  I've tried setting them up using integrated security and SQL Server user/pass, same result.  I've changed from a hard-path to UNC path for where to retrieve the file it DTS's in...

Let me go into more detail in case I haven't mentioned something important:

There will be a directory on my harddrive called 4Randy (c:\4Randy)
In there will be placed two files, status.txt and status-1.txt
At this point I have 3 empty tables in my 4Randy database on the server, which is not the same as my computer.
These tables are status, status-1 and final.
I run DTS package 1 which pulls status.txt from my harddrive into the status database on the server
I run DTS package 2 which pulls status-1.txt from my harddrive into the status-1 database on the server
I run the 4Randy stored procedure which combines these two tables using one common field into the final database
I then run the final DTS package that write the contents of the final database on the server back to my harddrive as Results.txt

Maybe there's something in this process of two different machines I'm missing?

JP
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13604235
can you run your dtsrun... from MS-DOS?
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13604279
you can generate dtsrun string with dtsrunui.exe utility:

Start->RUN->[type ->]dtsrunui.exe-> in general tab select your pack->click advanced near ...
click generate -> copy it -> inside xp_cmdshel ' ....' -> run and see..
0
 

Author Comment

by:gleznov
ID: 13604371
I did that, and when I run it, I get something different, but still with an error:

DTSRun:  Loading...
DTSRun:  Executing...
DTSRun OnStart:  Copy Data from status-1 to [4Randy].[dbo].[status-1] Step
DTSRun OnError:  Copy Data from status-1 to [4Randy].[dbo].[status-1] Step, Error = -2147467259 (80004005)
   Error string:  Error opening datafile: Access is denied.
 
   Error source:  Microsoft Data Transformation Services Flat File Rowset Provider
   Help file:  DTSFFile.hlp
   Help context:  0
 
Error Detail Records:
 
Error:  5 (5); Provider Error:  5 (5)
   Error string:  Error opening datafile: Access is denied.
 
   Error source:  Microsoft Data Transformation Services Flat File Rowset Provider
   Help file:  DTSFFile.hlp
   Help context:  0
 
DTSRun OnFinish:  Copy Data from status-1 to [4Randy].[dbo].[status-1] Step
DTSRun:  Package execution complete.
NULL




JP
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13604616
was your file in use during pack execution?
can you type not UNC path to your file -  e.g. d:\ and try again?
0
 

Author Comment

by:gleznov
ID: 13604857
I've tried with UNC or with direct path - same results

JP
0
 

Author Comment

by:gleznov
ID: 13605554
One thing of note - it does work from the DOS prompt on the server now...

JP
0
 

Author Comment

by:gleznov
ID: 13605577
So if I understand right, that means the DTS package is correctly able to, from the server, grab the file from my machine through the UNC path, and make it work.  So why then, when called from xp_cmdshell, does it not work?

JP
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13606634
plan B`:copy from DOS working code in text file -> rename .txt file in BAT - create batch file

then run:
 exec master.dbo.xp_cmdshell 'C:\yourbachfile.bat'
0
 

Author Comment

by:gleznov
ID: 13612544
Very good idea, but it didn't work :/  I wonder what's going on?  If I run the bat file from the server machine, it does fine.  If I run it using cmdShell in Query Analyzer, errors....  what gives?

JP
0
 

Author Comment

by:gleznov
ID: 13612890
Has anyone ever had something like this to work?  Looking back, even on some of the links I was given above to similar questions, none of those people every found a solution.

JP
0
 

Author Comment

by:gleznov
ID: 13612909
Are there some kind of permissions in SQL Server that may need to be set?  Any certain user maybe?  An in-built user?  It's gotta be something...  Does Query Analyzer, when calling something like that, have some kind of automatic unseen username?

JP
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13614196
First: make sure MSSQLSERVER service and,  very important, sqlserveragent service are running as member of local admin group
0
 
LVL 43

Accepted Solution

by:
Eugene Z earned 1400 total points
ID: 13614229
Second:
you need to be a member of the sysadmins group to run xp_cmdshell.  If you're not, you can enable the "proxy" account in SQLAgent to allow non-sysadmins the right to execute it.

...  Open Enterprise Manger, go to management under the server you're working on, Right-click on "SQL Server Agent" and choose properties.  Go to the job-system tab--down in the "non-sysadmin job step proxy account" what do you see?
0
 

Author Comment

by:gleznov
ID: 13614433
Yeah, it's checked.  I'm having one of my coworkers who knows a little more about the admin side of Sql Server look at it in a bit and see if we can get that straightened out.  I'll get back to you.  And thanks!

JP
0
 

Author Comment

by:gleznov
ID: 13616056
We're messing with it, but no luck yet :(  Any other pointers about the permissions/etc?

JP
0
 

Author Comment

by:gleznov
ID: 13621206
I have a little problem in my UNC - it wanted //mycomputer/sharedfolder/file, I was giving it //mycomputer/c$/sharedfolder/file - dumb mistake.  It all works fine now!  Thank you all so much!  I'll do my best to split fairly.

JP
0
 

Author Comment

by:gleznov
ID: 13621209
ah there's only two of you, that makes it easier :)  (lol)  
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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 …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

762 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