When I use %Header:my-header-id% in the Btach Merge connector, I just get files called “.xml”
Does this work?
Solved
Does %header:% work for the Batch Merge Connector
Best answer by James B
I think you might be able to manage this in the SQL Server connector right off the bat.
Do I understand that you’re selecting from some find of view that has records that represent multiple groups with a shared key column? Say I’ve got a simple table like this:
| ID (key) | OrderNumber | Customer | ItemName | Quantity |
|---|---|---|---|---|
| 1 | PO001 | James Blasingame | Keyboard | 1 |
| 2 | PO001 | James Blasingame | Mouse | 1 |
| 3 | PO002 | Teddy Blasingame | Vehicle Trucks Book | 2 |
| 4 | PO002 | Teddy Blasingame | Toy Truck | 3 |
| 5 | PO003 | Tommy Blasingame | Fuzzy Fox | 2 |
| 6 | PO003 | Tommy Blasingame | Ball | 1 |
Ordinarily, if these orders are contained in another table, I can query that table for unique records, and then use a foreign key to select from child tables, but in a given output mapping I can also call the same table more than once to achieve the same effect.
If I configure an output mapping against this table like:
<Items>
<OrderLines table="`OrderLines`" allColumns="true" selectQuery="SELECT DISTINCT `OrderNumber` FROM `OrderLines`">
<OrderNumber key="true" />
<OrderLines table="`OrderLines`" selectQuery="SELECT * FROM `OrderLines` WHERE `OrderNumber` = ${OrderNumber}">
<ID type="int" key="true" />
<Customer />
<ItemName />
<OrderNumber />
<Quantity type="int" />
</OrderLines>
</OrderLines>
</Items>
Then I can configure an output mapping where the first query just selects the distinct keys from the table, and uses it as a WHERE clause to select unique records. If I leave the database connectors Max Records setting at 0, this will output one file per group, like so:
<?xml version="1.0" encoding="UTF-8"?>
<Items xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<OrderLines>
<OrderNumber>PO002</OrderNumber>
<OrderLines>
<ID>3</ID>
<Customer>Teddy Blasingame</Customer>
<ItemName>Vehicle Trucks Book</ItemName>
<OrderNumber>PO002</OrderNumber>
<Quantity>2</Quantity>
</OrderLines>
<OrderLines>
<ID>4</ID>
<Customer>Teddy Blasingame</Customer>
<ItemName>Toy Truck</ItemName>
<OrderNumber>PO002</OrderNumber>
<Quantity>3</Quantity>
</OrderLines>
</OrderLines>
</Items>
In addition, there’s another feature that you can take advantage of - if you notice the parent query, I edited the first query to define “OrderNumber” as the key column. That isn’t really the case, but if I instruct the connector that it is on that query, I can set the Local File Scheme on the connector to:
%PK%.xml
The net effect of this is that the output files are renamed with the value in that column:
I’ve zipped up the connector and sample SQLite company file used for this example here. Are you able to use this to group your records into named files at the first step?
This topic has been closed for replies.
I can reproduce this here, and I'll discuss this with our development team, but I'm not sure if this was an intended design of the BatchMerge connector or not - the individual members of the batch each have their own headers, but they lose this context in the merge. I do see that the headers are in the batch as well as a the messages within, so I’m not sure whether this is a simple fix or not.
Can you give me a little more detail on how the flow works here - where the header that you want to be used emerges from and what the batch is formed from? I might be able to come up with a workaround to get that same value back on the filename (especially if it comes from somewhere in the body)
Thanks James.
here’s my flow
Rows are pulled from an SQL Server table.
Each row of the result set has a SHIPMENT_NUMBER, Several rows can share the same SHIPMENT_NUMBER. Ultimately I want to create a file for each SHIPMENT_NUMBER that contains all rows with that SHIPMENT_NUMBER.
The script extracts the SHIPMENT_NUMBER and puts it into the header so we can create batches by SHIPMENT_NUMBER. This works fine.
Here’s the script (script header removed)
<!-- Read XML -->
<arc:set attr="input.uri" value="[FilePath]"/>
<arc:call op="xmlOpen" input="input">
<arc:set attr="xml.handle" value="[handle]" />
</arc:call>
<arc:set attr="xml.map:shipmentnumber" value="/Items/Shipment/SHIPMENT_NUMBER"/>
<arc:try>
<arc:call op="xmlDOMGet" in="xml" out="out1">
<arc:set attr="_message.header:Batch-Shipment-Number" value="[out1.shipmentnumber]"/> </arc:call>
<arc:finally>
<arc:call op="xmlClose" input="xml"/>
</arc:finally>
</arc:try>
<!-- This example simply reads the content of the input file and pushes it out. Use this
snippet as a starting point for manipulating the input file to produce output in
your flow. -->
<arc:set attr="in.file" value="[filepath]" />
<arc:call op="fileRead" in="in" out="out" >
<!-- Here is where you can access the content of the file via the 'file:data' param. -->
<arc:set attr="output.data" value="[out.file:data]" />
</arc:call>
<arc:set attr="output.FileName" value="[_message.header:Batch-Shipment-Number]-[_|guid()].xml" />
<arc:set attr="output.filepath" value="[filepath]" />
<arc:push item="output" />
But I want the filename to be the <SHIPMENT_NUMBER>.csv eventually.
I’m just looking at doing the file naming later in the flow.
thanks in advance
I’ve tried this on my File Connector, but i doesn’t rename the file
Log shows that it’s doesn't try to rename it
[2023-07-21T12:27:47.778] [Info] Arc Version: 23.0.8517.0
[2023-07-21T12:27:47.778] [Info] System Version: Microsoft Windows Server 2022 Standard
[2023-07-21T12:27:47.778] [Info] VM Version: 4.0.30319.42000
[2023-07-21T12:27:47.778] [Debug] Starting to send file: Batch-20230721122745555.csv
[2023-07-21T12:27:47.778] [Debug] Getting stream from file: D:\cData - Output\APG_UPS_Manifest_Processing\Batch-20230721122745555.csv
[2023-07-21T12:27:47.778] [Debug] Send directly. FilePath: D:\cData - Output\APG_UPS_Manifest_Processing\Batch-20230721122745555.csv
[2023-07-21T12:27:47.778] [Debug] Start copying stream.
[2023-07-21T12:27:47.778] [Debug] Copying stream completed.
[2023-07-21T12:27:47.794] [Info] Message MergeSameShipments-20230721-122746308-ofQE is finalized. Name: Batch-20230721122745555.csv, Type: Input, Status: Success, Message: , Processing Time: 16ms.
I’ve managed it by inserting a Script Connector
<!-- This example simply reads the content of the input file and pushes it out. Use this
snippet as a starting point for manipulating the input file to produce output in
your flow. -->
<arc:set attr="in.file" value="[filepath]" />
<arc:call op="fileRead" in="in" out="out" >
<!-- Here is where you can access the content of the file via the 'file:data' param. -->
<arc:set attr="output.data" value="[out.file:data]" />
</arc:call>
<arc:set attr="output.filename" value="[_message.header:Shipment-Number][filepath|fileext()]" />
<arc:set attr="output.filepath" value="[filepath]" />
<arc:push item="output" />
I think you might be able to manage this in the SQL Server connector right off the bat.
Do I understand that you’re selecting from some find of view that has records that represent multiple groups with a shared key column? Say I’ve got a simple table like this:
| ID (key) | OrderNumber | Customer | ItemName | Quantity |
|---|---|---|---|---|
| 1 | PO001 | James Blasingame | Keyboard | 1 |
| 2 | PO001 | James Blasingame | Mouse | 1 |
| 3 | PO002 | Teddy Blasingame | Vehicle Trucks Book | 2 |
| 4 | PO002 | Teddy Blasingame | Toy Truck | 3 |
| 5 | PO003 | Tommy Blasingame | Fuzzy Fox | 2 |
| 6 | PO003 | Tommy Blasingame | Ball | 1 |
Ordinarily, if these orders are contained in another table, I can query that table for unique records, and then use a foreign key to select from child tables, but in a given output mapping I can also call the same table more than once to achieve the same effect.
If I configure an output mapping against this table like:
<Items>
<OrderLines table="`OrderLines`" allColumns="true" selectQuery="SELECT DISTINCT `OrderNumber` FROM `OrderLines`">
<OrderNumber key="true" />
<OrderLines table="`OrderLines`" selectQuery="SELECT * FROM `OrderLines` WHERE `OrderNumber` = ${OrderNumber}">
<ID type="int" key="true" />
<Customer />
<ItemName />
<OrderNumber />
<Quantity type="int" />
</OrderLines>
</OrderLines>
</Items>
Then I can configure an output mapping where the first query just selects the distinct keys from the table, and uses it as a WHERE clause to select unique records. If I leave the database connectors Max Records setting at 0, this will output one file per group, like so:
<?xml version="1.0" encoding="UTF-8"?>
<Items xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<OrderLines>
<OrderNumber>PO002</OrderNumber>
<OrderLines>
<ID>3</ID>
<Customer>Teddy Blasingame</Customer>
<ItemName>Vehicle Trucks Book</ItemName>
<OrderNumber>PO002</OrderNumber>
<Quantity>2</Quantity>
</OrderLines>
<OrderLines>
<ID>4</ID>
<Customer>Teddy Blasingame</Customer>
<ItemName>Toy Truck</ItemName>
<OrderNumber>PO002</OrderNumber>
<Quantity>3</Quantity>
</OrderLines>
</OrderLines>
</Items>
In addition, there’s another feature that you can take advantage of - if you notice the parent query, I edited the first query to define “OrderNumber” as the key column. That isn’t really the case, but if I instruct the connector that it is on that query, I can set the Local File Scheme on the connector to:
%PK%.xml
The net effect of this is that the output files are renamed with the value in that column:
I’ve zipped up the connector and sample SQLite company file used for this example here. Are you able to use this to group your records into named files at the first step?
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
