DTS Parameters are not being passed
-
I've created a stored procedure that generates a job to run a DTS package.
The DTS Package takes two parameters (both integers).
Here's the code for the stored procedure
CREATE PROCEDURE [dbo].[sp_dso_ExecProcessMatchImportDTS]
(
@user numeric(18,0),
@file_type integer
)
AS
declare @jid uniqueidentifier
-- Specifiy DTS to be executed
declare @dts varchar(128)
set @dts = 'DTS_ProcessMatchImports'
--Initialize command
declare @cmd varchar(4000)
set @cmd = 'C:\Program Files\Microsoft SQL Server\80\Tools\Binn\dtsrun.exe /S "(local)" /N "' + @dts + '" /W "-1" /E '
-- Specifiy variables values to be passed to DTS through DTS Run
set @cmd = @cmd + ' /A "@user":"3"="' + rtrim(cast(@user as char(20))) + '"'
set @cmd = @cmd + ' /A "@file_type":"3"="' + rtrim(cast(@file_type as char(20))) + '"'
-- Create a unique job name
declare @jname varchar(128)
set @jname = 'ProcessMatchImportsDTS'
-- Create job
exec msdb.dbo. sp_add_job
@job_name = @jname,
@enabled = 1,
@category_name = 'DSO',
@delete_level = 1,
@job_id = @jid OUTPUT
exec msdb.dbo.sp_add_jobserver
@job_id = @jid,
@server_name = '(local)'
exec msdb.dbo.sp_add_jobstep
@job_id = @jid,
@step_name = 'Execute DTS',
@subsystem = 'CMDEXEC',
@command = @cmd
-- Start job
exec msdb.dbo.sp_start_job @job_id = @jid
This is the cmd that the stored procedure generates
C:\Program Files\Microsoft SQL Server\80\Tools\Binn\dtsrun.exe /S "(local)" /N "DTS_ProcessMatchImports" /W "-1" /E /A "@user":"3"="2" /A "@file_type":"3"="2"
When I execute this via SQL Query Analyzer with parameters of 3,3,
I get the following message.
Job 'ProcessMatchImportsDTS' started successfully.
My DTS package calls a stored procedure with the parameters I sent into DTS Package.
exec sp_ProcessMatchImports ?,?
I have specified my global variables in the DTS Package as will as selected them as paraemeters for the store procedure execute call.
I've checked all parameters to ensure they are all exactly alike and well as the DTS Package for case-sensitivity
I've set the default values for the global variables to 0 and do not have the explicit Global variables checked
Here is the stored procedure
CREATE PROCEDURE dbo.sp_ProcessMatchImports (@user integer, @file_type integer)
as
begin
declare @strsubject varchar(150)
if (@file_type = 0) begin
select @strsubject = 'This is the file type sent into the store procedure 0'
end else begin
select @strsubject = 'This is the file type sent into the store procedure 1'
end
EXEC master.dbo.xp_sendmail @recipients=N'MYEMAILADDRESS', @subject= N'Testing Match Import File Type', @message= @strsubject
end
The stored procedure is being executed and I'm getting the email but it is returning the default 0 not 1
I would appreciate any help I can get in solving this problem. Am I missing a step, is there something that is suppose to be checked.
Look forward to some feed back!!!
-
I created a very simple DTS Package that that inserts the global variable value into a table and it is working without any problems. Some things to check:
1. Are there multiple versions of the DTS package? If so, then most desired version may not be executed, so delete all but the desired version.
2. Does the DTS package have a "Dynamic Properties Task"? This is required to pass global variable values.
3. Is a "on success" workflow defined between the "Dynamic Properties Task" and the "SQL Task" that executes the stored procedure?
Here is the test case without the DTS package source.
CREATE TABLE GlobalVariableSave
( customerId nvarchar (5)
, RunTsdatetime default ( current_timestamp )
)
GO
create procedure GlobalVariableSave_sp
(@customerId nvarchar(5) )
as
set nocount on
insert into GlobalVariableSave (customerId) values (@customerId )
go
-- Create the DTS Package
-- Test SQL
select * from GlobalVariableSave
exec master.dbo.xp_cmdshell 'dtsrun.exe /S "(local)" /N "GlobalVariableTest" /W FALSE /E /ACustomerId:8=ALFKI'
select * from GlobalVariableSave
SQL = Scarcely Qualifies as a Language
-
So I have to have a Dymanic Properties Task to execute the SQL Task
Everything that I have read on this shows the Dymanic Properties getting the global variables from a file or table.
Is there any other way to capture the parameters that I'm sending into the DTSPackage execution without creating a table.
Susan
-
I just ran a test an you do NOT need a Dynamic Properties Task unless a global variable is used to set a connection property.
The table "GlobalVariableSave" is only for diagnostics purposes and was a easy means to determine if the stored procedure was receiving the global variable value that was passed.
Send me an email at cfederl@yahoo.com with you package in *.dts format and I will take a look.
SQL = Scarcely Qualifies as a Language
-
I ran another test using the sp_dso_ExecProcessMatchImportDTS stored procedure but with my DTS package and passing the value of the global variable worked fine. This leads me to believe that the problem is with your DTS package.
SQL = Scarcely Qualifies as a Language
-
I want to say thank you for all you responses...
I knew that this was working before are at least I thought it was... But of course the times this DTS Package was ran it was passing in the same global variable values as the default was set... That was dumb of me to not set those variables to 0's.
However, I've made no changes to the DTS Pacakge and now it's working. Go figure!!!
I one thing that has been done, is a reboot of the server.
Have you ever heard of an occurrance like this..
Kindda makes me wonder!!!
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply