• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

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

How would I execute DTSexample from a stored procedure?

JP
0
gleznov
Asked:
gleznov
  • 28
  • 16
  • 6
2 Solutions
 
Eugene ZCommented:
exec master..xp_cmdshell 'DTSrun.exe ...'

see example:
http://www.databasejournal.com/features/mssql/article.php/3404001
0
 
gleznovAuthor Commented:
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
 
Eugene ZCommented:
one more:

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

about MS SQL SERVER MS-DOS utility - dtsrun.exe see BOL
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Eugene ZCommented:
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
 
gleznovAuthor Commented:
is that /S server_name /E /N package_name  or /Sserver_name /E /Npackage_name?

JP
0
 
Kevin3NFCommented:
no spaces...
0
 
Kevin3NFCommented:
/Sserver_name /E /Npackage_name
0
 
gleznovAuthor Commented:
thx
0
 
gleznovAuthor Commented:
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
 
Kevin3NFCommented:
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
 
gleznovAuthor Commented:
Sweetness - I'm gonna go see what I come up with
0
 
Eugene ZCommented:
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
 
gleznovAuthor Commented:
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
 
gleznovAuthor Commented:
I'm sorry, the DTS package is also called 4Randy, not the stored procedure.

JP
0
 
Kevin3NFCommented:
Is your Query Analyzer session connected to 4Randy?
0
 
gleznovAuthor Commented:
Yes, or I can have a

use [4Randy]

if I need.

JP
0
 
Kevin3NFCommented:
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
 
gleznovAuthor Commented:
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
 
gleznovAuthor Commented:
well I truncated the tables necessary to receive the DTS'd in data without a problem.

JP
0
 
gleznovAuthor Commented:
Anyone still around?

JP
0
 
Eugene ZCommented:
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
 
gleznovAuthor Commented:
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
 
gleznovAuthor Commented:
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
 
gleznovAuthor Commented:
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
 
Eugene ZCommented:
>network path to the file - it is good idea - in DTS design - you see your PC drives but sql server...
0
 
Kevin3NFCommented:
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
 
gleznovAuthor Commented:
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
 
gleznovAuthor Commented:
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
 
Eugene ZCommented:
what error?: when you are trying execute DTS pack from EM?
0
 
gleznovAuthor Commented:
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
 
Eugene ZCommented:
can you run your dtsrun... from MS-DOS?
0
 
Eugene ZCommented:
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
 
gleznovAuthor Commented:
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
 
Eugene ZCommented:
was your file in use during pack execution?
can you type not UNC path to your file -  e.g. d:\ and try again?
0
 
gleznovAuthor Commented:
I've tried with UNC or with direct path - same results

JP
0
 
gleznovAuthor Commented:
One thing of note - it does work from the DOS prompt on the server now...

JP
0
 
gleznovAuthor Commented:
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
 
Eugene ZCommented:
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
 
gleznovAuthor Commented:
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
 
gleznovAuthor Commented:
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
 
gleznovAuthor Commented:
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
 
Eugene ZCommented:
First: make sure MSSQLSERVER service and,  very important, sqlserveragent service are running as member of local admin group
0
 
Eugene ZCommented:
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
 
gleznovAuthor Commented:
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
 
gleznovAuthor Commented:
We're messing with it, but no luck yet :(  Any other pointers about the permissions/etc?

JP
0
 
gleznovAuthor Commented:
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
 
gleznovAuthor Commented:
ah there's only two of you, that makes it easier :)  (lol)  
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 28
  • 16
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now