Scheduling a SSIS Package with SQL Server Agent
4. In the New Job Step window, you need to provide the following below mentioned details.
Step name: SalesCustomer SSIS Package
Type: SQL Server Integration Services Package from the drop-down list
Run as: SQL Server Agent Service Account
In the General tab you need to provide information related to package source and package folder location. For this example you will be using the SalesCustomer.dtsx package which is available in “C:CustomerDataSalesCustomer.dtsx” folder. To configure, choose “File System” value from the Package source drop-down list and for Package location you need to browse and provide the path where the SalesCustomer.dtsx SSIS package exists.
Package source: File system
Note: There are basically three options available to select in Package source drop-down list namely SQL Server, File system and SSIS Package Store.
5. In Configurations tab, you have the option to specify the alternative configuration XML file if your package is using it. Else the package will be using the default configuration file which is available in “C:Program FilesMicrosoft SQL Server90DTSBinnMsDtsSrvr.ini.xml” location.
6. In Command Files tab, you can provide the environment specific settings that can be used by the SSIS package if there are any. If there are no such settings then the configurations tab can be left alone as shown in the snippet below.
7. In Data Sources tab, you will be able to see all the connection strings which the SSIS package is using internally. If you are interested in override those settings then you can select the corresponding connection and modify the connection string as appropriate.
8. In Execution options tab, you can configure many package level options like;-
- Fail the package on validation warnings
- Validate package without executing
- Override MaxConcurrentExecutables property (you can set the max concurrent executable properties value as -1 which means the package can execute parallel threads. The general formula is number of processers + 2).
- Enable package checkpoints (you can provide the checkpoint file information and the restart options that the package needs to follow when it is restarted after a failure)
- Use 32 bit runtime (this is very helpful if you want the package to be executed in 32 bit run time of SQL Server)