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.



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



2.2 Click on Location.



2.3 Expand “Entire Directory“.



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



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.



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



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



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.





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



Now click on browse.

All the added credentials can be seen from here.



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



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.



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



Now click on "Steps" then click on New.



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.



Now browse your package.



Click on OK:



Now go to schedule.



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:



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.



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



Now the package will execute on the scheduled time.

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