You can apply this looping concept through Unix script.
Add a sequence to the records you are going to pass to Webservices if the sequence in the table is not there.
Take 2 variables in Unix script - Tot_Rec_Cnt and Web_Serv_Cnt. Deduct this 'Web_Serv_Cnt for every 100 records you pass to Web services and loop through until 'Tot_Rec_Cnt > 0'.
Hope this helps !
May I ask why you want to execute the session N number of times. Wouldn't it be easier in your case to simply "collect" 100 records, issue a Web Service call for these, then "collect" the next 100 records, issue the next call, and so on?
Granted, it is a little tricky to implement that in a mapping, but it can be done, and it would save you the hassle of repeatedly executing the session.
BTW you can do that both using Unix scripts and Windows batch or PowerShell scripts, no question.
Thanks Mohan. Will explore the idea.
Thanks Nico for the suggestion, but how can Informatica dynamically pick up 100 records?
1 of 1 people found this helpful
There are different ways to achieve that.
First way is in the Source Qualifier. Many SQL dialects offer some way to restrict the total count of records. For example, in Oracle you could use the following WHERE filter:
AND ROWNUM <= 100
In SQL Server you can use the following means:
SELECT TOP 100 ...
I seem to recall that in Db2 there's also a way, but at this moment I don't recall how. Please ask some DB2 specialist if necessary.
Again, this is DB specific.
The second way is to build your mapping such that you can restrict the number of records processed. However, before explaining how to do that it might be worth examining how exactly this web service is called. Do you want to invoke this web service several times, each time with a set of 100 input records?
If that's the case, then I can describe it in my next post how that can be achieved within the mapping.
Do you want to invoke this web service several times, each time with a set of 100 input records?
Yes this is our exact requirement. Currently web services pick up complete set of records and load it, we need to break it into chunks.The looping needs to be dynamic until the last record in the table.
The rough outline of the process goes like this:
You start by feeding all records into an Expression transformation.
Within this EXP, you set up a variable port of type Bigint to count all incoming records from 1 to whatever count of input records. Output the value of this variable port via an output port COUNTER of type Bigint.
Make sure that this same EXP also outputs one more output port GROUP_NO of type Integer with the constant value of 1; GROUP_NO should be above COUNTER in the port list (this isn't strictly necessary but makes life easier a little later).
Next you need a "dummy" source, for example, a flat file with just one single port of type String, set the input type in the session to Command instead of File, hit the Tab key (this ensures that the Workflow Manager will switch from the File output details to the Command output details), and enter this command:
Feed the string port from this "dummy" source into an EXP which outputs the following two ports:
1.: GROUP_NO, integer, constant value 2.
2.: COUNTER, bigint, constant value 1.
Now join the two data streams based on GROUP_NO followed by COUNTER. The Joiner must be set to Sorted Input and performs a Full Outer Join. The stream from the "dummy" source is the Master source.
Now comes the tricky part which I cannot describe in detail. Simply because it's way too long ago that I've worked with a web service consumer transformation. What I can tell you is that you have to feed 100 records into a WSC as the "detail" information and that for each 100th record AND for the very last record which comes from the "dummy" source you have to create the envelope of the web service call and actually do the call.
How that can be done in detail: again, I can't tell because first I don't know the structure of your web service and second because it's simply too long ago.
So I only can hope that you can take the description above to some use.
Whatever I left unclear, please ask for more details / explanation. As long as you don't ask me about the web service consumer transformation, I'll do my best.