best approach in loading data from SMART/AS400 dbs to SQL 2000
-
Hi guys!
I need some sound advice on the best architecture/approach in loading data from as400 database to SQL 2k. My company is currently using a third party application (SMART) residing on AS400. The goal of my project is to establish a mirror database in SQL Server. All updates will be loaded to my SQL database on a daily basis.
Approach 1
Create logical files containing latest updates.
Create DTS to transform data to SQL.
Develop middleware program to run DTS.
-- by the way, can i do away with creation of logical files and access directly to physical files since structure of as400 is transparent to us?
Approach 2
Establish link server to AS400
Create DTS to transform data from linked server to SQL db
Develop middleware program to run DTS.
-- would you recommend to run the DTS via scheduled job instead of creating a middleware program?
Approach 3
Develop middleware program to transform data from AS400 to SQL.
--this was suggested to me by a programmer, dump table in a disconnected dataset and do manipulation.
Any thoughts on this?
Kitts DC
Manila, Philippines
-
Test the different methods of obtaining data in Approach 1 & 2 and go with the one that provides the best speed. This will depend on the physical location of each server and the connectivity between them.
From there I'd suggest using DTS to load the data into staging tables and the transform/load data as needed using stored procedures. I would avoid developing a middleware app to use anywhere in the process, everything you need is already provided
Our main production database is on Progress that resides on a Unix server. We perform nightly copies of the data to our SQL Server database using an ODBC connection in DTS. On a nightly basis we bring across approx. 4GB of data to a staging database and then perform delete/update/inserts against our production SQL Server database. The whole process takes approx 1 1/2 hrs to complete. The bottleneck of the operation is we're limited to a single ODBC connection so we can't take advantage of loading data in parallel.
If you decide to go the DTS approach take into account if/when you decide to upgrade to SQL Server 2005. The DTS replacement, SQL Server Integration Services (SSIS), is a much changed beast and if the DTS packages become overly complex you could be faced with discarding/re-writing them during the upgrade.
--------------------
Colt 45 - the original point and click interface
-
Hi Phill,
Hmm.. your take on SSIS is something new to me, and I'll ask if we have any plans to migrate to sql 2005 soon.
I appreciate your advice and will experiment on approach 1 and 2. It's nice to know someone in a similar situation. Now I can move on to the details.
Many thanks!
Kitts DC
Manila, Philippines
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply