July 3, 2006 at 7:45 am
Please help this is driving me bananas
Does anyone know why there is no faciilty with SSIS to export text as XML, only as a file source ?
I have a requirement to export to XML from within SQL2005 and I can't believe MS hasn't got the facility to do it.
Thanks.
July 4, 2006 at 1:52 am
I'm no SSIS guru but this is how I managed to do it
1. Create new package
2. Add SQL connection manager for server you're connecting to
3. Add Execute SQL task to Control Flow
4. Set Result Set property to XML
5. Set connection properties to your connection manager and enter a SQL statement which uses FOR XML to return an XML stream
6. Click on Result Set on the left hand list (under Parameter Mapping)
7. Set the Result Name to be 0 (zero)
8. Create a new String variable to assign the result to
9. Next add a Script task and write out the contents of the variable to a file as follows
Public Sub Main()
Dim sw As New IO.StreamWriter("C:\Temp\test.xml")
sw.Write(Dts.Variables("User::XMLContent").Value.ToString())
sw.Dispose()
Dts.TaskResult = Dts.Results.Success
End Sub
Obviously you can create a variable for the output file name, add error handling and generally polish the package all round but running this did generate an xml file (with root element added even) in the location I needed.
Hope this helps
Obviously I've hardcode
July 4, 2006 at 4:42 pm
With a flat file source its easy to define the format of the file (delimited, fixed width etc...) if its XML, how do you want the data outputted? An XML file is so open to formatting it would be wrong (in my opinion) for SSIS to define how the data should be formatted. I'm guessing that's the thinking that went into the lack of an XML destination.
In the meantime, Donald Farmer's book tells you how to do exactly what you want to do using the script task. Available from all good online bookshops.
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
April 14, 2007 at 12:53 am
Is there a way to get SSIS to not include the element? I am already defining this in my Stored Proc returning the XML.
April 21, 2007 at 9:40 pm
Probably a bit too late for those originating the thread but if you're still looking for an XML destination, take a look at the (SSIS) sample; datasetdestination - it has an option to write the dataset contents to an xml file after completion.
Steve.
May 16, 2007 at 2:17 pm
Where is the "(SSIS) sample; datasetdestination "
May 16, 2007 at 2:37 pm
If you've installed to the default dirs, then
C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Programming Samples\Data Flow\DataSetDestination Component Sample
I think BOL covers off how to set it up.
Steve.
November 8, 2007 at 1:00 am
Guys I have been given a task where a client gives me a source in the form of .tx or .csv format. I am requested to output the outcome as an XML in SSIS.
Anyone help appreciated. (A sample also will be fine)
December 3, 2007 at 10:01 am
Hello!
I'm new in here and i have a question (a problem!) about how to export XML using SSIS. I have a table with one column (that i queried from with a Execute SQL Task). This column has a XML data in each row. So each cell has different XML data and i want to send the content of each of these cells to a different *.xml file in, for example,my Desktop. I'm thinking in using the "Foreach Loop Container" with "Foreach ADO Enumerator" (to retrive all cells), after the "Execute SQL Task", in order to run the whole result set (the column table with the xmls) but i don't know what to "put" inside the container... Plus, tere is no XML Destination. How do i send the xml data "away"?
Any help?
Thanks in advance,
Filipa
March 26, 2008 at 3:38 am
If you use Reporting Services you would use it to generate xml outputs. Use "Save as XML" in Report Manager or Visual studio. Xml structure of report is partially configurable - right click text box in data grid in Visual Studio when designing report and coose Properties and there tab "Data Output". On this place you can configure if you want data in attributes, elements etc. If reports use data grouping the elements in xml output are nested.
Unfortunatelly in xml export remains some system elements. The only way to delete it is xslt transformation (stylesheet), which could be referenced from report properties. But I didn't try this way yet.
July 2, 2008 at 2:18 am
There is a commercial XML Destination adapter available at http://www.keelio.com that should solve most of your needs for exporting data to XML
July 24, 2008 at 1:22 pm
It probably depends on the amount of data that you need to return, but a solution that worked for me was to generate the xml dataset in SQL (FOR XML AUTO, ROOT,ELEMENTS XSINIL) and then convert that dataset to varchar(max). This way it gets the DT_TEXT datatype in SSIS. I then use the Flat File Destination to generate my .xml output file.
July 25, 2008 at 2:19 am
Thanks for the replies.
Luckily this thread is so old now that I've moved jobs in the intervening period, and no longer have to worry about it !
December 5, 2008 at 10:53 am
Okay Gang.
I can officially say I have tried every posted method. If you are using Visual Studio Business Intelligence Studio, GO RIGHT NOW and get the Keelio XML Adapter for SQL. You can bypass the entire formatting issue altogether and simply build a standard query, and KEELIO will do all the work. I am in the position of needing to update my Google Base and an SLI Search Engine which both require Automated XML file building AND Automated FTP. I spend WEEKS trying to get a writable file in XML from SQL to no avail.
I went to http://www.keelio.com and downloaded the program. It was installed in two minutes...in six minutes, I had working XML, within an Hour and SSIS package was saved to SQL and running as a SQL Server Agent Job hourly.
I've been doing this since before SQL was a product. I am so old I pre-date relational databases.
Trust me, this works. AND they are giving me 30 days FREE to try it out, and it only takes about one day to really see it work.
Side Note: My version had a problem with N_TEXT, but I was able to get around it by using the CAST statement to convert it to regular text. It is my understanding that the release dated AFTER 12/7/08 will have this resolved.
December 5, 2008 at 11:00 am
Sorry J, but I gotta disagree...
I do not believe in reinventing the wheel, nor do I accept that I need to become a script junkie to convert a simple query into a dependable XML output. I downloaded the Keelio 30 day trial for free, and was up and running in one day, with the format I wanted, laid out the way I choose. But understand, my objection is not to spending days reading and trying things. My objection is to paying a consultant 75 bucks an hour for seven days to do what Keelio can do in seven minutes. It's just not good business.
IF YOU ARE USING THIS FOR BUSINESS....
go to keelio.com and save yourself a ton of money in programming, a ton of headaches in documentation and a screaming nightmare in formatting issues and just BUY a working solution instead of killing yourself trying to create one. All of these "buy a book" and "try this chuck of code" solutions are obviously written by IT gurus who have no connection to the reality of the economics of paying them. It's simple math. One consultant, spending three full days on this at 75 an hour comes to: $1,800.00.
How much is your time WORTH?
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply