这是原始代码:
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestStep',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'SSIS',
@command=N'/ISSERVER "\"\SSISDB\FolderName\ProjectName\PackageName.dtsx\"" /SERVER localhost /ENVREFERENCE 9 /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E',
@database_name=N'master', ...
我想做这样的事情(ENVREFERENCE
用动态值替换长字符串后的值):
DECLARE @myVariable int
SET @myVariable = 10
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestStep',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'SSIS',
@command=N'/ISSERVER "\"\SSISDB\AccuCenter\AccuCenterDBImport\VehicleMake.dtsx\"" /SERVER localhost /ENVREFERENCE ' + @myVariable + N' /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E',
@database_name=N'master'
您不能将整个变量放在一个变量中吗?
DECLARE @myCommand nvarchar(max)
SET @myCommand = N'/ISSERVER "\"\SSISDB\AccuCenter\AccuCenterDBImport\VehicleMake.dtsx\"" /SERVER localhost /ENVREFERENCE '
+ LTRIM(STR(@myVariable))
+ N' /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E'
顺便说一句,您可以完全根据自己的需要进行构建。
然后在调用sp_add_jobstep时使用它:
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep
@job_id=@jobId,
@step_name=N'TestStep',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'SSIS',
@command=@myCommand
@database_name=N'master'
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句