[SOLVED] JSON object as target - failing to load records

November 20, 2015, 06:30 AM

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,

December 02, 2015, 03:01 PM

Clif

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.

December 08, 2015, 09:53 AM

mreddappareddy

Hi Clif,

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

Thanks,
Reddappa.

December 10, 2015, 09:56 AM

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.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,

December 11, 2015, 09:05 AM

mreddappareddy

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.