Francisco H Tapia
my.lists at verizon.net
Tue Feb 4 14:53:01 CST 2003
Ron, XLS and txt files can be imported into DTS scripts. what's more important is that you can use the DTS package to modify and make all changes needed to your data, be it with temp tables or variables in VBscripts.. the end result is that the extra time you invest into a DTS package the added speed you add to the import process, While the Access97 db that I am importing requirese normalization, there is a lot of sensitive data that must match when moving from Integer PK's to Guids. Thus it is imperative that the DTS package work appropriatly, initially I began writting everything with cursors and found one of my processes took over 5 hrs to import, later I moved it to a DTS package and temp tables and now it takes less than a minute to run...If you have any questions on scripting an XLS or TXT file for that matter feel free to post the dba-sqlserver at databaseadvisor.com list. -Francisco http://rcm.netfirms.com ----- Original Message ----- From: "Ron Moore" <rmoore at comtechpst.com> To: <accessd at databaseadvisors.com> Sent: Tuesday, February 04, 2003 11:50 AM Subject: RE: [AccessD] Pushing A2K Data to SQL2K - S-L-O-W!: : Francisco, I do not have a 'good' ODBC' connection to our Informix DB. And : even if I did, there is MUCH, MUCH more required than just normalizing the : data. The schema we have requires normalizing, scrubbing the data, : 'capturing' business rules, etc. Unfortunately, I'm stuck with the txt/xls : files as my source. : : No I'm not using DTS packages. I'm new to SQL, so I fell back on that which : I knew. : : Thanks, : Ron : : -----Original Message----- : From: accessd-admin at databaseadvisors.com : [mailto:accessd-admin at databaseadvisors.com]On Behalf Of Francisco H : Tapia : Sent: Tuesday, February 04, 2003 2:33 PM : To: accessd at databaseadvisors.com : Subject: Re: [AccessD] Pushing A2K Data to SQL2K - S-L-O-W!: : : : I agree with Martin, If you are using an ODBC connection from Access to : Informix, then you ought to be able to connect directly from SQL, one thing : you didn't mention is if you are using a DTS package in order to transport : your data from Access to SQL, my current process does not have so many : records but the source DB is 250megs, and I normalize the data on the fly it : takes less than 30minutes... : : -Francisco : http://rcm.netfirms.com : ----- Original Message ----- : From: <Mwp.Reid at Queens-Belfast.AC.UK> : To: <accessd at databaseadvisors.com> : Sent: Tuesday, February 04, 2003 11:18 AM : Subject: Re: [AccessD] Pushing A2K Data to SQL2K - S-L-O-W!: : : : : : : Ron : : : : excuse my ignorance of Informix but why do you need Access to sit in the : : middle? : : : : Martin : : : : : : : : : : Quoting Ron Moore <rmoore at comtechpst.com>: : : : : > *cross-posted to SQL & Access Forums* : : > : : > I'm migrating/converting data from an Informix DB to a SQLServer2K app : : > using : : > A2K as the middleware. The final append query selects records from a : : > buffer : : > table and inserts them into a SQL table through an ODBC connection. : : > The : : > push of the largest table takes almost 5 HOURS! :-(((( The Windows : : > Task : : > Manager shows A2K and the specific DB 'toggling' between 'Running' and : : > 'Not : : > Responding', but the process does finish. : : > : : > 107,850 Records in A2K table. 72 Fields. DB size (compressed before : : > push) : : > is 86Mb (was 346Mb). : : > : : > Do I have to live with this lack of speed? This is for a pilot which : : > will : : > be built from 'scratch' MANY times before it goes to production. : : > : : > TIA, : : > Ron Moore : : > Sr. Database Administrator : : > Comtech PST Corp. : : > Melville, NY : : > www.comptechpst.com : : > : : > _______________________________________________ : : > AccessD mailing list : : > AccessD at databaseadvisors.com : : > http://databaseadvisors.com/mailman/listinfo/accessd : : > Website: http://www.databaseadvisors.com : : > : : _______________________________________________ : : AccessD mailing list : : AccessD at databaseadvisors.com : : http://databaseadvisors.com/mailman/listinfo/accessd : : Website: http://www.databaseadvisors.com : : : : : _______________________________________________ : AccessD mailing list : AccessD at databaseadvisors.com : http://databaseadvisors.com/mailman/listinfo/accessd : Website: http://www.databaseadvisors.com : : _______________________________________________ : AccessD mailing list : AccessD at databaseadvisors.com : http://databaseadvisors.com/mailman/listinfo/accessd : Website: http://www.databaseadvisors.com :