CODEWITHSUNDEEP
ssis
Jin Yong
Jin Yong

Reputation: 43778

How can I read the ResultCSV from the XML file with SSIS then write it into CSV file or database?

Does any one know how can I read the ResultCSV from the XML file with SSIS then write it into CSV file?

I have a SSIS package will call to the web service and return the following result to a XML file:

<?xml version="1.0" encoding="utf-16"?>
<string>{"result_code":1,"result_message":"Calling List Export Successful","total_records":0,"total_processed":0,"total_excluded":0,"ResultCSV":"record_id,contact_info,contact_info_type,record_type,record_status,call_result,attempt,dial_sched_time,call_time,daily_from,daily_till,tz_dbid,campaign_id,agent_id,chain_id,chain_n,group_id,app_id,treatments,media_ref,email_subject,email_template_id,switch_id,CAMPAIGNNAME,CLIENTID,CREATE_DATE,CUSTOMERNAME,CUSTOMERPCODE,CUSTOMERSTATE,CUSTOMERSUBURB,DIAL_DROM,DIN,DNC_CLEAN,Filter1,Filter2,GNAF_PID,NO_DIAL,SCHEDULE,SMARTCALLRESULT,TELE_LASTCONTACT,SELL_CODE,CAMPAIGNID,PROSPECT_ID\r\n2030660,3740411111111,1,3,2,9,3,1392618825,,28800,72000,116,,,1,1,,,,,,,,OPEN_TOP_MEDIA,1164098,30/05/2014 12:00:00 AM,Janelle de Ano,1234,VIC,,30/05/2014 12:00:00 AM,1,6/03/2014 12:00:00 AM,0,,,0,,,,,413,1164098\r\n2030661,3740432715529,1,2,3,33,1,,,28800,72000,116,,,2,1,,,,,,,,OPEN_TOP_MEDIA,1164214,30/05/2014 12:00:00 AM,Lawrence nyguen,1234,VIC,,30/05/2014 12:00:00 AM,1,7/03/2014 12:00:00 AM,0,,,0,,,,,413,1164214\r\n","number_callinglist_records":0}</string>

How can I using SSIS to read through the xml file and get ResultCSV value then write it into the database or CSV file?

any tutorial or example?

Upvotes: 0

Views: 107

Answers (1)

Jayvee
Jayvee

Reputation: 10875

As inside the node you actually have json format (i.e. key-value pairs, colon- separated), once thing you can do is ignore the fact that is an xml and read the file as a colon-separated flat file.

Then you need to identify the record that contains the result, for example if it's always the last one, as in the example you posted, then you can put a conditional split that splits the records in result and others, being "result" the one you're interested in and that you can identify because it will contain the string "}".

Next, you can remove "}" from the record using a derived column transformation.

Finally, the record with the result, now without "}" at the end, is your comma-separated file, which you can save to a csv file.

Upvotes: 0

Related Questions