1 2 Previous Next 19 Replies Latest reply on Nov 11, 2019 12:00 PM by Ram Barot

    Posting a JSON File to a REST API using Informatica PowerCenter

    Ziad Ali Active Member

      I am give a REST API and we have to upload a JSON file to the REST. How do we achieve it using informatica Power center. I know we can use HTTP transformation to post data but is there a way we can post a file?

       

      If we can't with powercenter, what can we use to post a file to REST using informatica tools.

       

      Thanks in advance.

        • 1. Re: Posting a JSON File to a REST API using Informatica PowerCenter
          Syed Aziz Seasoned Veteran

          Hello Ziad,

           

          PowerCenter does not support files that are in Java Script Object Notation (JSON) format. Pmdtm is only capable of reading flat files (fixed or delimited), XML files, and so on. JSON is not a format that is readable by the dtm code or by the Designer for parsing the metadata.

           

          JSON data can be posted to Representational State Transfer (REST) Web Service by HTTP transformation using the SIMPLE POST mode. To do this, perform the following steps:

          Create an HTTP transformation with the HTTP method as a SIMPLE POST.

          Pass the JSON input from a string port to the HTTP transformation.

          Create a header with HTTP Name as Content-Type and the Default Value as application/json.

          If the endpoint requires a basic HTTP authentication, do as follows:

           

          Create an HTTP connection with the credentials.

          Assign it to the HTTP transformation in the session.

           

          - To read JSON, use a Java transformation and output in XML or write to a database.

          - B2B Data Transformation offers out of the box handling of the JSON Format.

          - For B2B Data Transformation, an additional block to support JSON Format can be downloaded from the Informatica Marketplace: B2B DT : Data Transformation JSON Support

          - Starting B2B Data Transformation, the support for JSON format is built into the product and there is no need to download the Marketplace block.

          - In addition, the JSON format is also HParser offering. Refer to the following links on the Informatica Marketplace for more details: HParser Community Edition

          -  JSON data extracts are handled through B2B Data Transformation project creation. 

          - B2B Data Transformation supports JSON through an import mechanism (such as, same as COBOL); upon loading a JSON object, a parser/serializer/XSD would automatically be generated.

           

          It is required to provide your parameters, into a single string in order to enable the SIMPLE POST if more than one is posted.

           

          Best regards,

          Syed

          • 2. Re: Posting a JSON File to a REST API using Informatica PowerCenter
            Nico Heinze Guru

            As long as no processing is necessary on the JSON contents, you could simply read a JSON file line by line, concatenate all those lines into one single string, and then post this string as described by Syed. This way you can get along without B2B DT, writing any Java code, or whatever.

             

            Regards,

            Nico

            • 3. Re: Posting a JSON File to a REST API using Informatica PowerCenter
              Ziad Ali Active Member

              Thank you Syed and Nico. I am having issues with the REST API to authenticate it, I am working on that to resolve. But I have few questions based on the suggestions you guys gave, how would I give the whole JSON file in one string? I create the JSON file using Powercenter if I attach the port to expression it is going to write the HTTP line by line right? Also is there a way we can send the JSON file as an attachment?

               

              Thanks. Appreciate your help in this.

              • 4. Re: Posting a JSON File to a REST API using Informatica PowerCenter
                Ziad Ali Active Member

                Thank you Syed and Nico. I am having issues with the REST API to authenticate it, I am working on that to resolve. But I have few questions based on the suggestions you guys gave, how would I give the whole JSON file in one string? I create the JSON file using Powercenter if I attach the port to expression it is going to write the HTTP line by line right? Also is there a way we can send the JSON file as an attachment?

                 

                Thanks. Appreciate your help in this

                • 5. Re: Posting a JSON File to a REST API using Informatica PowerCenter
                  Ziad Ali Active Member

                  Thank you Syed and Nico. I am having issues with the REST API to authenticate it, I am working on that to resolve. But I have few questions based on the suggestions you guys gave, how would I give the whole JSON file in one string? I create the JSON file using Powercenter if I attach the port to expression it is going to write the HTTP line by line right? Also is there a way we can send the JSON file as an attachment?

                   

                  Thanks. Appreciate your help in this

                  • 6. Re: Posting a JSON File to a REST API using Informatica PowerCenter
                    Nico Heinze Guru

                    Easy answer first, about the attachment thing I cannot tell, I really don't know how that can be achieved, sorry.

                     

                    About the one-line thing: you basically have to do two things to get the whole content into one line.

                    For this explanation I assume that you have the JSON contents stored in a text file, meaning you will need a flat-file source qualifier to read the JSON file.

                    That leads me to the first of the two things I've mentioned above.

                    You have to make sure that each line of content is read as one singe string and not as one or more fields.

                    This can be achieved with a little trick:

                    Create a flat file source definition with one string port (e.g. of length 10000).

                    Go to the flat file options for the delimiter character(s).

                    Enter the following as the delimiter character:

                       \037

                    (backslash, zero, three, seven)

                    This means that the ASCII control character Unit Separator (US, ASCII code 31, in octal representation 037) is set up as the field delimiter. To be honest I haven't seen this character in use at least since the early 1980's. So you can be fairly confident that no one will ever produce a JSON file with this character in any field. Meaning that each line of text will be read as one single string field.

                     

                    The second thing you have to do is to concatenate all those lines into one single string.

                    This can be done using an Aggregator transformation.

                    Set up an AGG and drag the string port (I assume here it's named FIELD) from the source definition (ok, from the source qualifier) into this AGG.

                    Create a variable port of type String(10000) named v_str and set it to this expression:

                       v_str || FIELD

                    Create an output port of type String(10000) named FULL_STRING with this expression:

                       v_str

                     

                    Beware that the AGG doesn't have any Group-By port. This means that it will produce one output record for all input records. And this output value will be the whole JSON file as one single long string.

                     

                    Regarding the HTTP transformation I have to be honest, I don't quite understand what you meant. An example might help.

                     

                    Regards,

                    Nico

                    • 7. Re: Posting a JSON File to a REST API using Informatica PowerCenter
                      Ziad Ali Active Member

                      Thanks again Nico. I will try to concat the string into one line.

                       

                      I think I misrepresented the HTTP part, since you explained on how to achieve JSON in single string I believe that that should not be a problem.

                       

                      When I am trying to simple post using the HTTP trans the following error pops up, not sure it is a problem in REST side or am I missing something. I created a connection with the credentials and using that connection with the HTTP trans

                       

                      • 8. Re: Posting a JSON File to a REST API using Informatica PowerCenter
                        Nico Heinze Guru

                        "Connection refused" usually means that a firewall rule is blocking the connection. Usually, not always. But please check this with your firewall / network security team.

                         

                        Regards,

                        Nico

                        • 9. Re: Posting a JSON File to a REST API using Informatica PowerCenter
                          Ziad Ali Active Member

                          Hey Aziz,

                          Do you know if we could use Java transformation to post a file onto REST?

                           

                          Appreciate you help

                          • 10. Re: Posting a JSON File to a REST API using Informatica PowerCenter
                            Ziad Ali Active Member

                            Hey Nico,

                             

                            I did reach out to them they are still working on it, in the mean time do you know if we could use Java transformation to post a file or data to REST?

                             

                            Appreciate you help

                            • 11. Re: Posting a JSON File to a REST API using Informatica PowerCenter
                              Nico Heinze Guru

                              Definitely yes, but to be frank that's not my area of expertise. I only know for sure that you can invoke REST web services from Java code, but I don't know how and with what JAR files / libraries and the like.

                               

                              What I can help with is how to handle that stuff in a Java transformation (JTX). I know the gotchas and advantages of the JTX pretty well, so that need not become a problem. Just make sure that from a command-line tool you can invoke this REST web service, then we can focus on embedding this in a JTX and an appropriate mapping.

                               

                              Regards,

                              Nico

                              • 12. Re: Posting a JSON File to a REST API using Informatica PowerCenter
                                Ziad Ali Active Member

                                Nico thanks for your responses.

                                 

                                I found something I can use but I am not sure on how to pass credentials unto this code.

                                 

                                • 13. Re: Posting a JSON File to a REST API using Informatica PowerCenter
                                  Nico Heinze Guru

                                  You probably will have to analyse the HttpURLConnectionExample class and its methods; if there are methods to set the credentials, fine, then you can use it; if not, you will have to look at the source code and maybe a few other examples on the internet.

                                   

                                  Sorry that I can't give a better reply here, that's not my area of expertise.

                                   

                                  Regards,

                                  Nico

                                  • 14. Re: Posting a JSON File to a REST API using Informatica PowerCenter
                                    Ziad Ali Active Member

                                    Nico, thanks for all your responses. But I did this using powershell. I called powershell using .bat and POSTED the JSON file to REST.

                                    1 2 Previous Next