I am using SSIS 2005 to extract from source, here is my this is my query, SELECT IMITNR,PgmCode = CASE SUBSTRING(PARTCTRYORG,1,2) WHEN 'US' THEN 'NAFTA' WHEN 'CA' THEN 'NAFTA' WHEN 'MX' THEN 'NAFTA' ELSE ' ' END, ITMCOST FROM dbo.tmp_Camp This sample data IMITNR PgmCode Cost S-5512-CSTM NAFTA 0.970 ARTWORK NAFTA 65.000 BLEED CHARGE NAFTA 25.000 BOX DIE NA 525.000 CANCEL CHRG NA 167.600 I want output as XML in following format <TRANSACTION> <IMITNR>S-5512-CSTM</><PgmCode>NAFTA</PgmCode><Cost>0.970</PgmCode> </TRANSACTION> <TRANSACTION> <IMITNR>ARTWORK</><PgmCode>NAFTA</PgmCode><Cost>65.000</PgmCode> </TRANSACTION> <TRANSACTION> <IMITNR>BLEED CHARGE</><PgmCode>NAFTA</PgmCode><Cost>525.000</PgmCode> </TRANSACTION> Since there is no XML destination in SSIS, Is this possible if not, what is way to achive this Thanks Vaishu

You may want to investigate the FOR XML clause for the SELECT statement. You can also handcraft this XML (quite easily) using a Derived Column component, with an expression like: "<TRANSACTION><IMITNR>" + [IMITNR] + "</IMITNR>"<PgmCode>" + [PgmCode] ...etc... Then send that one column to a text file. Talk to me now on

Look at this blog entry. It gives step-by-step details. http://businessincredulous.blogspot.com/2012/05/ssis-xml-output.html