August 20, 2003 at 4:39 am
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
August 20, 2003 at 6:03 am
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
August 21, 2003 at 10:59 am
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
November 20, 2008 at 10:50 am
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply