Create and Schedule a SSIS Job in SQL Server

In this tutorial, we are going discuss how to create and schedule a SSIS SQL Job in in SQL Server.

In this session, we are going discuss how to create a SQL job, connect the Job with a SSIS package, and schedule the job on the server.

Before we get started, if you want to know about the SSIS package implementation using SQL Server, please go through the following article: 

STEP 1

Follow the below steps to create a new job.

  • Open the SQL Server Management Studio on Windows and you should have the “SQL Server Agent” to implement the automated jobs.
  • Expand the “SQL Server Agent” in Object Explorer.
  • Right click on the Jobs and select the “New Job…”
  • You can see the “New Job” window

STEP 2

Here, you should give a valid name for the new job. And then click on “OK” button.

 

Finally, you can see that the job has been created on the “SQL Server Agent” list.

Once this is done, right click on the SQL Agent and refresh it.

Find the below screenshot for your reference (The job has been created).

Then click on “Property” option to set the job configurations.

SQL Server

STEP 3 - General Page

In the general page, you should give a name for Scheduling the job and then click on “OK”.

 

STEP 4

Then select the “Steps” option from the menu list.

You will see the below screen. Follow the below steps for the JOB configurations.

  1. Enter the step name
  2. The Type should be “SQL Server Integration Services Package”
  3. The Run as mode should be “SQL Server Agent Service Account”
  4. And change Package Source from SSIS Package to File System.
  5. Then select the your SSIS package file, where you have stored locally in your machine/server
  6. Then click on “OK”.

STEP 5 - Schedule Page

Follow the steps to schedule the JOB timing.

  1. Click on “Schedule” option from the menu
  2. You can see that “New” button in bottom of the window. Click on it.
  3. Enter the name to “Schedule a JOB”
  4. Mention the timing that, when the JOB wants to execute Daily, Weekly, Monthly or Yearly.
  5. Set the timing to execute your operation AM/PM
  6. If you want to continue your JOB till end, set “No end date” else set how long the JOB wants to execute.
  7. Click on “OK

Now, the created schedule will be displayed on the schedules list.

STEP 6 - Notification Page

It will send you the email notification alert, when the JOB succeeds or fails.

Click on the e-mail option and select the JOB mode. Then click on “OK”.

STEP 7

These are the properties to Start/Stop/View/Disable a JOB.

  • Start Job at Step - It will execute your JOB
  • Stop Job - The Job execution process will be stopped
  • View History - You can see the successes/failure history of a JOB
  • Enable - To enable the JOB for package execution
  • Disable - It will stop the JOB execution.
  • Properties - You can do the configuration for the JOB

 

SQL Server

Right click on the Job name and click the “Start Job at Step”.

The JOB will be starting to execute. 

Something like the below screenshot where you can see the updates.
 
SQL Server

Once this is complete, you can see the changes as per your SSIS logic.

Hope this helps.

Please let me know if you need anything.

Thanks.