How to persist data load from initial file dump through subsequent data transformation steps
So I've got a file with about 20MM rows in it. For my initial proof of concept, I've just been using the File Source Import on each data transformation step, which has worked fine, but is certainly going to be a bottleneck in my package. I would like to be able to store this in memory, like the RecordSet or DataReader destinations seem to offer. However, it seems as though RecordSet is only useable with ForEach containers, which is not acceptable according to how I want to use this dataset, and I can't figure out how to read from the DataReader destination at the beginning of a data transformation step. It's been suggested that I store the intial dump of data into a temp table in SQL Server and then read from there each time, but I would like to avoid that if at all possible. So, to recap: * File with 20MM rows * Package with about 6 different data transformation steps, each modifying different columns of the same dataset (unfeasible to merge all of these into one step) before loading to SQL Server. * Want to load the file into memory so that each data transformation step doesn't have to wait to load the data from disk. * Loading into a RecordSet destination/object variable and then using a ForEach container will not work.
April 28th, 2011 12:22pm

SSIS is using memory buffers regardless (internally) so it seems to be a moot point in loading anything else to memory. I also hope you do not use a 32 bit machine to process it. OtherwiseI would just Bulk Inserting the whole file into a database and process it from there - Bulk Insert http://msdn.microsoft.com/en-us/library/ms141239.aspxArthur My Blog
Free Windows Admin Tool Kit Click here and download it now
April 28th, 2011 12:34pm

No, I'm definitely using a 64-bit OS to process.
April 28th, 2011 12:36pm

I'm not sure why having all of your transforms in one Data Flow "isn't feasible"... but storing the recordset in memory is. Anyway - you can use that Recordset Destination generated object, it's just not "easy". But guru Jamie Thomson has the steps for you to get it done. Beware your memory usage... :) Talk to me now on
Free Windows Admin Tool Kit Click here and download it now
April 28th, 2011 12:36pm

Thanks Todd.
April 28th, 2011 1:10pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics