Scheduling SSIS Package Using SQL Server Agent

Step 1:  Set your SSIS packages properties as:

Go to Security tab and then Set 

ProtectionLevel : EncryptSensitiveWithPassword

PackagePassword : xyz (any password you like )
 
1.gif

Step 2: Build your  SSIS Project

Step 3: Login into SQL Server Integration Services


Step 4:  Go to MSDB folder 
 

Step 5:  Right Click on MSDB folder and then New Folder and give the name say SSISPackage and then right click on SSISPackage and then Select Import Package.

Step 6: Select Package Location: File System and then Broswe your package by clicking ellipse button in front of Package Path.

5.gif

Step 7: Select Protection Level as:  Encrypt sensitive data with password and then enter your package password (temp which we already set in our package).

6.gif

Step 8: Click Ok. Then again enter your package password (temp) which we entered in the previous step.

Step 9: Log in to Sql Server Management Studio and Select Database Engine

Step 10: Go to Sql Server Agent and then Right Click on Job and Select New Job.

Step 11: In General tab : Give a name to your Job say ScheduleSSIS and give any description (optional) 

9.gif

Step 12:  Go to Steps tab and then click New. Settings
  • Step Name say as Step1.                 
  • Select SQL Server Integration Services Package in Type 
  • Run as: SQL Server Agent Service Account.
  • Package Source : SQL server
  • Server : IP or Server Name
  • Windows or SQL Server Authentication of the SQL Server where we have stored our SSIS Packages
  • Click ellipse button in front of Package and then Select your SSIS Package you want to schedule.

Step 13: Select Your Package and then click ok and then enter the package password.

Step 14: Go to Schedule tab and then click New and give a name to Schedule such as Schedule1 and Schedule Type: Recurring and the rest of the setting as per your requirement and last click OK.

11.gif

Your SSIS Package has been successfully scheduled.

All queries are welcome !!!

Rajeev Kumar Mehta

EMail - [email protected],[email protected]

Up Next
    Ebook Download
    View all
    Learn
    View all