"Brad Allison" <ballison@ukcdogs.com> wrote in
news:uZX9bwbbEHA.1644@tk2msftngp13.phx.gbl:
[color=blue]
> Lucas,
>
> Thank you for the valuable information.
>
> Being very new to this, I thought I had to have SQL Server to use DTS.
> Is DTS something else altogether separate as DTS has always been
> recommended to me.[/color]
DTS is SQL Server's Data Transformation Services. It allows a database
Administrator to write a load script in graphical format. Check out this
website for more information:
http://www.sqldts.com/
DTS vs. Bulk Load + .NET code - DTS is easier to administer for a non-
programmer since it provides a graphical interface. But, DTS can become
hard to maintain if the script is complex, and DTS has a couple of
quirks I don't like as a programmer. Bulk load is great for straight
inserts - i.e. a table copy. You can also create a format file for SQL
bulk loads to handle custom formatting (to an extent). Additional
formatting for bulk loads will have to be done by your extract tool.
[color=blue]
> If I were to use Bulk loading, what would be involved in that? Just
> using a datareader to extract the data, save to text and then use this
> text to enter this information into Access? Or is Bulk loading using
> some routine that I might not be aware of?[/color]
Here is the documentation for SQL Server's Bulk Insert Command:
http://msdn.microsoft.com/library/de...l=/library/en-
us/tsqlref/ts_ba-bz_4fec.asp
SQL Server also has a command called BCP:
http://msdn.microsoft.com/library/de...l=/library/en-
us/coprompt/cp_bcp_61et.asp
[color=blue]
> I am going to look into MySQL as that may be the answer. Our business
> is built on data. The AS400 stores around 10 GB of various tables of
> data. Eventually what we need to do is to have a system that
> replicates the AS400 data and allow that data to be accessible to the
> web and some various utilities in-house. Obviously we do not want to
> put the data from the AS400 directly on the web.[/color]
MySQL also has the ability to bulk load a text file:
http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html
MySQL is great - reliablity is great, speed is great, and the cost is
great too. As I mentioned in my previous post, I highly recommend mySQL
Manager from EMS Hi-Tech if you're looking for a MySQL Administration
Tool (like SQL Server's Enterprise Manager).
I think a bulk load or a DTS package would be the easiest solution for
you. Ask your AS/400 administrator to provide you with a daily extract
in the proper bulk load format (i.e. CSV) . Run the bulk load/DTS
package once per day to sync your web database with your AS/400 system.
Handling record changes maybe a bit more difficult - you may have to
delete the records from the SQL Database, and reinsert the update
records from the AS/400. Alternatively, you might want to create a
syncronization tool to handle updates.
I'm sure there are packages for syncing SQL server with AS/400... but I
haven't looked into that route as the packages are probably pricy.
--
Lucas Tam (REMOVEnntp@rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/ 
