blogs.conchango.com

Hello Jamie,

How are you ? I have not worked on SSIS for 7 months, now I come back to SSIS with an XML project. Could you tell me how I can get the result of the SQL query (via OLEDB query) that is written like "SELECT * FROM XXX FOR XML AUTO", into a DT_TEXT format. As this query executed with OLEDB retrieves a dataflow with the DT_IMAGE type ! (I need to transfer this data into a XML file).

Thank you so much for your advice !

Does somebody has a solution ? Thanks in advance !

Yen

Having the same problem as Yen and googling it has turned up NOTHING!

To create an XML file from a SQL stored procedure in SSIS, try this:

In SQL Server:

1. Create a proc called “spGenerateXML” that contains your SELECT FOR XML statement

In SSIS:

1. Create a Package level string variable called "XMLVariable"

2. In Connection Manager, create a New OLEDB Connection called "SQLDB"

3. In Connection Manager, create a New File Connection called "XMLFile"

4. On Control Flow tab

  a) Add Execute SQL Task

  b) Add Script Task

  c) Connect Execute SQL Task (parent) to Script Task (child)

5. Edit Execute SQL Task, General section

  a) Change Result Set to "XML"

  b) Set Connection to "SQLDB"

  c) Set SQLStatement to "EXEC spGenerateXML"

6. Edit Execute SQL Task, Result Set section

  a) Click Add button

  b) Set Result Name to 0

  c) Set Variable Name to "User::XMLVariable"

7. Edit Script Task, Script Section

  a) Set ReadOnlyVariables to "XMLVariable"

  b) Click Design Script button

  c) Replace "Main" Subroutine with:

Public Sub Main()

  Dim XMLString As String = Nothing

  Dim fs As StreamWriter = Nothing

  Dim strfilename As String = _

DirectCast(Dts.Connections("XMLFile").AcquireConnection(Dts.Transaction),_

String)

  XMLString = Dts.Variables("XMLVariable").Value.ToString.Replace("<ROOT>",_

"").Replace("</ROOT>", "")

 'XMLString = Dts.Variables("XMLVariable").Value.ToString

  fs = New StreamWriter(strfilename, False)

  fs.Write(XMLString)

  fs.Close()

  Dts.TaskResult = Dts.Results.Success

End Sub

Note: The XML my proc created had a different ROOT element, so I had to strip off the ROOTs before saving the file.

thank you Morgen, it really works !!! i've done only small changes to your script, it took me only 5 minutes and i was finished with my package... :)

I had to add the following lines to the script task.  Thanks for the step by step!

I was also up in 5 minutes.  WooHoo!

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Imports System.Xml

This was very helpfull. It saved me a  whole bunch of work.

My sincere thanks

@Morgan: awesome "template"!

I had to change StreamWriter to IO.StreamWriter

Hi

I'm trying somethign similar but am getting stuck on the import of the XML into another database. SSIS does not seem to like the schema that SQL Server includes in the XML file.

Could you paste a copy of the SQL comamnd you're using, or at least the parameters that have to do with the XML output? I'm using this:

select * from myTable

for xml auto, ELEMENTS XSINIL,  root('myTable')

Thx!

Hi

I have this working for small and medium size files OK but when I do an XML select on the whole database to output about 50mb of data the package just hangs.

I would guess its running out of memory but there is no way to set memory that I can see.

(I can output the same data as a CSV file by using a dataflow task)

Anyone got any ideas.

Hi Morgan,

Thanks it worked for me

That is a great step by step.  I've spent an hour reviewing this subject online and your page was the most clear for me.

Thanks!!!

If you are working on generating a small XML, using string variable is ok.  But if you have to generate a large XML file, you will get the out of memory error.  In this case, you will need to use sqlcmd, I use this to write my XML file (>4GB) in just 4 mins on the server.

Hi,

I have a similar problem (my task is to export some parent-child data to XML file and send it trough mail ). I done the above described task BUT I always get an empty XML file. After debug the script I realize that the problem is in line

XMLString = Dts.Variables("User::XMLVariable").Value.ToString()

I always get a "...MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL Server" Description="No description provided"?...."

why? I'm pretty sure that data in not more that 4gb... but it seams that XMLVariable dont get the correct result.

I create XML like :

SELECT *,(SELECT * from Declaratie_data Ddata where  Declaratie.Declaratiecode = Ddata.Declaratiecode

      FOR XML AUTO, TYPE)

from Declaraties Declaratie where Declaratie.Logdatum > getDate()-1

FOR XML AUTO, TYPE, ROOT('doc')

Also I what to ask Eward to put some example of usage of "...sqlcmd..."  if he have time

Sorry for my Tarzan English :-)

Keelio Software has an XML destination adapter (along with a lot of other addons for SSIS) that you can purchase at http://www.keelio.com

the problem with ......MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL Server" Description="No description provided"?.... ERROR RESOLVED

The problem was that in SQL task - where I made a XML

from DB the result has to be the EXACTLY the same name as a Variable that is

holding a result.

so if your variable has a XMLvar name your SQL statement that create XML should be something like

select (select * from <Table> for XML ) as XMLvar

cheers

--select (select * from <Table> for XML ) as XMLvar

No luck to me .... must be something else. Anybody?

Got it! Must use Ado.NET connection.  the select stmnt is NOT needed!

Thank you for the code, and for the wonderful step by step directions--it looks like it doesn't like the CAST piece--I am getting the following runtime error:

Unable to cast COM object of type 'System.__ComObject' to class type 'System.String'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.

Is there a reference I need to set that I am missing?