The following question was posed on the SSIS forum recently:
I have a table with 25 Million records. I want to transfer the records from the table to flat files. One flat file can contain only 1 Million record. So I need to generate 25 flat files with 1 Million in each file. Whats the best way to accomplish this task using SSIS.
On the surface this sounds fairly simple but if you look into it in a bit more detail it becomes an interesting little problem. Firstly, I can inteprete the question in one of two ways:
#1 here is quite easy to accomplish so lets deal with that first. We simply have to split the data into the required number of files (which in the question was 25, but I've simpified it to 5). Here's the data-flow that does it. You will notice that I have used Konesans' Data Generator Source Adapter and 5 instances of the Trash Destination Adapterwhich are invaluable tools for scenarios like these. The data generator is used here to synthesize some random string data and the trash destination is used to simulate some flat files into which the data flows.
As you can see from the diagram we use a script. component to generate a surrogate key value (this technique is described here: http://www.sqlis.com/default.aspx?37). We use that value in the conditional spilt transform. along with the modulus (%) operator to split the data in a round-robin technique. Here is the conditional split editor showing this:
public class ScriptMain : UserComponent
int counter = 0;
public override void PreExecute()
public override void PostExecute()
public override void Input0_ProcessInputRow(Input0Buffer Row)
counter += 1;
Row.SK = counter;
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7899089/viewspace-732600/，如需转载，请注明出处，否则将追究法律责任。