I am unable to get the data from Time fields of SAP tables when the data is extracted using a RFC function module. When I execute the RFC in SAP ECC directly i can see the data in ERZET and AEDET (time fields) but within Data Services they come out as 00:00:00.000000000.
How do I extract the data correctly? The datatype in both SAP and Dataservices is Time.
Attaching a word doc with screenshots of data within DS and SAP.
Yes I have had this very same problem, in particular with the CATSDB table (Timesheet data). At one of our customers, it would always error without any real reason or so it seemed - the DF was using RFC Streaming to get the table out of ECC 6.0 and into the SQL Server based DWH, using Data Services 4.1 SP2 at the time.
Long story, short:
We tried switching to ABAP (R/3 DF) for this table but that created another headache - as there were linefeed characters in the time entry comments. (The ABAP program in the R/3 DF exports the data from CATDB into a text file on the SAP application server, which the DS Job Server then reads… and it kept hitting the unexpected linefeed characters and aborted reading those rows…)
So in the end I did the following:
I switched back to RFC Streaming DF for this table and I modified the RFC table definition in the DF and changed the TIME data type to VARCHAR(50). I did this on the RFC table object in the DF itself, NOT in a Query Task.
This allowed me to capture the data in SQL Server and see what was causing all this grief.
And sure enough, a handful of records was using 24:00:00 as part of the time format - which is of course wrong, as there is no “24th hour” - there is 23:59:59 and then there is 00:00:00. 24 is not a valid hour value for time. I have no idea how or why this happened but it was this handful of entries that prevented me from reading this data into the data warehouse.
The Staging table had all times converted to VARCHAR(50) - so I created a custom function to check if the time was set to 24:00:00 and if so, I changed this back to 23:59:59. This was followed by a Validation transform that checked if the time values were actually valid - if so, the Pass stream would convert this data back into TIME values and be loaded into the reporting tables. Any invalid entries would be loaded into an error table - so that this data could be examined and cleaned up in ECC. So far, no other invalid time data has been detected other than the 24:00:00 entries.
Thanks for the reply. I had read your previous post on this topic but wasnt sure if the solution would apply to my case.
Do you mean when I import the function in SAP datastore, i need to change the datatype to varchar(50) in the output parameters?(Attaching screenshot) If i do that and call the RFC function in my jobs, this time none of the data gets extracted. The output is blank.
I am not pulling data from an ECC table directly in my jobs. I am making a function call to a custom built RFC in ECC directly into my DS jobs. I need to capture the output of the custom RFC function in a SQL database table. The only place i can change the datatype of the Time field to varchar is after I import the RFC under ‘Functions’ in the datastore. But that did not work either.
Get the custom rfc data type changed or move away from that rfc. I have not found other solution with this problem (changing the table definition is not applicable here, but is a good trick).
I did run the job in debug mode and when i view the output structure from the RFC I see the Time field is all 00:00:00.0000000. Thats why i know that DS is unable to convert the Time field of RFC output to DS time field.
I cannot move away from the RFC since thats my requirement. I can ask the ABAP developer to see if he can change the format to Varchar in the RFC but not sure if that’ll solve the issue.
Mansi, I tried this one on a 4.1 SP1 FP5 using Design Time data viewer and also with a 4.2 SP1 system with the same option on a MKPF - CPUTM and it appears to work fine for me.
I would ask you to do this same test on the MKPF if not on the table from where that TIME column actually comes from and see if the TIME value comes up or not.
Do you want me to extract directly from the table MKPF to see if Time comes out? If thats the case, then it does. When i get data directly from an ECC table, the time field has no issues. Its only when i invoke a custom RFC by making a function call, the data is missing.
I asked the ABAP developer to create another copy of the RFC and change the Time format to varchar. I wanna test if the data comes out when its a varchar. I will keep you posted.
(BOB member since 2007-01-10)
(BOB member since 2008-07-17)
(BOB member since 2012-03-20)