DTS parameters not being passed to SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

DTS parameters not being passed to SP

Author

Topic

sbrazier
Starting Member

19 Posts

Posted - 2009-12-04 : 14:52:56

I've created a stored procedure(1) that creates a job to execute the DTS package sending in parameters. The DTS package calls a stored procedure(2) passing some parameters to it. I've tested my stored procedure(1) and it works. The job is created and the dts package executes and runs stored procedure(2) which runs successfully with the parameter values set at design time. The runtime parameters are not being used when the stored procedure(2) executes.

Here is the code I used to execute the stored procedurer to create the job:

EXEC [dbo].[DSO_SyExecSQLASyncSP]
@dts = N'[DTS Package Name]',
@user = N'[user]',
@user_email = N'[users email]',
@claim_yr = [claim year],
@dept_code = N'[dept]'

here's the code for DSO_SyExecSQLAsyncSP
PROCEDURE [dbo].[DSO_SyExecSQLASyncSP]
(
@dts varchar(128),
@user varchar(15),
@user_email varchar(150),
@claim_yr int,
@dept_code char(6)

)
AS
declare @jid uniqueidentifier
declare @cmd varchar(4000)
set @cmd = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\dtsrun.exe" /S(local) /N' + @dts + ' /W 0 /E '
-- Specifiy variables values to be passed to DTS through DTS Run
set @cmd = @cmd + '/A claim_year:3=' + cast(@claim_yr as char(4)) + ' '
set @cmd = @cmd + '/A user":8=' + @user + ' '
set @cmd = @cmd + '/A user_email:8=' + @user_email + ' '
set @cmd = @cmd + '/A dept_code:8=' + rtrim(@dept_code) + ''
-- Create a unique job name
declare @jname varchar(128)
set @jname = rtrim(@dts) + 'DTS'
-- 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

Would appreciate any help! I have several processes that have to be created this way and being in a time crunch isn't helping.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-12-04 : 15:00:59

Why would you write a stored procedure to create a job to run a dts that executes a stored procedure???

Wouldn't it make more sense to simply call the stored procedure at the end of the chain?

In any event, how are you passing the parameters from the dts package to the stored proc?

sbrazier
Starting Member

19 Posts

Posted - 2009-12-04 : 15:53:36

The stored procedure(2) that the dts package calls starts a process that will take some time to run. The stored procedure(1), which creates the job, is called from a windows application. I have stored procedure(2) send an email to the user when the process is completed.

I have the dts package global variables mapped to the exec statement in my sql task.
EXEC dbo.HITS_dso_Dept_Process_Imports ?,?,?,?

sbrazier
Starting Member

19 Posts

Posted - 2009-12-04 : 16:09:37

Got it working. My changes to the command line

declare @cmd varchar(4000)
set @cmd = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\dtsrun.exe" /S "(local)" /N "' + @dts + '" /L "G:\Department Files\CPC\dso\HITS_dso\DTSLogs\dts_logs.txt" /W "-1" /E '
-- Specifiy variables values to be passed to DTS through DTS Run
set @cmd = @cmd + '/A "@claim_year":"3"="' + cast(@claim_yr as char(4)) + '" '
set @cmd = @cmd + '/A "@user":"8"="' + @user + '" '
set @cmd = @cmd + '/A "@user_email":"8"="' + @user_email + '" '
set @cmd = @cmd + '/A "@dept_code":"8"="' + rtrim(@dept_code) + '"'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources