What is the best way to set up a DTS routine to input the textfile with over 200,000 records into a database table once every month. Currently everything is in Access and the textfile is read sequentially and inserted into Access using a webpage, but now I want to move it to SQL Server. What is the best way?

Thanks!

Assuming you are just doing a strait transfer, then create two connections,
one to your access database and the other to SQL Server. Then add a DataPump
task to move the data. After creating the package, you can manually run the
package as needed or schedule it via SQL Agent to automatically run at
whatever interval you need.

--
--Brian
(Please reply to the newsgroups only.)


"Shawn Ferguson" <SFergus2 (AT) cscc (DOT) edu> wrote

What is the best way to set up a DTS routine to input the textfile with over
200,000 records into a database table once every month. Currently
everything is in Access and the textfile is read sequentially and inserted
into Access using a webpage, but now I want to move it to SQL Server. What
is the best way?

Thanks!

I want to eliminate the Access file and use only the Text file and SQL Server. Right now they use the textfile to Access. Acutally the source comes from an old version of Paradox to text file to Access.

Thanks.


Assuming you are just doing a strait transfer, then create two connections,
one to your access database and the other to SQL Server. Then add a DataPump
task to move the data. After creating the package, you can manually run the
package as needed or schedule it via SQL Agent to automatically run at
whatever interval you need.

--
--Brian
(Please reply to the newsgroups only.)


"Shawn Ferguson" <SFergus2 (AT) cscc (DOT) edu> wrote

What is the best way to set up a DTS routine to input the textfile with over
200,000 records into a database table once every month. Currently
everything is in Access and the textfile is read sequentially and inserted
into Access using a webpage, but now I want to move it to SQL Server. What
is the best way?

Thanks!

In that case, create a Text File (Source) connection instead of the Access
connection. The rest remains the same. If you wanted, you could also
connect directly to the Paradox files using the Paradox connection but
again, other than changing the connection type, the process of using the
DataPump Task remains the same.

As an alternative, if the data file resides on the SQL Server box, then you
could also use the BulkInsert Task instead of the DataPump Task to move the
data. BulkInsert is a bit faster but has the limitation of where the data
can reside. Books-online can provide more details on the differences
between the tasks.

--
--Brian
(Please reply to the newsgroups only.)


"Shawn Ferguson" <SFergus2 (AT) cscc (DOT) edu> wrote

I want to eliminate the Access file and use only the Text file and SQL
Server. Right now they use the textfile to Access. Acutally the source
comes from an old version of Paradox to text file to Access.

Thanks.


Assuming you are just doing a strait transfer, then create two connections,
one to your access database and the other to SQL Server. Then add a DataPump
task to move the data. After creating the package, you can manually run the
package as needed or schedule it via SQL Agent to automatically run at
whatever interval you need.

--
--Brian
(Please reply to the newsgroups only.)


"Shawn Ferguson" <SFergus2 (AT) cscc (DOT) edu> wrote

What is the best way to set up a DTS routine to input the textfile with over
200,000 records into a database table once every month. Currently
everything is in Access and the textfile is read sequentially and inserted
into Access using a webpage, but now I want to move it to SQL Server. What
is the best way?

Thanks!