使用 C# 为 SSIS 包创建 SQL 作业
3.00/5 (5投票s)
如何创建 SQL Server 代理作业来调度 SSIS 包
引言
有时，用户希望使用 C# 应用程序来调度 SSIS 包的执行。 在这里，我将介绍如何通过 C# 应用程序来调度执行 SSIS 包的任务。
背景
什么是 SQL 作业 / SQL Server 代理作业？
每当我们想要在给定日期和时间调度任何任务时，我们都会使用 SQL Server 代理作业。 您可以从下面的示例更好地了解 SQL Server 代理作业
如果您想在下班后每天备份所有公司服务器，或者想在任何给定时间运行任何特定任务，您可以自动执行此任务。 计划在周一至周五 22:00 之后运行备份； 如果备份遇到问题，SQL Server 代理可以记录该事件并通知您，该通知也将是一封电子邮件。
Using the Code
SQL Server 代理作业是通过在 SQL Server 中执行以下步骤创建的
- 在 SQL Server Management Studio 的对象资源管理器中，右键单击 SQL Server 代理。
- 右键单击SQL Server 代理 -> 新建 -> 作业
- 填写所需的详细信息，然后单击“确定”以创建作业
在这里，我专注于使用 C# 创建 SQL Server 代理作业来运行 SSIS 包。 因此，我使用的步骤是
- 创建用于创建 SQL 作业的 SQL 查询
- 在 SQL 查询中，应创建唯一的
JobId。 然后，作业调度将在此JobID上发生 - 传递 SSIS 包路径和该 SSIS 包中使用的变量
- 调度 SQL 作业有两种调度类型
- 运行一次：用户将创建 SQL 作业，该作业仅在给定的开始日期运行一次。 在下面提到的代码中将
JobSchedule的名称设置为RunOnce。 运行一次的代码是sQuery = sQuery +@" EXEC msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'RunOnce', @freq_type=1, "; sQuery = sQuery + " @active_start_date=" + startDate + ", "; sQuery = sQuery + @" @active_end_date=99991231, @active_start_time=" + strartTime + ""; sQuery = sQuery + @" EXEC msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'"; - 每天运行：有时，用户要求特定的 SQL 作业每天在特定时间运行。 为此，我们必须相应地设置一些变量。 在下面提到的代码中将名称设置为 RunDaily 且将 @freq_type 设置为 4。 每天运行的代码是
sQuery = sQuery + @" EXEC msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'RunDaily', @freq_type=4, @freq_interval=1, "; sQuery = sQuery + " @active_start_date=" + startDate + ", "; sQuery = sQuery + @" @active_end_date=99991231, @active_start_time=" + strartTime + ""; sQuery = sQuery + @" EXEC msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'";
用户可以根据选择每天运行或运行一次来编写代码。 以下代码也包含上述两个条件。
在这里，我们需要一些变量数据。 它们是
PackagePath：SSIS 包路径JobName：将要创建的作业的名称DatabaseName：数据库的名称Variables：将传递到 SSIS 包中的 SSIS 包变量IsRunDaily：基于此变量将为 RunDaily 或 RunOnce 创建 SQL Server 作业StartTime：作业开始时间。 格式应为 hhmmss，并且应为整数StartDate：作业开始日期，这将是作业的开始日期。 格式应为 YYYYMMDD，并且应为整数。- 执行该 SQL 查询以创建 SQL 作业
- 如果 SQL 查询成功执行，则表示 SQL 作业已成功创建。
用于创建 SQL 作业的 C# 代码是
public string CreateSQLJob()
{
string result = string.Empty;
//Package which will be executed by SQL Job
string PackagePath =
"C:\Documents and Settings\Sandeep\My Documents\SSISPackage\SsisPackage.dtsx";
//Job Name which will be created
string jobName = "SQLJob_RunSSIS";
string sQuery = string.Empty;
string databaseName = "MyDatabase"; //name of the database to which
//the job step command execution is confined.
string EmpCode = "3";
string EmpName = "Sandeep";
string IsRunDaily = "True"; // Run the SQL Job Daily or Not
//StartTime should be Integer and in the format of hhmmss.
//Here 62517 means hh : 6, mm : 25 & ss : 17
Int32 strartTime = 62517;
//StartDate should be Integer and in the format of YYYYMMDD.
//Here 20130927 means YYYY : 2013, MM : 09, DD : 27
Int32 startDate = 20130927;
//Creating SQL Query for Create SQL Job on the DB Server
sQuery = @" DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'" +
jobName + "', @job_id = @jobId OUTPUT ";
sQuery = sQuery + @" EXEC msdb.dbo.sp_add_jobstep
@job_id=@jobId, @step_name=N'Do SQL Stuff',
@step_id=1,
@subsystem=N'SSIS', ";
sQuery = sQuery + @" @command=N'/FILE """ + PackagePath + "";
sQuery = sQuery + @""" /CHECKPOINTING OFF /SET "
"\Package.Variables[User::EmpCode].Properties
[Value]"";" + EmpCode + "";
sQuery = sQuery + @" /SET ""\Package.Variables
[User::EmpName].Properties[Value]"";" +
EmpName + " /REPORTING E;', ";
sQuery = sQuery + @" @database_name=N'"+databaseName+"', ";
sQuery = sQuery + @" @flags=0 ";
if (IsRunDaily == "False") {
sQuery = sQuery +@"
EXEC msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'RunOnce',
@freq_type=1, ";
sQuery = sQuery + " @active_start_date=" + startDate + ", ";
sQuery = sQuery + @" @active_end_date=99991231,
@active_start_time=" + strartTime + "";
sQuery = sQuery + @" EXEC msdb.dbo.sp_add_jobserver
@job_id = @jobId, @server_name = N'(local)'";
}
else
{
sQuery = sQuery + @" EXEC msdb.dbo.sp_add_jobschedule
@job_id=@jobId, @name=N'RunDaily',
@freq_type=4,
@freq_interval=1, ";
sQuery = sQuery + " @active_start_date=" + startDate + ", ";
sQuery = sQuery + @" @active_end_date=99991231,
@active_start_time=" + strartTime + "";
sQuery = sQuery + @" EXEC msdb.dbo.sp_add_jobserver
@job_id = @jobId, @server_name = N'(local)'";
}
//Execute SQL Query
int rowsEffected = executeCommandQuery(sQuery,CommandType.Text);
if (rowsEffected != -9)
{
result = "Job Created Successfully.";
}
else {
result = result = "Job Already exists!! or Some error occurred.";
}
return result;
}
protected int executeCommandQuery(String sQuery, CommandType commandType)
{
// this function will execute the query opening
// the connection and using the command
//sQuery can be the name of storedProcedure or simple Query String
//commandType : sqltype or simple query String
//the final execute method of the command
//will be decided by the calling function
int rowEffected;
protected SqlConnection _connection;
_connection.ConnectionString = ConfigurationManager.ConnectionStrings
["SQLConnStr"].ConnectionString; //From Web.config
try
{
_connection.Open();
SqlCommand command = new SqlCommand(sQuery, _connection);
command.CommandType = commandType;
rowEffected = command.ExecuteNonQuery();// the query has been
//already passed only execute it
_connection.Close();
}
catch (Exception ex)
{
rowEffected = -9;
_connection.Close();
throw ex;
}
finally
{
_connection.Close();
}
return rowEffected;
}
在上面的代码中，executeCommandQuery() 函数用于执行 SQL 语句，该语句用于创建 SQL Server 代理作业。
创建 SQL Server 代理作业后，您可以在 SQL Server Management Studio 上查看创建的 SQL 作业。 使用以下步骤查看 SQL 作业
- 打开对象资源管理器
- 展开 SQL Server 代理
- 展开“作业”以查看您创建的已创建 SQL Server 作业。
- 您还可以通过右键单击该 SQL Server 作业并选择“属性”来查看 SQL Server 作业的属性。
一些重要的 SQL 查询，用于获取 SQL Server 代理作业的详细信息
要获取可用的作业详细信息，请使用以下 SQL 查询
Select * from msdb.dbo.sysjobs where name = 'Job_Name'
要获取作业步骤的详细信息，即 SQL Server 代理作业中使用的任务，这将通过 Job_id 获取。 使用以下 SQL 查询
Select * from [msdb].[dbo].[sysjobsteps] _
where job_id = 'CB36BECC-FE3A-4F03-A969-BD0C5A200690'
要获取 SQL Server 代理作业的调度详细信息。 这将通过 Job_id 获取。 使用以下 SQL 查询
Select * from msdb.dbo.sysjobschedules _
where job_id = _'CB36BECC-FE3A-4F03-A969-BD0C5A200690'
要获取 SQL Server 代理作业的活动详细信息。 这将通过 Job_id 获取。 使用以下 SQL 查询
Select * from msdb.dbo.sysjobactivity _
where job_id = 'CB36BECC-FE3A-4F03-A969-BD0C5A200690'
关注点
我试图使用 C# 调用/执行 SSIS 包。 为此，我找到了多种方法。 在我之前的文章中，我已经描述了如何使用 C# 和存储过程来执行 SSIS 包。 在这里，我描述了如何使用 SQL Server 代理作业来调用 SSIS 包。 在我的下一篇文章中，我将编写一种使用 Windows Profiler 调用 SSIS 包的方法，该 Profiler 将使用 C# 创建。
使用 C# 和存储过程执行 SSIS 包的先前链接是
历史
- 2013 年 10 月 1 日：首次发布