I have a small Requirement for KM modification...
Source : MS SQL Server
Target : File
I have a table with 100 rows i want to load it to multiples files (files need to created automatically ) like........
File_1: 10 rows
File_2: 10 rows
File_3: 10 rows
File_10: 10 rows
i want to split one table record to multiple files using IKM (Which will do this)
All files need to have the Header....
hope this is clear.....
I have to have one interface for this.
Please any body can suggest the best way to do this.....
I can see no way to do that with only one interface in the current version of ODI.
Future versions might include multi-loading features and you will be able to play with the number of row.
For the moment I would go for a procedure.
Actually, it won't be a "small" KM modification.
But it may be possible.
Based on the "IKM SQL to File Append" and "LKM File to SQL" First, modify the "LKM File to SQL"
- in the "Create work table" step, add a new column named RANK_ROW (number)
- in the "Load data" step, populate this new column. For example, with rownum (1, 2, 3, 4...). Then, modify the "IKM SQL to File Append"
- Duplicate 10 times the steps "Truncate target file" ; "Insert column headers" and "Insert new rows"
- In each step, modify <%=snpRef.getTargetTable("TABLE_NAME")%> to add the number. Example <%=snpRef.getTargetTable("TABLE_NAME")%>_1 ; <%=snpRef.getTargetTable("TABLE_NAME")%>_2 ...
- In "Insert new rows" step, modify the "command as source" to add FILTER in the where clause.
Filter on the RANK.
For example, in the first one, you write "and RANK between 1 and 9"
In the second one, "and RANK between 10 and 19"
You could improve that by using Jython to loop on the rank, and avoid duplicate the steps... but it will be more complicated.
I once had a similar task and successfully used the approach described here: https://blogs.oracle.com/dataintegration/entry/odi_11g_parallel_pipelined_unload
See, if this might be helpful for you, too.
Hi A. Drieux,
Thanks for the response !
Actually i don't need the "LKM File to SQL" because my source is MS SQL Server so i am having Staging on Source so in that case i don't need to have the LKM, only IKM i required which is "IKM MS SQL to FILE APPEND".
so can you please help me bit to modify the IKM only for the purpose.
appreciate your help !
You can use the same logic, even without LKM.
You juste have to create this "rank" column on your master source table.
Then filter on it in each duplicated step of your IKM.
If you're not allowed to create this column on source table, then create a temporary table (with an extra interface) that will store your data with this rank. You can for example use "yellow interface" to do this.
PS : do no hesitate to mark helpful posts as "correct" or "helpful".