For companies implementing SAP BusinessObjects Planning and Consolidation 5.1 or 7.0, version for Microsoft, several challenges are involved with setting up job scheduling and integrating SAP BusinessObjects Planning and Consolidation with the rest of the enterprise reporting landscape. While the Microsoft version provides a time-based job scheduling interface for SQL Server Integration Services (SSIS) packages through data manager, most companies typically schedule extraction, transformation, and loading (ETL) jobs using third-party scheduling tools that do not provide an easy interface with SAP BusinessObjects Planning and Consolidation.

When loading data into SAP BusinessObjects Planning and Consolidation, you need to execute SSIS packages within the data manager, using standard SAP BusinessObjects Planning and Consolidation functionality, such as validating input records, logging reject records, and capturing the data load history. However, to have controlled scheduling and to integrate the SAP BusinessObjects Planning and Consolidation jobs into the overall enterprise-wide ETL schedule, it is beneficial to be able to trigger SSIS packages using any other scheduling tool and still have them execute within the data manager.

In this article, I present an easy-to-implement solution for executing SSIS packages with a third-party enterprise scheduling tool. This solution enables you to create a batch script on the SAP BusinessObjects Planning and Consolidation application server that can trigger the scheduled SSIS packages. The script also gets the return codes based on the execution status of the jobs and passes them on to the enterprise scheduling tool. When you pass the necessary parameters to it, only a single batch script is needed for triggering all the SSIS packages in an entire SAP BusinessObjects Planning and Consolidation environment. Figure 1 shows the end-to-end control flow from scheduling a job in a third-party application to executing an SSIS package in the SAP BusinessObjects Planning and Consolidation data manager and receiving the return code (SSIS package execution status) from the third-party application.

Figure 1

End-to-end control flow

The steps below describe how to create and schedule an SSIS package within SAP BusinessObjects Planning and Consolidation, set up a batch script on the application server, and execute it through an enterprise scheduling tool. The batch script can be triggered from any scheduling tool that is capable of passing parameters and executing OS level scripts. All SAP BusinessObjects Planning and Consolidation log files are placed into the standard SAP BusinessObjects Planning and Consolidation folders.

Step 1. Create a Package in SAP BusinessObjects Planning and Consolidation Data Manager

Once the SSIS package is created and is ready to be executed in SAP BusinessObjects Planning and Consolidation, it should be added to the data manager through the eData menu within SAP BusinessObjects Planning and Consolidation, version for Microsoft. Note that the SSIS package should be placed in the predefined file path on the file share folder to add it to the data manager.

Select Organize Package List… from the eData menu (Figure 2).

Figure 2

Select Organize Package List…

Right-click and select Add Package from the context menu on the right screen (Figure 3).

Figure 3

Select Add Package

Select the appropriate SSIS package from the list (Figure 4).

Figure 4

Select the SSIS package

Fill out the description and click the Add button (Figure 5).

Figure 5

Enter a description

Save the changes by clicking the Add button to successfully add the new package to the data manager (Figure 6).

Figure 6

Save the changes

Step 2. Schedule the SSIS Package in the SAP BusinessObjects Planning and Consolidation Data Manager

To execute the SSIS package within the SAP BusinessObjects Planning and Consolidation environment, you must schedule it with the Enable Schedule option turned off. This prepares the package to be run from a background job but prevents it from being executed based on the default time settings.

Select Run a data management package from the action pane (Figure 7).

Figure 7

Click Run a data management package under the Manage Data Options section

In the data manager screen, select the package that needs be scheduled and click the Schedule radio button (Figure 8). Then click the Next button.

Figure 8

Select the Schedule radio button

Fill out the schedule name and description (for consistency, it is best to use the same name as the package) and deselect the Enable Schedule check box (Figure 9). Click the Finish button to successfully schedule the package.

Figure 9

Finish scheduling the package

Step 3. Check the Schedule Status

Select View schedule status from the Manage Data Options section in the SAP BusinessObjects Planning and Consolidation Excel interface to see the package schedule status (Figure 10). Note that the Status should be set to Not Scheduled in the Package Scheduler Status window. Also, note down the Job name as you will need it later.

Figure 10

Set the schedule status

Step 4. Check the Scheduled Tasks on the Application Server

Log in to the SAP BusinessObjects Planning and Consolidation application server and check the scheduled tasks to see which parameters are required for executing the background job that was created when the SSIS package was scheduled. Note that the parameters are the same for all packages, but the values differ based on the SSIS package. From your desktop, follow menu path Start > Programs > Accessories > System Tools > Scheduled Tasks (Figure 11).

Figure 11

Schedule tasks

Find the corresponding job name noted in step 3 (e.g., DM_FPR_DEV_PCM_6.job) and right-click it. Select Properties from the context menu to see the executable command and the required parameters (Figure 12). Note that when a package is scheduled, the data manager actually executes OSoftSchedulingDMLauncher.exe on the server with predefined parameters. Make a note of the job name and parameters as you will need these for the third-party application later.

Figure 12

List of scheduled tasks

Step 5. Create a Batch Script and Place It on the Application Server

Log in to the application server using the remote desktop connection, and create the script BPCDataLoad.bat in the sacabpcprodscripts folder. The batch script has two steps: 

1. Execute OSoftSchedulingDMLauncher.exe that in turn triggers the SSIS package in the data manager.
2. Execute a SQL command to read a table on the SAP BusinessObjects Planning and Consolidation database for converting the package execution status into the return code that is passed back to the scheduling tool (Figure 13).

Figure 13

Batch script

This command includes these parameter details:

            USERID: <Any ID that has access to the data manager>

            APPSET: <Appset name>

            APPLICATION: <Application name>

            SCHEDJOB: <Job name from step 3>

            DATABASE: <Database name>

            JOBNAME: <Job name in single quotes from step 3>

Step 6. Set Up an Enterprise Scheduling Job to Execute the SSIS Package

Within the scheduling tool, you need to create a job to trigger the batch script by passing all the above parameters to the batch script. The job status is visible in the scheduling tool as well as in the SAP BusinessObjects Planning and Consolidation data manager to better monitor the data loads. Here are the important parameters that need to be provided in setting up the scheduling tool job.

Job Name is a user-defined name. Provide the script name that was created on the application server and the location in the General tab (Figure 14).

Figure 14

Enter a user-defined name

Provide the server name in the Execution tab (Figure 15).

Figure 15

Enter the server name

Provide the parameter values in the Set tab (Figure 16).

Figure 16

Provide the parameter values

By following these steps and providing the corresponding parameters in the scheduling tool job, you can trigger any number of SSIS packages from the enterprise scheduling tool with a single batch script.

Step 7. Execute the Job and Check the Status in Both Systems

Execute the job to run the script that in turn triggers the SSIS package on the SAP BusinessObjects Planning and Consolidation application server. The job status is visible in both the scheduling tool and the SAP BusinessObjects Planning and Consolidation data manager. While the job is running, the job status is yellow in my example scheduling tool and the package status in SAP BusinessObjects Planning and Consolidation data manager is seen as progressing.

Upon successful completion of the SSIS package in SAP BusinessObjects Planning and Consolidation, the package status in the data manager changes to either Completed if successful or Warning if there are any rejected records in the input file. The corresponding scheduling tool job status changes to green.

If the SSIS package fails in SAP BusinessObjects Planning and Consolidation, the package status in the data manager changes to Error and the corresponding scheduling tool job status changes to red (Figure 17).

Figure 17

Current status of the job

Select Manage Data > View package status in SAP BusinessObjects Planning and Consolidation to see the SSIS package status in the data manager (Figure 18).

Figure 18

View the package status