Database Columns values mismatch when creating csv pipe seperated file from database records

user_1767265 0
Viewed 111

Mule 4 App has been doing select from a SQL Server database around 20k rows and convert result set into pipe separated csv file and push the file to s3 using s3 connector.

In the database, we have columns like aId, bId, cId etc. When we checked the data in the csv file, value for aId is actual value for bId, value for bId is the actual value for cId in the database. ( only few places)

<db:select doc:name="Select" doc:id="e8d1eda4-3ecc-48e9-90ea-a19a0a4e20f9" config-ref="MSSQL_Database_Config">
    <db:sql >#['SELECT * FROM Table']</db:sql>
</db:select>
<ee:transform doc:name="Set Payload" doc:id="9b7e5bcc-e61b-427d-8507-8655db76cb83">
    <ee:message>
        <ee:set-payload><![CDATA[%dw 2.0
    output application/csv separator='|'
    ---
    payload]]></ee:set-payload>
    </ee:message>
</ee:transform>
<s3:create-object doc:name="Create object" doc:id="beea8757-7e69-4119-8529-e0c26be08b65" config-ref="Amazon_S3_Configuration" bucketName="bucket1" key="file123"/>

We have double check the database value, it confirm somehow the mule is changing the order of the columns value ( few places)

For example, in the database, aId bId cId values are like 1, 2, 3

In the CSV file, aId|bId|cId values are 2,3,1 (in a few places)

I have not configured anything extra in db connector/aws streaming all are default. Is there anything do i need to enable to fix this issue.

Thanks in advance

mule4 dataweave mule
1 Answers

Changing the repeatable file store values (database connector) to 50000 from 500 helps to resolve the issue. I raised the ticket with Mulesoft Support. Update here if I get any solution.

<db:select doc:name="Select" doc:id="e8d1eda4-3ecc-48e9-90ea-a19a0a4e20f9" config-ref="MSSQL_Database_Config">
    <ee:repeatable-file-store-iterable inMemoryObjects="50000" />
    <db:sql >#['SELECT * FROM Table']</db:sql>
</db:select>
user_17672650
Related