SSIS CSV to XML

Hi,

I was wondering if the following is possible in SSIS and if someone could point me in the right direction...

Basically I would like to convert CSV files provided by customers into an XML file, how the position of the required columns in CSV may vary depending on customer.

For example we will require the following columns to be appended to the XML:

>> Account Code
>> Collection Date
>> Delivery Date
>> Product Code
>> Quantity

Customer A may send a file that has the following columns in order:

>> Collection Date
>> Delivery Date
>> Product Code
>> Quantity
>> Description
>> Type
>> Account

Customer B may send a file that has the following columns in order:

>> Product Code
>> Collection Date
>> Delivery Date
>> Account
>> Quantity
>> Description1
>> Description2

Is it possible to setup a mapping for each customer. For example a SQL table that looks like:
MappingTable.jpg
So the number in the table would show where the column is positioned in the customers text file.

I hope this makes sense...

Thanks, Greg
spen_langAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
lcohanConnect With a Mentor Database AnalystCommented:
Here's one way to do it outside SQL:

http://social.technet.microsoft.com/Forums/windowsserver/en-US/921bb5a8-c744-40de-899f-9121449ede83/merge-csv-files


In SQL you could use SSIS - Data Flow task with a MERGE connector (or UNION ALL transformation) to pull the files into the same data stream. Then output to a new file.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.