I have a .net webforms front end that allows admin users to upload two .xls files for offline processing. As these files will be used for validation (and aggregation) I store these in an image field in a table.
My ultimate goal is to create a SSIS package that will process these files offline. Does anyone know how to use SSIS to read a blob from a table into it's native (in this case .xls) format for use in a Data Flow task?
I haven't tested that this works - but you might be able to query for the blob, write it out as a raw file, then use it as a source in another Data Flow.
@dmo:
I thought about doing some kind of file system export so that I could import it into SSIS. However, seems like there should some "in memory" way of doing this?
@Ryan Eastabrook:
In my (admittedly limited) experience with SSIS, it is quite good at rapidly getting something up and running, but frusteratingly limited in getting something that "feels" like the most elegant, efficient solution to a programmer.
Since the Excel Source Editor seems to take only files as input, you need to give it a file or reimplement its functionality in code that can take a blob. I understand that this is unsatisfying, but in the end, this is a time saving tool.
Export Column Transformation should do the trick. Check out: MSDN Export Column Transformation.