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

Highlighted
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 :-)

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. 

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 :-)

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

15TH OCTOBER, COUNTY HALL, LONDON

Join us at the Community Lounge.

Register Now

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Definitive Guide to Data Quality

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

Download