July 17, 2014 at 4:46 am
Hi all,
I have a pretty straight forward task, but since I rarely use SSIS I am already stuck ...
There is a XML-file in a folder. I want to load this file into a variable (as XML). Then I would pass this variable as a parameter to a stored procedure, which processes the XML-input from the variable.
I know that I can do this with a openrowset bulk import in tsql. But I guess this also can be done with a ssis package, right?
Thanks for any tips.
Reto E.
July 17, 2014 at 5:05 am
You cannot load directly to a TSQL variable in SSIS (two different tools).
You can perhaps load the XML file as one giant string into a temp table and then use an Execute SQL Task to load it into a variable and execute your stored procedure.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 17, 2014 at 5:58 am
Yes.
I now load the xml-file into a variable with a script task. This works great.
found this with google:
Public Sub Main()
'initialize StreamReader class for text file
Dim streamReader As New StreamReader("c:\Data_Exchange\xml_test.xml")
' Read the StreamReader To End and assign to local variable
Dim StreamText As String = streamReader.ReadToEnd()
' assign SSIS variable with value of StreamText local variable.
Me.Dts.Variables("xml").Value = StreamText
Dts.TaskResult = ScriptResults.Success
End Sub
I will have to test the performance with large XML-files. But for now it seems to work fine.
July 17, 2014 at 6:12 am
Quick thought, I would, if possible, use SSIS to pass the file name and path to an OPENROWSET on the SQL Server.
😎
July 17, 2014 at 6:15 am
reto.eggenberger (7/17/2014)
Yes.I now load the xml-file into a variable with a script task. This works great.
found this with google:
Public Sub Main()
'initialize StreamReader class for text file
Dim streamReader As New StreamReader("c:\Data_Exchange\xml_test.xml")
' Read the StreamReader To End and assign to local variable
Dim StreamText As String = streamReader.ReadToEnd()
' assign SSIS variable with value of StreamText local variable.
Me.Dts.Variables("xml").Value = StreamText
Dts.TaskResult = ScriptResults.Success
End Sub
I will have to test the performance with large XML-files. But for now it seems to work fine.
Glad you solved the issue and thanks for posting the code. Very informative.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply