Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

pass parameters to dts using xp_cmdshell rundts Expand / Collapse
Author
Message
Posted Wednesday, August 20, 2003 4:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 14, 2004 4:50 AM
Points: 37, Visits: 1
hi all,

can someone pls show me how to pass two parameters to a dts package using something like...

exec master..xp_cmdshell 'dtsrun /S Server_Name /N Package_Name /E /A ParametersHere'

then once that is done, how can I then insert those values into a table (from within the DTS package) using something like...

select
col1=parameter1,
col2=parameter2
into
Database_Name.dbo.Table_Name

I'm thinking I'll probably need to set up a couple of global variables in the DTS package as well?

thanks, sho.

Edited by - shoayb on 08/20/2003 04:39:58 AM



Post #15463
Posted Wednesday, August 20, 2003 6:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 14, 2004 4:50 AM
Points: 37, Visits: 1
hi again all,

i've figured it out, here's a response to my question...


IN THE DTS DESIGNER
a) Create a server/database connection
b) Create a SQL Task thing in DTS designer
c) Enter the following SQL Statement...
insert into Table_Name values (?)
(make sure the table exists)
d) Click on the Parameters button and select a global variable you've already created

Now when you run the DTS package through the DTS designer the ? (in the SQL Statement)will be replaced by the global variable value.
If you run it through, say, query analyser using:
exec master..xp_cmdshell 'dtsrun /S Server_Name /N Package_Name /E /A Global_Variable_Name=xyz'
then the ? will be replaced with xyz.

sho

Edited by - shoayb on 08/20/2003 06:04:11 AM

Edited by - shoayb on 08/21/2003 10:52:06 AM



Post #75727
Posted Thursday, August 21, 2003 10:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 14, 2004 4:50 AM
Points: 37, Visits: 1
hi again,

i've had a few emails from people and i've changed the sql syntax in my prev post so it should work.

also, here's a stored procedure and some ASP to run the stored procedure should you want.

1) STORED PROCEDURE
create procedure execute_dts_package @dts_package_name varchar(100), @parameter_string varchar(100) as

declare @exec_string as varchar(255)
set @exec_string = 'master..xp_cmdshell ' + '''dtsrun /S SQL_Server_Name /N ' + @dts_package_name + ' /E /A ' + @parameter_string + ''''
exec (@exec_string)

go


2) ASP Page
<%@Language=VBScript %>
<%
call sub_execute_dts_package ("DTS_Package_Name", "Parameter_Name=Parameter_Value")

sub sub_execute_dts_package (p_dts_package_name, p_parameter_string)
Dim objConn, objerr, exec_string
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Driver={SQL Server};Server=SQL_Server_Name;Database=Database_Name;Uid=User_ID;Pwd=Password"

exec_string = "exec execute_dts_package " & "'" & p_dts_package_name & "'," & " '" & p_parameter_string & "'"

objConn.Execute exec_string
Response.Write "DTS Package " & p_dts_package_name & " succeeded"
end sub
%>



sho



Edited by - shoayb on 08/22/2003 07:16:55 AM



Post #75728
Posted Thursday, November 20, 2008 10:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 7:48 AM
Points: 3, Visits: 97
I am running the following:
master..xp_cmdshell 'dtsrun /S ricmssql02 /U admindw /P letmein /N "scott - debug"'
but also send parameters. I am seeing the following to do that:
/A global_parameter_name:typeid=value
And also that the whole thing can be repeated to send multiple.
My questions are; What is the delimiter for multiple? What are the values for typeid?
I have Googled and looked at msdn and can't find a good explanation.
Post #605994
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse