Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 17th, 2003, 11:48 AM
Authorized User
 
Join Date: Jun 2003
Location: Richardson, Texas, USA.
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default Execute DTS with input parameters from a SP

I need to execute a dts package and pass it some input parameters. I can find code to execute a DTS packages but not with passing in parameters. Does anyone know how to do this?

Thanks,

David
 
Old July 18th, 2003, 03:28 AM
Authorized User
 
Join Date: Jun 2003
Location: Edinburgh, , United Kingdom.
Posts: 87
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi

I'm not sure in what context you want to use this but I'll give you an example of the way we use parameters in our DTS. We select the parameter values from a table in the database.

Right click on the white background in your DTS and click package properties. Click on the global variables TAB and create your variables – Name, Type, Value (default value)

To use it drag an 'Execute SQL Task' object to your DTS. View the properties of the task and in the ‘SQL statement’ window create the SQL to get your parameters from your table. Click on the ‘Parameters’ button, click on the ‘Output Parameters’ TAB, create your mapping to the global variable created earlier by using the drop down list in ‘Output Global Variables’.

I hope this helps.

Regards

Nickie
 
Old July 18th, 2003, 08:27 AM
Authorized User
 
Join Date: Jun 2003
Location: Richardson, Texas, USA.
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Nickie,

Thanks for your reply. I need to execute the DTS package from a stored procedure and pass values into the DTS package. I have got my parameters setup in the DTS package I just do not know how to pass the parameters in. I have been using the following procedure to execute DTS packages but it does not provide a way to pass in user defined parameters.



/****** Object: Stored Procedure dbo.spExecuteDTSPackage Script Date: 2/18/2003 5:27:58 AM ******/
CREATE PROCEDURE [dbo].[spExecuteDTSPackage]
@in_varPackageName Varchar(100),
@in_varServerName varchar(20),
@out_chrErrorType Char(3) = 'SYS' OUTPUT,
@out_intErrorCode Int =0 OUTPUT
AS
DECLARE @object int,
@hr int,
@src varchar(255),
@desc varchar(255),
@val as varchar(100)

-- LOAD A VARIABLE WITH A NEEDED PROCEDURE CALL..
SELECT @val = 'LoadFromSQLServer(' + @in_varServerName + ', "", "", 256, , , , ' + @in_varPackageName + ')';
-- CREATE THE OBJECT.
EXEC @hr = master..sp_OACreate 'DTS.package', @object OUT;
IF @hr <> 0
BEGIN
      -- Trapping error if object not created
        EXEC master..sp_OAGetErrorInfo @object, @src OUT, @desc OUT
        SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
        RETURN
END
-- GET THE DTS PACKAGE REFRENCE.
EXEC @hr = master..sp_OAMethod @object, @Val, NULL;
IF @hr <> 0
BEGIN
       -- Trapping error if Execute failed
      EXEC master..sp_OAGetErrorInfo @object, @src OUT, @desc OUT
       SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
      RETURN
END
-- EXECUTE THE DTS PACKAGE.
EXEC @hr = master..sp_OAMethod @object, 'Execute'
IF @hr <> 0
BEGIN
       -- Trapping error if Execute failed
      EXEC master..sp_OAGetErrorInfo @object, @src OUT, @desc OUT
       SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
      RETURN
END
GO



Thanks,

David
 
Old July 18th, 2003, 09:20 AM
Authorized User
 
Join Date: Jun 2003
Location: Edinburgh, , United Kingdom.
Posts: 87
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi

Not sure if/how you can do that with a direct call to a DTS.

But you can do it the following way....
In your Stored Procedure before the call to your DTS you could set the value that you want to pass to your DTS in a table. This could be a new table with only 1 row that stores all the values you want to pass to the DTS. Set the value before your DTS call then the first step of your DTS you could retrieve these values into your Global variable list then use them as you please in your DTS.

I hope this helps.

Regards

Nickie
 
Old July 18th, 2003, 09:32 AM
Authorized User
 
Join Date: Jun 2003
Location: Richardson, Texas, USA.
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks Nickie,

I will use that approach if I can not find a way to pass the parameters. The information that needs to be passed is a password. I am trying to provide away for a person to be able to reset users passwords without giving that person sysadmin privileges. I can accomplish this using DTS since it allow me to run a process as sa.

Thanks for your help,

David
 
Old April 27th, 2005, 07:41 AM
Registered User
 
Join Date: Apr 2005
Location: Villa Ballester, Buenos Aires, Argentina.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to Rothariger Send a message via MSN to Rothariger
Default

Hello, te method to pass global variblas to a DTS is this...

Code:
EXEC @hr = sp_OASetProperty @oPKG, 'GlobalVariables("globalvariablename").Value', value
IF @hr <> 0
BEGIN
    PRINT '***  GlobalVariable Assignment Failed'
    EXEC master..sp_displayoaerrorinfo @oPKG, @hr
    RETURN
END
thanks to your code, i found my problem...
 
Old March 22nd, 2006, 05:36 AM
Registered User
 
Join Date: Mar 2006
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Any one know how to execute/calling a DTS (in database server by using store procedure) from web server? I tried to mapped drives but not successful. Please help..

Thank in advance.




Similar Threads
Thread Thread Starter Forum Replies Last Post
Report Services Wont execute My SP desireemm Reporting Services 1 December 21st, 2007 03:46 AM
Passing Parameters to Subreports containing SP ms1234 Crystal Reports 0 May 18th, 2007 11:46 AM
Calling an SP from a DTS Package? Gibi SQL Server DTS 1 April 18th, 2007 05:47 AM
optional parameters in SP yuqlin BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 7 July 13th, 2004 03:28 PM
Viewing List of Parameters SP sidneyfuerte SQL Server 2000 1 September 12th, 2003 02:17 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.