SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


pass parameters to dts using xp_cmdshell rundts


pass parameters to dts using xp_cmdshell rundts

Author
Message
shoayb
shoayb
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 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



shoayb
shoayb
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 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



shoayb
shoayb
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 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



mark.umbach
mark.umbach
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 119
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search