James Barash
James at fcidms.com
Thu Aug 10 10:00:35 CDT 2006
John, Here is an exaple of a dts package that loops through all the files in a directory and imports them by dynamically changing the source object. It's a bit hard to understand at first but I've used it as a starting point for doing something very similar to what you're doing now. http://www.sqldts.com/default.aspx?246 It's written for SQL Server 2000. If you are using 2005, it is much easier to create an SSIS package that does the same thing since looping is written into the dtsx object set. Hope this helps James Barash -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of JWColby Sent: Thursday, August 10, 2006 10:06 AM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] SQL Server hanging It doesn't appear that it is possible to do this via the wizard, or at least I can't see how. However under data transformation / local packages / right click / New package you can get at the designer which does allow all of this stuff. I have been doing more than one transform (several different files) from the simple wizard. Under normal circumstances, the second and subsequent transforms wait for the first to finish. However, and I have not determined how or why, sometimes the second or third or fourth transform that I define will start executing as well. Sometimes all of them at once!!! When that happens of course, things slow down a LOT. I could never tell if the slow down was linear or not. Unfortunately, I also started getting "unable to obtain lock" failures when this happens. Of course the error messages don't make clear whether the locks are database licks or file system locks. I suspect that it is db (row) locks. I thought perhaps the designer would give enough more control that it would significantly speed up the imports. I set the properties to use a table lock which it seems would speed things up. I also set it up to read 1000 records (lines, or at least so I think) into the source buffer from the source text file instead of one line at a time, thinking that this too might speed things up. I did however ask it to commit after each 1000 rows which is supposed to slow it down a bit. All in all I cannot see any visible speedup, though it doesn't appear to be any slower either. I must say that the process of switching from file to file using the same transform is a bit cumbersome. You have a source object and a destination object, and then a transform object. The transform defines all these properties such as buffer size, table / row locks, commit after N rows etc. What makes no sense to me however is that it ALSO defines the source, i.e. the NAME of the table is also embedded in the transform object. This just makes no sense to me since the transform operates on a source (thus already defined) and a destination. The upshot is that in order to move from one text file to the next using the same setup (transform) I have to open the source object and change the file name there, then open the transform object and change the source file there. No big deal except that doing the change in the transform object then prompts for something like "specify which transform to delete", very confusing. The upshot of all this is that I do now have more control than using the simple wizard, however it is more of a PITA. This may be mitigated by being able to simply edit the transform package in two places (the source object table and the transform object source property) instead of running through the entire wizard again. I may also be able to get around the locking issue by going back to the row level locking, but commit after every 1000 rows. This might allow me to queue up 4 or 5 of these and let them run so I am not baby sitting this thing all day. John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Martin Reid Sent: Thursday, August 10, 2006 9:12 AM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] SQL Server hanging John Look up Insert Commit Size re DTS Soorry my server is down at the moment so cant check it for you. Martin Martin WP Reid Training and Assessment Unit Riddle Hall Belfast tel: 02890 974477 ________________________________ From: dba-sqlserver-bounces at databaseadvisors.com on behalf of JWColby Sent: Thu 10/08/2006 13:42 To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] SQL Server hanging >using DTS, you can adjust the "commit after # rows" level. Is this possible using the DTS wizard? I am not able to find this property anywhere and of course the help doesn't. John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of artful at rogers.com Sent: Wednesday, August 09, 2006 5:19 PM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] SQL Server hanging Assuming that you are importing the data using DTS, you can adjust the "commit after # rows" level. Cut it back to say 100 or 1000 rows and then see what is happening. ----- Original Message ---- From: Martin Reid <mwp.reid at qub.ac.uk> To: dba-sqlserver at databaseadvisors.com Sent: Wednesday, August 9, 2006 4:04:10 PM Subject: Re: [dba-SQLServer] SQL Server hanging John Try and run a trace on the server and database your importing to. Open SQL Proflier -> File -> New->Trace See what that shows you. Martin Martin WP Reid Training and Assessment Unit Riddle Hall Belfast tel: 02890 974477 _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com <http://www.databaseadvisors.com/> _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com <http://www.databaseadvisors.com/> _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com <http://www.databaseadvisors.com/> _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com