Parsing Batch Results into Individual XML files

Question

Post reply

Parsing Batch Results into Individual XML files

KJKKPSI

SSCommitted

Points: 1521
More actions
August 2, 2013 at 9:10 am

#280747

I am building an ordering system and need to output each completed order, including detail records to an XML output file, for archiving purposes. The orders/details are generated via an SSIS package as a batch based on a number of user settings, and it is completely possible and likely that multiple orders for multiple clients will be generated in a single execution. I need each order separated out into it's own XML output file at the end of this process, and dropped in a folder.
I believe I am good to go on how to generate the XML String in the T-SQL portion of the process, but I am unsure of how to get SSIS to put each result set into it's own file, and uniquely named.
Any help would be appreciated.
Thank you

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

KJKKPSI SSCommitted Points: 1521 More actions · Answer 1

I believe I've worked out my issue.

Thanks anyways.

aaron.reese SSChampion Points: 13498 More actions · Answer 2

Please post your solution for others to learn from.

If it was me, I would

Create an object variable

Create a dataflow task

in the DFT:

create an ole source which is the SQL expression to create the dataset

send the datapipe to a recordset which is stored in the object

Create a foreach loop

in the FEL:

iterate over the object variable and for each object assign the values to variables

Create a dataflow task

in the DFT:

use the variable values to determine the filename and contents to generate a unique file

you will now have one package than can output any number of unique files with a consistent data contents

KJKKPSI SSCommitted Points: 1521 More actions · Answer 3

aaron.reese (8/8/2013)
Please post your solution for others to learn from.
If it was me, I would
Create an object variable
Create a dataflow task
in the DFT:
create an ole source which is the SQL expression to create the dataset
send the datapipe to a recordset which is stored in the object
Create a foreach loop
in the FEL:
iterate over the object variable and for each object assign the values to variables
Create a dataflow task
in the DFT:
use the variable values to determine the filename and contents to generate a unique file
you will now have one package than can output any number of unique files with a consistent data contents

You nailed it Aaron, that is the exact solution I came up with.

I started with my OrderGeneration procedure, then grabbed a unique set of OrderIDs from the table and placed them into an Object variable called "OrderResultSet". I then enter a For Each Loop, setting my PurchaseOrderNumber and CurrentOrderID for each iteration. These two variables will be parsed together into the FileName variable, which will identify the name and physical location of the file. Next I checked for the existence of the to-be-created XML file, using a Script Task, and if not exists, generate the desired XML string by calling a stored procedure, add the FileName to the result set using a Derived Column, and using the Export Column Transformation, push the XML to the output location.