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)
9. In Logging tab, you have the option to log the SSIS package execution. There are basically five ways in which you can enable the SSIS package logging and they are by using Windows Event Log, Text File, XML File, SQL Server or SQL Server Profiler.
10. In Set values tab, you can override the values for the user variable used in the SSIS package.
11. In Verification tab, you can specify different options like:-
- Execute only signed packages
- Verify Package builds
- Verify package ID
- Verify version ID
If the package meets the criteria which you have specified then only the package will be executed. If there are no values specified then the package will execute ignoring the options.
12. In Command Line tab, you can see the actual command based on all the selection which you have done till now in different tabs. The command line which is highlighted in the snippet below will be executed once the package is scheduled. Click OK to save the Job step.
13. Next step will be to define the schedule for the SSIS package. This can be done by selecting the Schedules page from the “Select a page” left panel and then clicking the New… button to define the job schedule as shown in the snippet below. Click OK to save the Job schedule. And click OK in the main job window to save the SSIS Package Job.
Executing Newly Created Query