meninga
asked on
Using SSIS to convert csv to xml
I have a sample CSV file containg data in the following format:
CandidateID,CQN,CentreCode ,ExamRef,F orename,Su rname,Midd lename,DOB ,Gender
123456,ABC123,A0001CZ,XYZ1 23,Joe,Blo gs,,21/02/ 1990,M
123456,ABC124,A0001CZ,XYZ9 87,Joe,Blo gs,,21/02/ 1990,M
123457,ABC125,B0006CZ,XYZ1 23,Jane,Bl ogs,,18/05 /1990,F
123457,ABC126,B0006CZ,XYZ9 87,Jane,Bl ogs,,18/05 /1990,F
I need to convert this csv data into xml format which will provide the xml source for an SSIS package I have written. The xml source needs to be in the following format:
<data>
<user>
<id>0</id>
<forename>Dave</forename>
<surname>Dixon</surname>
<middleName>Wakefield</mid dleName>
<dob>07/10/1982</dob>
<gender>M</gender>
<addressLine1>31 Marlborough Road</addressLine1>
<addressLine2/>
<town>Shipley</town>
<county>7</county>
<country>0</country>
<postCode>BD18 3NX</postCode>
<telephone>22222</telephon e>
<email>example@test.com</e mail>
<ethnicOrigin>0</ethnicOri gin>
<accountExpiryDate>07/03/2 009</accou ntExpiryDa te>
<username>user_xyz</userna me>
<extraInfo>
<specialRequirements/>
</extraInfo>
<specialRequirements/>
<candidateRef>xyz</candida teRef>
<centreRoles>
<centre id="1">
<added>
<i>6</i>
</added>
<removed/>
</centre>
</centreRoles>
<centres>
<added/>
<removed/>
</centres>
<qualifications>
<added/>
<removed/>
</qualifications>
<retired>0</retired>
</user>
</data>
Is it possible to transform the CSV data to XML in SSIS? Thanks
CandidateID,CQN,CentreCode
123456,ABC123,A0001CZ,XYZ1
123456,ABC124,A0001CZ,XYZ9
123457,ABC125,B0006CZ,XYZ1
123457,ABC126,B0006CZ,XYZ9
I need to convert this csv data into xml format which will provide the xml source for an SSIS package I have written. The xml source needs to be in the following format:
<data>
<user>
<id>0</id>
<forename>Dave</forename>
<surname>Dixon</surname>
<middleName>Wakefield</mid
<dob>07/10/1982</dob>
<gender>M</gender>
<addressLine1>31 Marlborough Road</addressLine1>
<addressLine2/>
<town>Shipley</town>
<county>7</county>
<country>0</country>
<postCode>BD18 3NX</postCode>
<telephone>22222</telephon
<email>example@test.com</e
<ethnicOrigin>0</ethnicOri
<accountExpiryDate>07/03/2
<username>user_xyz</userna
<extraInfo>
<specialRequirements/>
</extraInfo>
<specialRequirements/>
<candidateRef>xyz</candida
<centreRoles>
<centre id="1">
<added>
<i>6</i>
</added>
<removed/>
</centre>
</centreRoles>
<centres>
<added/>
<removed/>
</centres>
<qualifications>
<added/>
<removed/>
</qualifications>
<retired>0</retired>
</user>
</data>
Is it possible to transform the CSV data to XML in SSIS? Thanks
ASKER
Thanks smar. I want to avoid using a staging table so I've worked out how to transform the data using the Script Component. This XML now complies to the format required to execute a stored procedure.
Congratulations. Do you mean SSIS Script task? In version 2005 it can be coded only in VB.NET - it has nothing to do with C#.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
msdn defintion
http://msdn.microsoft.com/en-us/library/ms173812.aspx
Longer text from magazin:
http://msdn.microsoft.com/en-us/library/aa175790(SQL.80).aspx