"Brad Allison" <ba******@ukcdogs.com> wrote in
news:uZ**************@tk2msftngp13.phx.gbl:
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.
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.
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?
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
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.
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 (RE********@rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/