Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Data Import Expand / Collapse
Author
Message
Posted Thursday, November 12, 2009 8:56 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:42 AM
Points: 20,799, Visits: 32,717
Okay, learned something new this morning about SSIS and multiple flat files.

Good question!



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)
Post #817909
Posted Thursday, November 12, 2009 9:29 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 9:11 AM
Points: 719, Visits: 695
Excellent question!
Post #817930
Posted Thursday, November 12, 2009 9:54 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:42 AM
Points: 20,799, Visits: 32,717
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.



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)
Post #817949
Posted Thursday, November 12, 2009 12:54 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 11:48 AM
Points: 17,948, Visits: 15,944
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
Post #818068
Posted Thursday, November 12, 2009 12:59 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 11, 2011 1:51 PM
Points: 341, Visits: 311
I was not aware of the MULTIFLATFILE either -- very informative and useful! I could have used this earlier...
Post #818070
Posted Friday, November 13, 2009 8:31 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
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.
Post #818550
Posted Friday, November 13, 2009 9:09 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:14 PM
Points: 2,627, Visits: 4,026
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.
Post #818607
Posted Monday, November 16, 2009 6:59 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, July 17, 2013 3:18 AM
Points: 419, Visits: 559
Excellent question - thank you
Post #819356
Posted Saturday, December 5, 2009 9:24 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 1:19 PM
Points: 9,294, Visits: 9,491
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."
Post #829495
Posted Saturday, March 20, 2010 5:53 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:42 AM
Points: 7,851, Visits: 9,602
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
Post #887000
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse