How to Create a Job in SQL Server using SSIS?

In this session, we are going discuss how to create a SQL job, connect the Job with an 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, Load Data From Database To Text File Using SSIS Package

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 “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 the “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 the “Property” option to set the job configurations.

STEP 3 - General Page

On the general page, you should give a name for Scheduling the job and then click “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 your SSIS package file, which 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 the “Schedule” option from the menu
2. You can see that “New” button at the bottom of the window. Click on it.
3. Enter the name to “Schedule a JOB”
4. Mention the timing of 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 the 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 an 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

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

 

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

I hope this helps.

Please let me know if you need anything.

Thanks.