Mark Harr (11/12/2009)
I second Ed Vassie's question on the supposed JCL limitation.I got the answer right, only because the category was SSIS and I figured that one of those two answers must be the one being driven for. However, SSIS is NEVER the most "efficient" solution. I'm sure there is a much more efficient solution using BCP or Bulk Insert (not with a cursor, that was an obvious bad answer), perhaps using CLR stored procedure, or perhaps concatenating the files before the BCP operation.
I wouldn't worry about JCL limitation, that isn't the point of the question. I could give you another setup that may make more sense. At a previous employer we extracted inventory data nightly from ISAM databases for import into SQL Server tables. The master table was exported into a single file. The child table was exported into multiple files that were concatenated before being ftp'ed to our SQL Server for import. The reason, it would take over 24 hours to export the child inventory database as a single file. Breaking it down into multiple files (20,000 master records each) running it in parallel allowed the export to be completed in one to two hours.
Using the Multiple Flat File Connection Manager would actually allow for the elimination of the concatenation process.