Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [SOLVED] JSON object as target - failing to load records

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] JSON object as target - failing to load records
 Login/Join
 
Member
posted
Greetings to All,

I am trying to create an ETL job where my requirement needs JSON object as a target.

I used some sample data to create a new JSON file as target in one flow.
When i tried to submit the flow is throwing an error but the rows are getting inserted. When I run the same flow without submitting it, the flow is executing without any errors.

In this process DataMigrator is not creating any synonyms for this JSON file even though I am specifying a seperate name for synonym in properties. So i went and created a synonym based on JSON adapter for the JSON output file
that was generated.

Next I tried to create a similar flow, this time instead of new JSON file(as mentioned in Point 1) I used the synonym that was created earlier. Now when i submit and run the job no matter how many records
the input file has , the flow is failing after generating the first record.

Here is the sample

Input File: Delimited Flat File
Input Content:
key,value
one,1
two,2,
three,3
four,4

Output File Type: JSON file
Content generated.
{ "records" : [
{ "key" : "one","value" : "1"} ,
}

I don't understand what am i doing wrong here.
Could someone please help me with this issue?

I am using DataMigrator v7706M, on Windows 7 64 Bit platform.

Thanks

This message has been edited. Last edited by: Tamra,


Webfocus 8, Windows All Versions.
 
Posts: 7 | Registered: October 14, 2015Report This Post
Guru
posted Hide Post
There are two issues here.

(1) When submitting the original flow with a new target the pop-up message is actually identified as a Warning.
(ICM18953) Missing or invalid transformation(s) in target
That does sound like an error and we will fix that. The problem here is just with the column named KEY. It should warn you that its use is restricted.

(2) Before running the second flow you need to edit the synonym. Right click on the segment RECORDS and select Properties. Change the value of SYNTAX_CHECK to relaxed and save the synonym.

Then run the flow and you should be good to go.


N/A
 
Posts: 397 | Location: New York City | Registered: May 03, 2007Report This Post
Member
posted Hide Post
Hi Clif,

Is there a way to generate multiple output files with 100 records limited to each file?

Thanks,
Reddappa.


Webfocus 8, Windows All Versions.
 
Posts: 7 | Registered: October 14, 2015Report This Post
Guru
posted Hide Post
There is. Assuming you got single file output to work you can extend this to multiple files using a paramerterized synonym and the iterator. These capabilities are documented in the DMUG.

1) To write out multiple files you'll need multiple file names. So you need to paramerterize the DATASET value in the synonym for your target table. Add a variable like &&FNAME to your synonym and then change DATASET to something like baseapp/&&FNAME.json

2) You need some way to partition the input data. One way would be if you had an identity column in your source data, that is a column that starts with 1 and increases by 1 for each row. Call it ID say.

3) You need to create a control file and a synonym for it. For example you could create a comma delimited file like this
A,B,C
1,100,1
101,200,2
201,300,3
...


4) Add a WHERE condition to your data flow.
 T1.ID BETWEEN &A AND &B


5) Switch to the process flow and add a Variable object between Start and the data Flow. Assign a file name and then save the flow.
&&FNAME='myfile'|&C 


6) Iterate the flow. Create a NEW flow and switch to the process flow tab. Drag your original flow into the process flow and on its Properties panel check the checkbox for "Get Parameters using synonym" and enter the name of the synonym you created in Step 3.

7) Submit the process flow and wait. It will run the original data flow once for row in your control table, writing 100 rows for each iteration.

This message has been edited. Last edited by: Clif,


N/A
 
Posts: 397 | Location: New York City | Registered: May 03, 2007Report This Post
Member
posted Hide Post
quote:
Originally posted by Clif:
There is. Assuming you got single file output to work you can extend this to multiple files using a paramerterized synonym and the iterator. These capabilities are documented in the DMUG.

1) To write out multiple files you'll need multiple file names. So you need to paramerterize the DATASET value in the synonym for your target table. Add a variable like &&FNAME to your synonym and then change DATASET to something like baseapp/&&FNAME.

2) You need some way to partition the input data. One way would be if you had an identity column in your source data, that is a column that starts with 1 and increases by 1 for each row. Call it ID say.

3) You need to create a control file and a synonym for it. For example you could create a comma delimited file like this
A,B,C
1,100,1
101,200,2
201,300,3
...


4) Add a WHERE condition to your data flow.
 T1.ID BETWEEN &A AND &B


5) Switch to the process flow and add a Variable object between Start and the data Flow. Assign a file name and then save the flow.
&&FNAME='myfile'|&C 


6) Iterate the flow. Create a NEW flow and switch to the process flow tab. Drag your original flow into the process flow and on its Properties panel check the checkbox for "Get Parameters using synonym" and enter the name of the synonym you created in Step 3.

7) Submit the process flow and wait. It will run the original data flow once for row in your control table, writing 100 rows for each iteration.



Hi Clif,

Thanks a ton, I was able to generate multiple files using the method given by you above, but i have a small issue here.

Though DataMigrator is able to generate the file, When i try to open it, I am getting a warning saying the "file doesnot exist, do you want to create it".
But when I sample the data with proper dataset variable values as an input,I am able to view the sample data for that file.

Any Suggestion or findings on what am i doing wrong here?

Thanks,
Reddappa.


Webfocus 8, Windows All Versions.
 
Posts: 7 | Registered: October 14, 2015Report This Post
Guru
posted Hide Post
Glad that worked for you.

One thing I missed in my post was that the DATASET name in the synonym should be:
baseapp/&&FNAME.json

Sorry about that.


N/A
 
Posts: 397 | Location: New York City | Registered: May 03, 2007Report This Post
Member
posted Hide Post
Yes, I have set the data set name just like you said. I have given the file name extensions in the dataset. And I am able to see that multiple files were generated with the given. But it's just that I am not able to open the file that was created through this method.
Whenever I try to open the file I am getting file does not exists error.


Webfocus 8, Windows All Versions.
 
Posts: 7 | Registered: October 14, 2015Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [SOLVED] JSON object as target - failing to load records

Copyright © 1996-2020 Information Builders