Here we are going to discuss about how to create sql job for SSIS package and schedule using SQL Server 2005. Sharing step by step details for scheduling SSIS package by using proxy account and credential. We can use either T-SQL statement or SQL Server Management Studio for creating SQL Jobs of SSIS Package
We got a request from DTS team for scheduling SSIS package on production for data processing at midnight. Request approved by manager and assign to DBA team which is added in daily queue list.
Based on the priority, request assign to one of team member who followed below steps to complete his task.
How to create SQL Job for SSIS Package?
Steps involve for creating sql job and scheduling SSIS package are as below.
1. Create credential in sql server 2005
2. Create proxy on the SQL Server
3. Schedule the job
Using SSMS First connect to SQL Server instance
To create credential account in SQL Server 2005 for SSIS package.
1. Click on security/credential –> right click on credential and select new credential.
2. Give the name of credential as test_credential. In the next step assign SQL Server login account as identity.
To create new pproxy account on the SQL Server Click on SQL Server Agents/proxies–> Right click on SSIS package execution for SSIS package–> new proxy
It will display dialog box for new proxy account of SSIS package
• Give the proxy name as “using_proxy”
• In credential name, select the credential which we have already created in earlier step for SSIS package. We can also select the credential account by click on browse button on right of the credential as shown in the below figure.
• Select your credential account from the list and click on ok
After assigning credential account, select the SQL Server Integration Services package from the list and click on ok.
Select the principals option, here we can add SQL Server login to grant access to the proxy account. Click on ok to continue.
Now scheduling SQL job is easy and simple same as normal job difference is only in step.
1. Select job and Right click on job –> new job
2. In this screen give the name of the job and assign the sql server login account as owner of the job to execute.
3. Click on step and click on add to create new step
4. Give the step name as “exec_package.”
5. Select SQL Server Integration Services package from type option.
6. Run as option will use proxy to run this package, Select the proxy from the drop down list.
7. Select general option from the tab and select package source as “File System”.
8. In package option, select the package file which you want to execute through job using browse option at right corner.
Click on advance option to log error details in event log if any failure occurs while execution of the package.
Click on last option “include step output in history”, we can also create output file by give file path in the output file output or directly store details in table of sql server.
9. We can schedule job by using schedule option in left side pane. Select the existing timer using pick option scheduler or create new by clicking on new.
10. Schedule this to midnight of every day.
Now click on ok to finish These steps will help to schedule the SSIS package on UAT and production environment.
Always recommended, try first on UAT before going production server.