Problem With SSIS package to export to XML

Question

Post reply

Problem With SSIS package to export to XML

CGSJohnson

SSCertifiable

Points: 7208
More actions
June 9, 2010 at 11:27 am

#387110

Good afternoon. I have been trying for several days now to export data from SQL server to an .XML file. Maybe someone can think of/see something that I can't. Below is what I have tried so far. Please let me know if you have any ideas.
- I have tried several methods, including outputting the data from SQL server as an XML recordset, using an Execute SQL task and then using a script to write the data to the file. In this instance, with logging enabled, the SQL task completes, but then nothing happens after that.
- I have also tried just outputting the xml data as a single column of text data and then writing to the file. In this instance, I receive an error message about a problem encountered with the primeoutput.
- Finally, I have tried, once again, outputting the data from SQL server as text data, using an Execute SQL task and then using the "Export" column destination. This results in the same problem, as above...the query executes, but nothign more.
Nothing ever gets written to the file. I have ran the query (see below) and it returns results just fine.
select convert(varchar(255), 'C:\Test.txt') as nameoffile, convert(varchar(max),(SELECT * FROM dbo.table
WHERE columnname like ? for xml path ('PathName'), root('RootName'), elements XSINIL)) as XMLOutput
Any ideas? Each row in the data set of about 450,000 records is fairly wideLet me know if you need more information.
Thanks...Chris

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

This might be of interest:

http://consultingblogs.emc.com/jamiethomson/archive/2006/07/11/SSIS_3A00_-Output-a-file-as-a-csv-or-an-XML-file.aspx

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

SSIS_NewBi Right there with Babe Points: 776 More actions · Answer 2

Can you post some same data of txt file and the table.

CGSJohnson SSCertifiable Points: 7208 More actions · Answer 3

I've already tried using a script component, which hasn't worked.

No data is outputted to the text file, which is the problem. I will post some of the data later on today, if I can.

CGSJohnson SSCertifiable Points: 7208 More actions · Answer 4

I have determined that it is either a problem with the XML string being returned from the query (it is too long) or the query to generate the XML string (it takes too long to run). So, I will try to do it piece-mill. Thanks for the responses.

- Chris