使用 tsql 使用 ssis 步骤创建作业 [英] creating job with ssis step using tsql

问题描述

我想使用存储过程创建 sql server 作业，但我似乎无法正确完成.集成服务目录 -> SSIDB -> Cat1 ->Projects->999->Packages->999.dtsx

I would like to create sql server job using stored procedure and I can't seem to get it right. Integration Service Catologs -> SSIDB -> Cat1 ->Projects->999->Packages->999.dtsx

在包选项卡上的以下脚本的第 1 步属性中，服务器:和包:"为空，我需要填充这些并将 32 位设置为 true

In step 1 properties of below script on Package tab "Server: and Package:" are empty, I need to populate these as well as set 32bit to true

以下是我得到的，提前致谢

Below is what I got, thanks in advance

EXECUTE msdb..sp_add_job @job_name = 'Job 1', @owner_login_name = SUSER_NAME(), @job_id = @JobId OUTPUT

EXECUTE msdb..sp_add_jobserver @job_id = @JobId, @server_name = @@SERVERNAME

EXECUTE msdb..sp_add_jobstep @job_id = @JobId, @step_name = 'Step1',@database_name = DB_NAME(), @on_success_action = 3 ,@subsystem = N'ssis'
, @command = N' "\SSISDB\Cat1\999\999.dtsx" @SERVER=N"@ServerName"'

EXECUTE msdb..sp_add_jobstep @job_id = @JobId, @step_name = 'Step2', @command = 'execute msdb..sp_delete_job @job_name="Job 1"'

EXECUTE msdb..sp_start_job @job_id = @JobId

推荐答案

如果其他人遇到类似情况，找出如何务实地创建作业的最简单方法是使用 UI(服务器代理 -> 新作业)创建它.创建您想要查看的所有内容，保存它，然后右键单击作业 Script Job As -> Create To -> New query 和 sql server 会将作业导出为查询，以便您可以看到您需要做什么.

if anyone else comes across similar situation, easiest way to figure out how to create a job pragmatically is to create it using UI (Server Agent -> New Job). create everything you want to see, save it, then right click at the job Script Job As -> Create To -> New query and sql server will export the job as a query so you can see what you need to do.

这篇关于使用 tsql 使用 ssis 步骤创建作业的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！