Is it possible to read Flat File data from a Variable?

Hi,

I'm relatively new to SSIS and I've been tinkering with the XML and Flat File sources.

I noticed that in the XML source it is possible to tell SSIS to read the XML data from a variable. I didn't see a similar option for the Flat File source.

Does anyone know if it is possible to read flat file data from a variable when using the Flat File source?

Thanks,

-dhideal

Simple answer is no. I'd question why you want to do this though as any volume of data in a variable will not perform well.

A Script component could act as a source though and output rows and columns. A Script Task could also be used to write the variable data to a file, and then just use the normal source.

|||

Darren,

Thanks for the quick response.

I realize that performance wont be the best but I was wondering more from the point of view of taking XML data that has been transformed using XSL and feeding that straight into a table.

I realize that I was going down the wrong path as I should not have the XSL convert to a flat file format but keep the data in XML format and then use the XML Source's read from variable capability.

Keeping the data in a variable is going to eat lots of RAM but hopefully avoiding the write/read to disk will give a significant enough performance gain to justify it.

Thanks,

-dhideal

|||

The disk cost may be less than the cost or marshalling data in memory.

I have seen similar situations where it has been cheaper to stage data through files, because that way you can use the pipeline which ideally only works on a buffer full of data at one time (not quite true, but the point is there). Therefore you don't have the pain or finding a lot of memory, you just work in piecmeal sections.

In summary the cost of writing and reading to and from a file can be far outweighed by the overhead of finding a lot of memory in one go. This does of course depend on hardware and data volumes, but you may want to try both methods.

|||

Its also worth saying that raw files can be screamingly fast. I've just constructed a quick and dirty demo where a raw file containing 10000000 records of width 270 bytes (nearly 3GB of data) was read from disk and pushed into a rowcount component in 68seconds. That's fast. When I pushed the same data into another raw file (i.e. writing to disk) it took 175seconds. Again very impressive, all the more so considering the files were on the same physical disk.

Definately worth a look.

-Jamie

|||

Jamie/Darren,

Thanks for the advice. I will definitely try out both options but I believe staying in memory might be better for me because the XML files I'm working with aren't huge but there are just a lot of them.

-dhideal