Schedule SQL Server Agent Job from SSIS Package Stored in MSDB/Integration Services
In my previous post, I had written How to add SSIS Package to MSDB. In this post we will learn how to use this stored package and Schedule SQL Server Agent Job from SSIS Package Stored in MSDB/Integration Services. Below mentioned is the step by step intructions.
1. Connect to SQL Server Instance where you want to schedule a Job, Expand SQL Server Agent, Right Click on Jobs and Select New Job as shown below.
2. In New Job window, In general tab, provide a meaningful name as shown below.
3. Now click on Steps Tab, and select New as shown below.
4. In New Job Step window, provide a Meaningful name for Step name(TestCHKDB_Step in my case), Select "SQL Server Integration Services Package" under Type, Select SQL Server Agent Service Account(Make sure this account has previlege on the destination server to process what you want) or you can use proxy Account. How to create SQL Server proxy account you can find here: SQL Server Agent Proxies
5. In Package Source, select SQL Server from dropdown and provide the servername in Server where the package was stored (LOCALHOST in my case)
6. Click on Elipse Button next to Package to select the package from the Store and in Select an SSIS Package window, Select the package from which you want to run the Job(TestCHKDB in my case which i had created in my earlier post) and select ok.
7. Now your screen should look like below. Click Ok and Click ok. Thats it. You have now Schedule SQL Server Agent Job from SSIS Package Stored in MSDB/Integration Services