SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Data Import


Data Import

Author
Message
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40010 Visits: 38564
Okay, learned something new this morning about SSIS and multiple flat files.

Good question!

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Dan Guzman - Not the MVP
Dan Guzman - Not the MVP
SSC Eights!
SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)

Group: General Forum Members
Points: 914 Visits: 741
Excellent question!
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40010 Visits: 38564
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32833 Visits: 18559
Nice question. My first inclination was to go with the foreach loop container (having done that before quite effectively) - but it was worth verifying what the multiflatfile does since I had seen it but never used it. Good thing - helped me to learn something new.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

FargoUT
FargoUT
Old Hand
Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)

Group: General Forum Members
Points: 361 Visits: 312
I was not aware of the MULTIFLATFILE either -- very informative and useful! I could have used this earlier... :-D
SanjayAttray
SanjayAttray
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4427 Visits: 1619
Never knew until now that there is something like MULTIFLATFILE data connection in SSIS.

Will try next time in multiple data uploads.

Thanks for a good question.

SQL DBA.
OCTom
OCTom
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3139 Visits: 4152
The MULTIFLATFILE answer looked obviously bogus to me (which is why Ipicked it ;-)). I had never heard of it (seems like a lot of others didn't either) and seems like something someone would make up as a joke. but, the joke's on me, and, I have learned something valuable.

Very good question. Cool
mosaic-263591
mosaic-263591
SSC-Addicted
SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)

Group: General Forum Members
Points: 442 Visits: 566
Excellent question - thank you
RBarryYoung
RBarryYoung
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15012 Visits: 9518
I did know about MULTIFLATFILE and I did get the question right, nonetheless I echo what others have said in that I have serious doubts that anything in SSIS is as efficient as BCP when used optimally on a problem like this.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Tom Thomson
Tom Thomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14440 Visits: 12220
What a fun question. One option that looks irrelevant given the topic, one that looks obviously wrong, and one using a feature I'd never heard of. That last had to be the right answer - and I've heard of the feature now so I've learnt something.

Tom

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search