SQLServerCentral.com / Article Discussions by Author / Article Discussions / Discuss content posted by Stewart Campbell / Data Import / Latest PostsInstantForum.NET v99.99.99SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 09 Dec 2016 18:10:59 GMT20RE: Data Importhttp://www.sqlservercentral.com/Forums/Topic817609-1684-1.aspxWhat 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.Sat, 20 Mar 2010 17:53:19 GMTTomThomsonRE: Data Importhttp://www.sqlservercentral.com/Forums/Topic817609-1684-1.aspxI 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.Sat, 05 Dec 2009 21:24:32 GMTRBarryYoungRE: Data Importhttp://www.sqlservercentral.com/Forums/Topic817609-1684-1.aspxExcellent question - thank youMon, 16 Nov 2009 06:59:51 GMTmosaic-263591RE: Data Importhttp://www.sqlservercentral.com/Forums/Topic817609-1684-1.aspxThe 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:Fri, 13 Nov 2009 09:09:41 GMTOCTomRE: Data Importhttp://www.sqlservercentral.com/Forums/Topic817609-1684-1.aspxNever 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.Fri, 13 Nov 2009 08:31:33 GMTSanjayAttrayRE: Data Importhttp://www.sqlservercentral.com/Forums/Topic817609-1684-1.aspxI was not aware of the MULTIFLATFILE either -- very informative and useful! I could have used this earlier... :-DThu, 12 Nov 2009 12:59:25 GMTFargoUTRE: Data Importhttp://www.sqlservercentral.com/Forums/Topic817609-1684-1.aspxNice 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.Thu, 12 Nov 2009 12:54:36 GMTSQLRNNRRE: Data Importhttp://www.sqlservercentral.com/Forums/Topic817609-1684-1.aspx[quote][b]Mark Harr (11/12/2009)[/b][hr]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 [b]NEVER[/b] the most [i]"efficient"[/i] 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.[/quote]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.Thu, 12 Nov 2009 09:54:49 GMTLynn PettisRE: Data Importhttp://www.sqlservercentral.com/Forums/Topic817609-1684-1.aspxExcellent question!Thu, 12 Nov 2009 09:29:22 GMTDan Guzman - Not the MVPRE: Data Importhttp://www.sqlservercentral.com/Forums/Topic817609-1684-1.aspxOkay, learned something new this morning about SSIS and multiple flat files.Good question!Thu, 12 Nov 2009 08:56:22 GMTLynn PettisRE: Data Importhttp://www.sqlservercentral.com/Forums/Topic817609-1684-1.aspxWOW i always used the for each loop to process files and never knew about multiflatfile feature.Good one.Thank youThu, 12 Nov 2009 08:42:22 GMTAlexSQLForumsRE: Data Importhttp://www.sqlservercentral.com/Forums/Topic817609-1684-1.aspxI agree, nice question. I learned something new today and that is the point.Thu, 12 Nov 2009 08:31:49 GMTCliff JonesRE: Data Importhttp://www.sqlservercentral.com/Forums/Topic817609-1684-1.aspxNice question! I didn't know a connections such as MULTIFLATFILE existed. I typically did what was suggested in the second answer. Thanks!Thu, 12 Nov 2009 06:29:16 GMTdgabeleRE: Data Importhttp://www.sqlservercentral.com/Forums/Topic817609-1684-1.aspxI 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 [b]NEVER[/b] the most [i]"efficient"[/i] 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.Thu, 12 Nov 2009 06:15:56 GMTMark HarrRE: Data Importhttp://www.sqlservercentral.com/Forums/Topic817609-1684-1.aspxExcellent question, stewartc, thank you!I wish you had set this question a long time ago, this could have saved some people some work... How did this slip under my radar? :crazy:Thu, 12 Nov 2009 06:15:39 GMTRob GoddardRE: Data Importhttp://www.sqlservercentral.com/Forums/Topic817609-1684-1.aspxAgreed. The main point of the question is about how to import a number of files.Thu, 12 Nov 2009 06:14:00 GMTEdVassieRE: Data Importhttp://www.sqlservercentral.com/Forums/Topic817609-1684-1.aspxI too have well over 10 years experience on mainframe, mainly cobol and db2 on z/os. limitations could be put in place for numerous reasons, including, inter alia, the transport mechanism used to move the files (e.g. x'com), network restrictions and, as stated, user (lack of) knowledge. however, the mention of "jcl limitations" is just incidental to the actual question, i.e. the most effective and efficient way to import the data and populate an SSAS cube.Thu, 12 Nov 2009 06:10:21 GMTStewart "Arturius" CampbellRE: Data Importhttp://www.sqlservercentral.com/Forums/Topic817609-1684-1.aspx:-P:w00t: good qThu, 12 Nov 2009 05:33:41 GMTinus.dupreezRE: Data Importhttp://www.sqlservercentral.com/Forums/Topic817609-1684-1.aspxI would be interested in knowing what the so-called JCL limitation is. With over 20 years mainframe experience it would be a new one for me (unless it is a limitation in the user's knowledge of JCL, I have seen that one before...).Thu, 12 Nov 2009 05:07:25 GMTEdVassieData Importhttp://www.sqlservercentral.com/Forums/Topic817609-1684-1.aspxComments posted to this topic are about the item [B]<A HREF="/questions/SSIS/68024/">Data Import</A>[/B]Wed, 11 Nov 2009 22:37:10 GMTStewart "Arturius" Campbell