July 9, 2003 at 11:36 am
Can anyone direct me how to execute a DTS package from a stored procedure?
Thanks,
fryere
fryere
July 9, 2003 at 12:05 pm
You need permission to run xp_cmdshell and execute a DTS package using the DTS Run utility from xp_cmdshell.
Examples from BOL.
Examples
To execute a DTS package saved as a COM-structured storage file, use:
dtsrun /Ffilename /Npackage_name /Mpackage_password
To execute a DTS package saved in the SQL Server msdb database, use:
dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name /Mpackage_password
To execute a DTS package saved in Meta Data Services, use:
dtsrun /Sserver_name /Uuser_nrame /Ppassword /Npackage_name /Rrepository_name
July 9, 2003 at 2:47 pm
Thanks Allen,
I can Execute the package at a command prompt, but I can't execute the xp_cmdshell from my SP. I should have access to it since I am a Systemadmin. However, I can execute xp_cmdshell from the query analyzer, but not my SP from the database I am working out of. Any suggestions on how I can use extended SPs from the Master DB in another DB????
Thanks in advance.
fryere
fryere
July 9, 2003 at 2:55 pm
For example, exec master..xp_cmdshell 'dir c:'
July 10, 2003 at 5:09 am
frere,
xp_cmdshell is one way. Alternatively look at this article, it shows you step by step how to use OLE within your SP to handle calls to DTS packages:
http://www.databasejournal.com/features/mssql/article.php/1459181
quote:
The most common method used to execute DTS packages from within T-SQL scripts or stored procedures, is via xp_cmdshell and dtsrun. Whilst this works, and is very simple the implement, it lacks flexibility. The SQL Server 2000 version of dtsrun allows you to pass GlobalVariables on the command line, which improves it's appeal, but the security restrictions on xp_cmdshell can still limit it's appeal. This article demonstrates the OLE stored procedures and some of the benefits of using them.The OLE stored procedures are a very powerful mechanism to access resources outside of those core to SQL Server, and this article demonstrates their use in relation to DTS. Whilst everyone else know refers to COM, not OLE, the SQL Server Books Online still uses the term OLE Automation, and the stored procedures are prefixed with sp_OA.
The only side affect is that the user/role executing this sp must have permissions to execute the SP_OA stored procedures within the master db.
hope this helps
July 10, 2003 at 6:35 am
ok sir, here you go couple of things. you do need permissions to xp_cmdshell. also, depending on how it is running make sure that the account under the non-sysadmin job step proxy account settings is the domanin account that the service runs under. also make sure you have permissions to all folders and or apps you are trying to run. lastly, run dts from actual command prompt on the server, make sure taht no windows pop up asking fro information from any application you are running. hope this helps. in below replace ***** with values for your server, password, user and name of package.
create proc dbo.usp_execute_dts @strTxtFile as varchar(200),
@strMonth as varchar(2),
@strYear as varchar(4),
@strExcelFile as varchar(200)
as
Set @strTxtFile = Replace(@strTxtFile, '''', '''''')
Set @strMonth = Replace(@strMonth, '''','''''')
Set @strYear = Replace(@strYear, '''','''''')
Set @strExcelFile = Replace(@strExcelFile, '''', '''''')
DECLARE @myline as varchar(7000)
--Execute DTS Package
SET @myline = 'dtsrun /S******** /U******** /P******** /N"*********"
/AgvImportTextFileName:8=' + @strTxtFile + ' /AVarMonth:8=' + @strMonth + ' /AVarYear:8=' + @strYear + '
/AgvImportExcelFileName:8=' + @strExcelFile + ''
EXEC master..xp_cmdshell @myline
July 10, 2003 at 7:25 am
frere
I have tried both examples in a recent project
1. xp_cmdshell
2. OLE Automation Stored Procedures
Using xp_cmdshell didn't give me the flexibility of responding to failures,
it will write it to the event log using the /W True option but my proc
needed a response if the DTS Package fialed.Each OLE Automation stored
procedure returns an integer code that is the HRESULT returned by the
underlying OLE Automation operation. If HRESULT is equal to 0, then
everything is okay, a nonzero HRESULT indicates OLE error.
Here is an example of OLE Automation using the LoadFromSQLServer Method
Here is the Method signature:
Public Overridable Sub LoadFromSQLServer(ByVal ServerName As String,
Optional ByVal ServerUserName As String = Nothing,
Optional ByVal ServerPassword As String = Nothing,
Optional ByVal Flags As DTS.DTSSQLServerStorageFlags = 0,
Optional ByVal PackagePassword As String = Nothing,
Optional ByVal PackageGuid As String = Nothing,
Optional ByVal PackageVersionGuid As String = Nothing,
Optional ByVal PackageName As String = Nothing,
Optional ByRef pVarPersistStgOfHost As Object = "")
--Permissions
--Only members of the sysadmin fixed server role can execute sp_OACreate, sp_OAMethod
CREATE PROCEDURE dbo.sp_ExecuteDts
@ServerName varchar(30),
@PackageName varchar(100)
AS
--These variables can be hard coded if you don't plan on reusing the proc
--@ServerName = Name of the Server
--@PackageName = Name of the Dts Package
DECLARE @object int
DECLARE @hr int
DECLARE @Flags int
SET @Flags=256 -- 0 Use SQL Server Authentication or 256 Use Windows Authentication
--create a package object
EXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUT
if @hr <> 0
BEGIN
RAISERROR ('Error creating DTS.Package',16,1)
RETURN
END
--should be on one line
EXEC @hr = sp_OAMethod @object, 'LoadFromSQLServer', NULL,@ServerName,'','',@Flags,'','','','@PackageName',''
IF @hr <> 0
BEGIN
exec sp_displayoaerrorinfo @object, @hr
RAISERROR ('Error LoadFromSQLServer',16,1)
RETURN
END
EXEC @hr = sp_OAMethod @object, 'Execute'
IF @hr <> 0
BEGIN
RAISERROR ('Execution failed',16,1)
RETURN
END
GO
--///////////////
Here is an example using xp_cmdshell, this proc must be stored in the master database
CREATE PROCEDURE dbo.ExecuteDts
AS
EXECUTE xp_cmdshell "DTSRun /S Server3 /E /N dtsAdd"
July 10, 2003 at 7:42 am
It is fighting me every step of the way!! I can cut & Paste my Dtsrun String to the command prompt and it will run fine, but when I try to run it from the SP, I now get errors saying that it can't open the data source. (BTW: The front end for this program that I am working on is an Access Project. Before..I was trying to insert the "Master.." before the xp_cmdshell statement in the Access Project, but it would never save for some reason. However, when I inserted it through Enterprise Manager it kept it.)
Thanks for everyone's input. I want to try to keep this as simple as possible, since I have very basic Sql Server skills. Also, this will only be a program that I use and I would like to try to get the DTS command to work, since I am so close, but I will look over the other options.
Thanks!!
fryere
fryere
July 10, 2003 at 10:18 am
One thing I was thinking if you haven't checked already is the user for the database link you're using in Access that connects to the SQL db to run the stored procedure. If the stored proc runs from Query Analyzer but not from Access, the link maybe using a different user connection and therefore different permissions.
July 10, 2003 at 11:06 am
Dean,
Access will run the SP, but for some reason it will not update the DTSRUN string in the SP. It's wierd, I can type in the changes and it will prompt me to save, but it never saves.
Thanks,
fryere
fryere
July 10, 2003 at 11:45 am
If you are having problems running it from the stored profcedure, but you can run it fine from Query Analyzer, check to see what account your service is logging in as. You may not have access to the DTS file with the account that the service uses.
July 10, 2003 at 1:42 pm
Well, this is odd behavior. I'm guessing you modified the Stored Procedure using SQL Enterprise Manager where you signed in as the System Admin. That didn't let you paste the "xp_cmdshell 'DTSRun /~Z0xBLAH-BLAH-BLAH...'" command into it? Are you able to run a DOS Batch file from the Stored Proc? If so, you could try pasting in the DTSRun command into the bat file, then execute the bat file.
Another thought I had would be to use the "sp_start_job" stored procedure in the msdb database to launch a job that runs your DTS package. The advantage would be that the package would run under the SQL Sever Agent service's profile and it would run asynchronously (if that's an advantage). In this case, you may want to send an email to the user if it fails. I'm not sure how Access forms handle data being updated underneath it, so don't know if this is viable.
Best of luck,
Dean
July 10, 2003 at 3:42 pm
Here's how I run a DTS pkg from an Access event:
Open Notepad and type in the DTSRun command line, i.e.
dtsrun.exe /S SrvrName\SrvrInstance /E /N DTS_pkg_Name
Then save as, for example, c:\DTS.bat.
Finally create a macro in Access, using RunApp as the action and c:\DTS.bat as the command line. Alternatively, you could put it in a sub or function, using: Call Shell("C:\DTS.bat",1).
It's not fancy, but it works.
July 10, 2003 at 3:51 pm
You may think the pass-through query in Access to call stored procedure in SQL Server too.
July 11, 2003 at 1:49 pm
Bill Nye,
Good sugguestion, nice & simple. I think I can make that one work.
Thanks for everyone Input.
fryere
fryere
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply