Automatic Scheduling of a SQL Server Integration Services (SSIS) Package

In some actual scenarios it is necessary that a SSIS package execute automatically in some selected days of a week/daily/monthly and so on.

We can schedule packages deployed to the Integration Services server and are stored in SQL Server, the SSIS Package Store and the file system.

It can be done using the SQL Server Agent by creating a proxy in it.

The following shows how to create a proxy in SQL Server Management Studio and configure a SSIS package to schedule its execution.

The following  is the procedure for scheduling a SSIS package.

1. Open the SQL Server management studio.

2. Go to Security -> Credentials. Right-click and add a credential.

browse

2.1 Click on the “Browse” button in front of Identity.

location

2.2 Click on Location.

Entire Directory

2.3 Expand “Entire Directory“.

domain name of server

2.4 Select “domain name of server”, and click on OK.

Find Now

2.5 Click on the “Advanced “button.

The following window appears.

Provide your name in the text box labelled “Name“ having the “Start with“ drop down list. Then click on the “Find Now “ button.

ID

It will search and list all the matching IDs, select your ID. After clicking OK the following window appears:

login

Click on OK, the following window appears. Now provide your window (login) password.

ok

And click on OK.

Now you can check the added credential.

3. Now go to SQL Server Agent, expand it and add a new proxy by right-clicking on it.

right clicking

SQL Server

Provide some name to the proxy then browse to the Credential name button.

proxy

Now click on browse.

All the added credentials can be seen from here.

added credentials

Select the credential name by checking the checkbox and then click on OK.

checkbox

Note: Check the checkbox of “SQL SERVER INTEGRATION SERVICE PACKAGE” Then click OK.

4. Now go to SQL Server Agent, expand it and right-click on New Job.

SQL server agent

Click on “General“ and provide a name to the job.

General

Now click on "Steps" then click on New.

Steps

Provide a name for the Step name.

Select “SQL Server Integration Services Package“ from the ”Type” drop down list.

Select your proxy name from the “Run as“ drop down list.

Select “file system“ from the Package source drop down list.

file system

Now browse your package.

package

Click on OK:

Click on ok

Now go to schedule.

schedule

Click on new:

Click on new

Provide any name for the schedule.

Select the schedule type and provide the appropriate schedule timing and day as per the scheduling requirements.

Check for the various option.

Then click on OK.

We can check the job in the job list as follows:

job list

We can also cross-check that when all the steps are done without an error, by right-clicking on this job, it begins checking the scheduling.

check the scheduling

Note: SQL Server Agent should always "start", in other words in running mode.

running

Now the package will execute on the scheduled time.

Thanks for reading the article. Please feel free to ask any questions related to it.

Up Next
    Ebook Download
    View all
    Learn
    View all