CPI DS Tips for Beginners

Introduction:

I have involved in data migration project where I need to work on CPI Data Services.  I am sharing my lessons learnt in this blog post with my fellow CPI DS developers.

Errors and Resolutions:

Sftp Target File Name Setup: During generation of file name with time stamp, I found an error “conversion Errors”.

File name pattern should be as shown below to add time stamp to fixed file name.

$G_Filename = ‘CUST_’||to_char(sysdate( ),’YYYYMMDDHHMISS’)||’.CSV’;

For Ex: G_Filename=’CUST_20191030124300.CSV

Sftp Passphrase issue: All of a sudden, I got an error “login Denied” although there is no change in login details. I Tried to update Passphrase via chrome browser but got same error. Login issue is resolved When I updated it via internet explorer.

Issues with time stamp conversion: I got issues during conversion of timestamp format from one format to another format. We can achieve date format conversion in 3 ways.

a) Using data store configuration default format setup as shown below.

b) Enter format type in column of File Format in Data Store.

c) Mapping level but it is bit complex when compared to above 2 approaches.

Default Value setup when there is null from source table or file. By default, Null Indicator (highlighted) value as <null>.

Hence file is generated with default value as null. In order to create fields/columns with default value as blank (“”) then enter blank (remove null) in field “Null Indicator” as shown below.

MAPPING ISSUES:

Map with Constant: I tried to find function “Constant” to map it to target field, but I did not find it. After spending some time, I found that below is process to map constant values.

a) Constants of type Integer. Need to enter constant as shown below.

b)Constant of Type Varchar. Need to enter constant in single quotes as shown below.

Date Time Conversions: it can be achieved easily with either option “default format” or “Format in Column definition” (explained in step 3). We don’t have direct date and time transform functions but we have format conversions from date to string and vice versa.

Concatenate Function: We don’t have concatenate function in CPI DS mapping, but we can use this operator || to concatenate.

For ex: CUSTOMERS.FIRSTNAME||’MIG’||’test’.

Escape Character issue: I got a error as “invalid expression” for escape chars in mapping/Transformation.

In order to allow escape char i.e. { use back slash \ in front the escape char i.e. \{

Generic Error for data issues: Below error is generic and it will not give exact issue. We have observed below error in case of data issues.

“2019-11-05 11:38:45       (E)(21204:21300)   RUN-50406       |Session PROD_CUSTOMERS_SIBEL_TO_HANADB|Data flow PROD_CUSTOMERS_SIBEL_TO_HANADB_PROD_CUSTOMERS_SIBEL_TO_HANADB

                                                               Data flow <PROD_CUSTOMERS_SIBEL_TO_HANADB_PROD_CUSTOMERS_SIBEL_TO_HANADB> received a bad system message. Message text from the

                                                               child process is <ﾀﾣￚﾋU�==========================================================

                                                               Collect the following and send to Customer Support:

1. Log files(error_*, monitor_*, trace_*) associated with this failed job.
2. Exported ATL file of this failed job.
3. DDL statements of tables referenced in this failed job.
4. Data to populate the tables referenced in the failed job. If not possible, get the last few rows (or sample of them) when

                                                               the job failed.

Core dump, if any, generated from this failed job.”

a) For ex: DS Task is configured to read column values with text qualifier (i.e “). If extra qualifier is added to column value (i.e.“firstname””) then it throws above error.

Below is configuration setup for Text Qualifier

b) Initially field names are hard coded as shown below to appear them in target file as a header line. It worked. Later point of time, I got request to add one more field to structure/data store. New field has been added to structure and File Header but I got generic error (above one) during testing.

Solution:After I used check box “First Name Contains Column names” it resolved the issue.

CONCLUSION

This blog post may not be useful for experience candidates but It is useful for CPI DS beginners.