Create SQL Job for SSIS Package by using C#

Introduction

Sometimes, user wants to schedule the execution of SSIS Package by using C# application. Here I am describing how to schedule a task for executing SSIS Package through C# application.

Background

What is SQL Job / SQL Server Agent Job?

Whenever we want to Schedule any Task at a given date and at a given time, we use SQL Server Agent Job. You can better understand about the SQL Server Agent Job from the below example:

If you want to back up all the company servers every weekday after hours, or want to run any specific task at any given time, you can automate this task. Schedule the backup to run after 22:00 Monday through Friday; if the backup encounters a problem, SQL Server Agent can record the event and notify you that notification will be an e-mail too.

Using the Code

SQL Server Agent Job is created by using the following steps in SQL Server:

• In SQL Server Management Studio, in Object Explorer, right click SQL Server Agent.
• Right Click SQL Server Agent -> New -> Job
• Fill the Required Details, then click on OK to create Job

Here I am focusing on the creation of SQL Server Agent Job to Run SSIS Package by using C#. So the steps which I used for that are:

1. Create SQL Query for creating the SQL Job
2. In the SQL Query, Unique JobId should be created. Then Job Scheduling will occur on that JobID
3. Pass the SSIS Package Path and Variables used in that SSIS Package
4. There are two scheduling types for Schedule SQL Job

• Run Once: User will create SQL Job which will run only once at the given Start Date. Set Name of the JobSchedule to RunOnce at the below mentioned code. The code for Run Once is:

  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)'";

• Run Daily: Sometimes, user has requirement that the specific SQL Job will run daily at a specific time. For that, we have to set some variables accordingly. Set Name to RunDaily and @freq_type to 4 at the below mentioned code. The code for run daily is:

  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)'";

User can write the code accordingly to the selection of Run Daily or Run Once. The below Code contains the above two conditions too.

Here we require some variables data. They are:

• PackagePath: SSIS Package Path
• JobName: Name of the Job which will be created
• DatabaseName: Name of the Database
• Variables: SSIS Package Variable that will be passed into the SSIS Package
• IsRunDaily: Variable based on that SQL Server job will Created for RunDaily or RunOnce
• StartTime: Job Start Time. It will be in the format of hhmmss and should be integer
• StartDate: Job Start Date, It will be the Start Date for the Job. It will be in the format of YYYYMMDD and should be integer.
• Execute that SQL Query to create SQL Job
• If SQL Query executed successfully, that means SQL Job is created successfully.

C# code to create SQL job is:

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;
        }

In the above code, executeCommandQuery() function is used to execute the SQL statement which is used for Create for SQL Server Agent Job.

After Creation of SQL Server Agent Job, you can view the Created SQL Job on your SQL Server Management Studio. Use the following steps to view the SQL job:

1. Open Object Explorer
2. Expand SQL Server Agent
3. Expand Jobs to view the Created SQL Server Job which you have created.
4. You will also view the Properties of SQL Server Job by right clicking on that SQL Server Job and Select Properties.

Some Important SQL Queries to get the details of the SQL Server Agent Jobs are:

To get the available Job Details, use the following SQL Query:

Select * from msdb.dbo.sysjobs where name = 'Job_Name'

To get the details of the Job Step, i.e. Tasks which are used in the SQL Server Agent Job, This will be fetched by Job_id. Use the following SQL Query:

Select * from [msdb].[dbo].[sysjobsteps] _
where job_id = 'CB36BECC-FE3A-4F03-A969-BD0C5A200690'

To get the details of the Scheduling of SQL Server Agent Job. This will be fetched by Job_id. Use the following SQL Query:

Select * from msdb.dbo.sysjobschedules _
where job_id = _'CB36BECC-FE3A-4F03-A969-BD0C5A200690'

To fetch Activity details of the SQL Server Agent Job. This will be fetched by Job_id. Use the following SQL Query:

 Select * from msdb.dbo.sysjobactivity _
where job_id = 'CB36BECC-FE3A-4F03-A969-BD0C5A200690'

Points of Interest

I was trying to Call/Execute the SSIS Package by using C#. For that, I had found multiple ways. In my previous article, I have already described about Executing SSIS Package by using C# and Stored Procedure. Here I described how to call SSIS Package by using SQL Server Agent Job. In my next article, I will write the way to call SSIS Package by using Windows Profiler that will be created by using C#.

Previous link for Execute SSIS Package by using C# and Stored Procedure is:

• http://www.codeproject.com/Articles/658959/Call-SSIS-Package-Through-Csharp

History

• 1^st October, 2013: Initial post