June 17, 2010 at 2:18 am
Hi,
I have a SSIS package which runs a query and allocates the results to an variable (type string), this is XML, however the query returns it as NVARCHAR(MAX), then the next step is a script to write out the contents to a file like so;
My.Computer.FileSystem.WriteAllText("C:\Sample.xml", Dts.Variables("XMLRecordSet").Value.ToString, False)
However the issue I'm getting is that my file seems to stop writing at around 37mb, about half way through, anyone any suggestions on how I can resolve this?
Thanks,
Nic
June 17, 2010 at 2:36 am
NicHopper (6/17/2010)
However the issue I'm getting is that my file seems to stop writing at around 37mb, about half way through, anyone any suggestions on how I can resolve this?
I bet your server has about 37mb of free memory when your script starts. The problem is that the .net framework, when filling a variable, likes to put the whole thing into memory then use it from there. Unfortunately, when you have a large file that this, you end up only getting part of it.
I would query a set amount of the result at a time, then write the file in bits.
June 17, 2010 at 4:18 am
Hi,
Thanks for the reply, I'm now getting the file to write out correctly.
However the time it takes to run the query and pass this to the string variable is quite long and it got me thinking is there a better way to write to a file, effectively I just need the results of a query (1 record, 1 column) to be written to a text file, however doing this through a data flow task doesn't seem possible when the destination is a flat file, as it only appears to write some of the file out, do you know a better way of doing this?
Thanks,
Nic
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply