Solved

SSIS CSV to XML

Posted on 2014-03-27
1
221 Views
Last Modified: 2016-02-10
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
0
Comment
Question by:spen_lang
1 Comment
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
Comment Utility
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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

In couple weeks ago, I encountered an extremely difficult problem while deploying 2008 SSIS packages to a new environment (SQL Server 2014 standard).  My scenario is: We have one C# application that is calling 2008R2 SSIS packages to load text fi…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now