This article shows, how to upload CATS data from a SQL server table by using SSIS and Xtract IS BAPI.

For creating Time Sheet entries in SAP, we use BAPI_CATIMESHEETMGR_INSERT. The important input fields are PROFILE and TESTRUN (import parameters) and table CATSRECORDS_IN. The results will be available in tables CATSRECORDS_OUT and RETURN. The RETURN table contains different types of messages, for example warning, information, error etc. If the input records do not have any errors, then the table CATSRECORDS_OUT will be populated with the same number of input records, otherwise it will not contain any entries.

When we post records using this BAPI, it is to be noted that only records that do not contain errors are posted. If an error occurs during posting, none of the data – including records that do not contain errors – is posted. So, it is advisable to include only a small number of records in each posting.

For example, let us populate 10 entries to CATSRECORDS_IN and execute the BAPI.

If there is no error then CATSRECORDS_OUT will contain 10 entries.

If two entries have errors, then CATSRECORDS_OUT will be empty, but we can identify the erroneous entries with the help of RETURN table. The erroneous entries can be easily identified with the ROW fields of RETURN table. If the second and third entries have errors then the ROW of RETURN table will be populated as 2 and 3.

By using Xtract IS BAPI, with the help of three SQL tables, we can fill the input details and read the output tables.

The three tables, to be created in SQL database are for CATSRECORD_IN and for CATSRECORD_OUT and RETURN. We need to fill the import parameters PROFILE with an appropriate value and TESTRUN as SPACE too.

Here is an overview of the data flow task.

First Step: Prepare input Table in SQL Database

Create a SQL table for CATSRECORDS_IN with the following fields:

Table Name: BAPICATSINPUT

Fill the table with sample data:

Second Step: Create IS BAPI Component

For creating the IS BAPI component, we need to create the data flow task and necessary OLE DB and XTRACT connections.

After this, create an OLE DB Source and map it with the already created BAPICATSINPUT table.

Then create the XTRACT BAPI component and assign the BAPI BAPI_CATIMESHEETMGR_INSERT .

For example, let us provide the PROFILE TEST and TESTRUN SPACE as import parameters. We should map the input type of CATSRECORD_IN as pipeline and also the output type of CATSRECORD_OUT and RETURN as pipeline.

Then map corresponding fields of CATSRECORD_IN with the fields of SQL table BAPICATSINPUT.

Third Step: Prepare CATSRECORDS_OUT and RETURN (Output) Tables in SQL Database:

Create two OLE DB destinations for CATSRECORDS_OUT and RETURN tables of BAPI.

Let us create BAPICATSRETURN and BAPICATSOUT tables for these OLE DB destinations and map the corresponding fields.

BAPICATSRETURN

BAPICATSOUTPUT

Now, the design is finished and lets us execute the application.

The output will be as follows.

From the above diagram, we would be able to see that for n number of records of CATSRECORDS_IN, there is exactly the same number of records in CATSRECORDS_OUT. So the data has been updated successfully.

Now let us check the SQL tables BAPICATSOUTPUT and BAPICATSRETURN.

BAPICATSOUTPUT

BAPICATSRETURN

There is only an information message.

Now let us check the SAP transaction CAT3 (Time Sheet: Display Times)

We have to provide PROFILE, WORKDATE and EMPLOYEENUMBER.

Let us now execute entries with errors.

Populate the table CATSRECORDS_IN with the following entries.

Here the Attendance or Absence Type is 0005 instead of 0001 for the second and fourth entries. Let us check the output now.

Here the CATSRECORDS_OUT does not have any entries. So it is clear that data is not updated.

Let us query the table RETURN to identify the error.

Here, by checking the ROW numbers we can easily identify the erroneous records. From the message, it is clear that the attendance/absence type (0005) is not maintained.

Here, by checking the ROW numbers we can easily identify the erroneous records. From the message, it is clear that the attendance/absence type (0005) is not maintained.

Written by Khoder Elzein

Khoder is responsible for presales and always has an attentive ear for our customers, prospectives and partners. When travelling in Germany or abroad, he provides customer support at PoCs, workshops and on training courses. He also looks after the further development of our software solutions. Khoder has been working in IT since the turn of the millennium; he has been a member of team Theobald since 2009. When it comes to private interests, family, nature and reading feature at the top of his list – along with innovative fusion cuisine, as you may infer from his favourite dish of Swabian cheese noodles with tabouleh.