Import Text File on remote server

Kevin Forbes

Hi there,

When importing a text file using SQL Server, how do I specify the path
to a file on the same remote server that SQL Server is running on? I
tried //c:/filename but that doesn't seem to work.

Also, once the import is working, how do I write a DTS package to
first delete all rows in a table, then repopulate with the text file,
or is it easier to drop the table, re-create it and then repopulate?
The table will contain approximately 30,000 records.

thanks for your help,
K

Jul 20 '05 #1

Dan Guzman

Exactly how are you importing the file? If this is a DTS Bulk Insert task
or T-SQL BULK INSERT statement, you can specify 'c:/filename' to import a
file that is on the same machine as the SQL Server.

In the case of a DTS Transformation, the path is relative to the machine one
which the DTS package executes. The path is the same as above if the DTS
package is run on the SQL Server.

You can truncate the table in your DTS package with an Execute SQL task that
executes TRUNCATE TABLE. Specify the task a workflow predecessor so that it
runs before your import.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Kevin Forbes" <ke*********@yahoo.com> wrote in message
news:25**************************@posting.google.c om...

Hi there,

When importing a text file using SQL Server, how do I specify the path
to a file on the same remote server that SQL Server is running on? I
tried //c:/filename but that doesn't seem to work.

Also, once the import is working, how do I write a DTS package to
first delete all rows in a table, then repopulate with the text file,
or is it easier to drop the table, re-create it and then repopulate?
The table will contain approximately 30,000 records.

thanks for your help,
K

Jul 20 '05 #2

Chuck Conover

Kevin,
There are some great lessons on importing and DTS packages on our site
at www.TechnicalVideos.net. Also covers creating data imports, stored
procedures and temporary tables.

Best regards,
Chuck Conover
www.TechnicalVideos.net

"Kevin Forbes" <ke*********@yahoo.com> wrote in message
news:25**************************@posting.google.c om...

Hi there,

When importing a text file using SQL Server, how do I specify the path
to a file on the same remote server that SQL Server is running on? I
tried //c:/filename but that doesn't seem to work.

Also, once the import is working, how do I write a DTS package to
first delete all rows in a table, then repopulate with the text file,
or is it easier to drop the table, re-create it and then repopulate?
The table will contain approximately 30,000 records.

thanks for your help,
K

Jul 20 '05 #3

John Bell

Hi

As well as looking through Books online the following web site
http://www.sqldts.com/
is well worth a look.

You can use the TRUNCATE TABLE statement see books online:

mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
lref.chm::/ts_ta-tz_2hk5.htm

from a Execute SQL Task

mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\dts
sql.chm::/dts_elemtsk2_3e0b.htm

The input file specification will be local to the server it is being
executed on, therefore c:\filename should be ok.
John

"Kevin Forbes" <ke*********@yahoo.com> wrote in message
news:25**************************@posting.google.c om...

Hi there,

When importing a text file using SQL Server, how do I specify the path
to a file on the same remote server that SQL Server is running on? I
tried //c:/filename but that doesn't seem to work.

Also, once the import is working, how do I write a DTS package to
first delete all rows in a table, then repopulate with the text file,
or is it easier to drop the table, re-create it and then repopulate?
The table will contain approximately 30,000 records.

thanks for your help,
K

Jul 20 '05 #4

Kevin Forbes

Thanks for your help,

I was just using the import data wizard and then saving it as a DTS
package, I don't know much about DTS programming but will give your
suggestions a try.

thank you,
K

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Jul 20 '05 #5