 | |
Is this the best solution, what is?
microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Is this the best solution, what is? -
01-22-2004
, 10:38 PM
#2
| |||
| |||
|
|
OK here's the situation: A file is created by Paradox and exported into a text (.txt) file. The text file get's uploaded to a remote server on the Internet. The text file is then FTP'd to my local computer. From the text file (50-100MB) of raw data which is separated by a pipe (|) with each text field being in double quotes and integers without quotes ex: "name"|"address"|2004), I create two tables from the file, first student table, then transcript table. First I back up the previous tables, then I truncate the transcript table and re-create the student table using select into ... (transcript table contains multiple student records, student table only has one record of the student). Then once the tables are created, the users use a web interface to query the tables to get their transcript information. Questions??? Should I be truncating the transcript table and re-creating the student table? I figure so because all the data from the text file is subject to change. Should I index the tables first then import or import the data then index them? I tried to use the FTP task, which worked for a little while, then stop working for no apparent reason so I have to manually FTP the file down. Is there a script I can write to FTP instead of using the task, which method is better? Any opinions, comments, and ways to simply would be appreciated. |
#3
| |||
| |||
|
|
-----Original Message----- What a lot of Qs. Q. Should I be truncating the transcript table and re- creating the student table? A. Is the data fluid? i.e. are people only interested in the new transcripts etc? What about these backups of tables? Does this go on ad infinitum? If they are only interested in the new stuff then maybe you could take the older rows out to an Archiving table and then you can provided a facility to look thrugh that if required. Q. Should I index the tables first then import or import the data then index them? A. for 100MB of data this really insn't going to be that much of an issue so long as you haven't overdone indexing. A table with 2-3 indices should be OK. Q. Is there a script I can write to FTP instead of using the task, which method is better? A. You can use a bacth fie and call it in the ExecuteProcess task. The DTSFTP task is down only and cannot do some sexy stuff that you can do with FTP.exe. It is in this release a baby. -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "James Johnson" <anonymous (AT) discussions (DOT) microsoft.com wrote in message news:254001c3e16a$b4d9c720$a301280a (AT) phx (DOT) gbl... OK here's the situation: A file is created by Paradox and exported into a text (.txt) file. The text file get's uploaded to a remote server on the Internet. The text file is then FTP'd to my local computer. From the text file (50-100MB) of raw data which is separated by a pipe (|) with each text field being in double quotes and integers without quotes ex: "name"|"address"|2004), I create two tables from the file, first student table, then transcript table. First I back up the previous tables, then I truncate the transcript table and re-create the student table using select into ... (transcript table contains multiple student records, student table only has one record of the student). Then once the tables are created, the users use a web interface to query the tables to get their transcript information. Questions??? Should I be truncating the transcript table and re-creating the student table? I figure so because all the data from the text file is subject to change. Should I index the tables first then import or import the data then index them? I tried to use the FTP task, which worked for a little while, then stop working for no apparent reason so I have to manually FTP the file down. Is there a script I can write to FTP instead of using the task, which method is better? Any opinions, comments, and ways to simply would be appreciated. . |
#4
| |||
| |||
|
|
Thank you, I didn't quite understand this part, "The DTSFTP task is down only and cannot do some sexy stuff that you can do with FTP.exe. It is in this release a baby." For some reason the DTS task work work anymore, your saying that I can use FTP.exe to do more? Where can I get more information about writing such a script? Thanks again! -----Original Message----- What a lot of Qs. Q. Should I be truncating the transcript table and re- creating the student table? A. Is the data fluid? i.e. are people only interested in the new transcripts etc? What about these backups of tables? Does this go on ad infinitum? If they are only interested in the new stuff then maybe you could take the older rows out to an Archiving table and then you can provided a facility to look thrugh that if required. Q. Should I index the tables first then import or import the data then index them? A. for 100MB of data this really insn't going to be that much of an issue so long as you haven't overdone indexing. A table with 2-3 indices should be OK. Q. Is there a script I can write to FTP instead of using the task, which method is better? A. You can use a bacth fie and call it in the ExecuteProcess task. The DTSFTP task is down only and cannot do some sexy stuff that you can do with FTP.exe. It is in this release a baby. -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "James Johnson" <anonymous (AT) discussions (DOT) microsoft.com wrote in message news:254001c3e16a$b4d9c720$a301280a (AT) phx (DOT) gbl... OK here's the situation: A file is created by Paradox and exported into a text (.txt) file. The text file get's uploaded to a remote server on the Internet. The text file is then FTP'd to my local computer. From the text file (50-100MB) of raw data which is separated by a pipe (|) with each text field being in double quotes and integers without quotes ex: "name"|"address"|2004), I create two tables from the file, first student table, then transcript table. First I back up the previous tables, then I truncate the transcript table and re-create the student table using select into ... (transcript table contains multiple student records, student table only has one record of the student). Then once the tables are created, the users use a web interface to query the tables to get their transcript information. Questions??? Should I be truncating the transcript table and re-creating the student table? I figure so because all the data from the text file is subject to change. Should I index the tables first then import or import the data then index them? I tried to use the FTP task, which worked for a little while, then stop working for no apparent reason so I have to manually FTP the file down. Is there a script I can write to FTP instead of using the task, which method is better? Any opinions, comments, and ways to simply would be appreciated. . |
#5
| |||
| |||
|
|
-----Original Message----- The FTP task in 2000 is very limited in functionality. You can google for tutorials on using FTP.exe There is also this tool which can be scheduled http://www.ncftpd.com/ -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "James Johnson" <anonymous (AT) discussions (DOT) microsoft.com wrote in message news:2af401c3e17a$5a03e410$a001280a (AT) phx (DOT) gbl... Thank you, I didn't quite understand this part, "The DTSFTP task is down only and cannot do some sexy stuff that you can do with FTP.exe. It is in this release a baby." For some reason the DTS task work work anymore, your saying that I can use FTP.exe to do more? Where can I get more information about writing such a script? Thanks again! -----Original Message----- What a lot of Qs. Q. Should I be truncating the transcript table and re- creating the student table? A. Is the data fluid? i.e. are people only interested in the new transcripts etc? What about these backups of tables? Does this go on ad infinitum? If they are only interested in the new stuff then maybe you could take the older rows out to an Archiving table and then you can provided a facility to look thrugh that if required. Q. Should I index the tables first then import or import the data then index them? A. for 100MB of data this really insn't going to be that much of an issue so long as you haven't overdone indexing. A table with 2-3 indices should be OK. Q. Is there a script I can write to FTP instead of using the task, which method is better? A. You can use a bacth fie and call it in the ExecuteProcess task. The DTSFTP task is down only and cannot do some sexy stuff that you can do with FTP.exe. It is in this release a baby. -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "James Johnson" <anonymous (AT) discussions (DOT) microsoft.com wrote in message news:254001c3e16a$b4d9c720$a301280a (AT) phx (DOT) gbl... OK here's the situation: A file is created by Paradox and exported into a text (.txt) file. The text file get's uploaded to a remote server on the Internet. The text file is then FTP'd to my local computer. From the text file (50-100MB) of raw data which is separated by a pipe (|) with each text field being in double quotes and integers without quotes ex: "name"|"address"|2004), I create two tables from the file, first student table, then transcript table. First I back up the previous tables, then I truncate the transcript table and re-create the student table using select into ... (transcript table contains multiple student records, student table only has one record of the student). Then once the tables are created, the users use a web interface to query the tables to get their transcript information. Questions??? Should I be truncating the transcript table and re-creating the student table? I figure so because all the data from the text file is subject to change. Should I index the tables first then import or import the data then index them? I tried to use the FTP task, which worked for a little while, then stop working for no apparent reason so I have to manually FTP the file down. Is there a script I can write to FTP instead of using the task, which method is better? Any opinions, comments, and ways to simply would be appreciated. . . |
#6
| |||
| |||
|
|
WOW, I looked into using FTP.exe and it works wonderfully. It is alot faster than using WS_ftp. Have you ever ran into any problems using FTP.exe? -----Original Message----- The FTP task in 2000 is very limited in functionality. You can google for tutorials on using FTP.exe There is also this tool which can be scheduled http://www.ncftpd.com/ -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "James Johnson" <anonymous (AT) discussions (DOT) microsoft.com wrote in message news:2af401c3e17a$5a03e410$a001280a (AT) phx (DOT) gbl... Thank you, I didn't quite understand this part, "The DTSFTP task is down only and cannot do some sexy stuff that you can do with FTP.exe. It is in this release a baby." For some reason the DTS task work work anymore, your saying that I can use FTP.exe to do more? Where can I get more information about writing such a script? Thanks again! -----Original Message----- What a lot of Qs. Q. Should I be truncating the transcript table and re- creating the student table? A. Is the data fluid? i.e. are people only interested in the new transcripts etc? What about these backups of tables? Does this go on ad infinitum? If they are only interested in the new stuff then maybe you could take the older rows out to an Archiving table and then you can provided a facility to look thrugh that if required. Q. Should I index the tables first then import or import the data then index them? A. for 100MB of data this really insn't going to be that much of an issue so long as you haven't overdone indexing. A table with 2-3 indices should be OK. Q. Is there a script I can write to FTP instead of using the task, which method is better? A. You can use a bacth fie and call it in the ExecuteProcess task. The DTSFTP task is down only and cannot do some sexy stuff that you can do with FTP.exe. It is in this release a baby. -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "James Johnson" <anonymous (AT) discussions (DOT) microsoft.com wrote in message news:254001c3e16a$b4d9c720$a301280a (AT) phx (DOT) gbl... OK here's the situation: A file is created by Paradox and exported into a text (.txt) file. The text file get's uploaded to a remote server on the Internet. The text file is then FTP'd to my local computer. From the text file (50-100MB) of raw data which is separated by a pipe (|) with each text field being in double quotes and integers without quotes ex: "name"|"address"|2004), I create two tables from the file, first student table, then transcript table. First I back up the previous tables, then I truncate the transcript table and re-create the student table using select into ... (transcript table contains multiple student records, student table only has one record of the student). Then once the tables are created, the users use a web interface to query the tables to get their transcript information. Questions??? Should I be truncating the transcript table and re-creating the student table? I figure so because all the data from the text file is subject to change. Should I index the tables first then import or import the data then index them? I tried to use the FTP task, which worked for a little while, then stop working for no apparent reason so I have to manually FTP the file down. Is there a script I can write to FTP instead of using the task, which method is better? Any opinions, comments, and ways to simply would be appreciated. . . |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.