How can I extract SAP Text into a SQL Server table ?

Five Stars

How can I extract SAP Text into a SQL Server table ?

Task : Extract text from a variety of SAP related objects ( e.g. Work centre text) and store the data into SQL Server table. 

 

Progress: I found a tutorial on youtube and managed to read the work centre records and join with a fixed flow component to then read the text data using an RFC_Read_Text component.

This sends it to an xml tmap and then to the SQL DB.  

So I can see where the text is. I can read it. 

 

BUT I get 11,325 records instead of 150 and I don't know how to reduce it to 150 records. 

 

Questions: 1) Is this a suitable method to extract the text ( or is there a better way) ?

2) How can I diagnose the tmap to get one row of text per one row of WorkCentre source data ? 

Five Stars

Re: How can I extract SAP Text into a SQL Server table ?

Screen Shot 2019-01-23 at 12.18.37.pngScreen Shot 2019-01-23 at 12.16.27.pngScreen Shot 2019-01-23 at 12.14.42.pngScreen Shot 2019-01-23 at 12.11.47.png

Employee

Re: How can I extract SAP Text into a SQL Server table ?

Hi,

 

    It seems the loop inside the tXMLMap is generating more lines per incoming record. If a single incoming record has multiple item data, it will create one record for each of those line items. This means the data will be expanded exponentially based on your input data set. 

 

    Please change the looping according to your business need and you can reduce the number of records. Also you can add a tUniqrow if you would like to filter out the duplicate records.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)
Five Stars

Re: How can I extract SAP Text into a SQL Server table ?

Hi thank you for the input! 

 

Do you have any suggestions on how I would change the tXMLMap ?  

Screen Shot 2019-01-23 at 13.37.26.png

 

I did try the tUniquerow - a great suggestion - and for this small table of 150 rows that gives me the results that I need. 

Highlighted
Employee

Re: How can I extract SAP Text into a SQL Server table ?

Hi Andrew,

 

     You can do the looping at higher level which will pick only one record instead of looping. But the question remains whether is it the right way to process the data.

 

     If the source is providing multiple records under same node of item in loop fashion, we will have no choice but to pick them. If we did not enforce the loop, it means we are going to miss some data.

 

     In your case, it seems the source is sending duplicate data in under item node. The best way is to talk to your source team to understand what is the level of looping we need to do during data extraction time. In ideal case, we need to create an Interface Definition Document which will capture the details of XML and its looping at each level so that source and target systems are agreeing to a common format. 

 

    Once your source team is providing you with these details, your life is going to be easy :-)

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)
Five Stars

Re: How can I extract SAP Text into a SQL Server table ?

The solution was that I needed to change the tXMLMap output parameter All-In-One to True! 

Screen Shot 2019-02-12 at 14.18.47.png

Cloud Free Trial

Try Talend Cloud free for 30 days.

Tutorial

Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.