May 26, 2009 at 8:17 am
I am building a DW and need replication from all of our AS400 to SQL 2008.
In the past I have had the luxury of having replication software handle this
for me. But alas, no more.
I know there must be a way in SSIS and/or using the new CDC for 2008
to get my AS400 data into my staging database on SQL 2008.
Essentially, after the initial load from AS400 I would only want insert/update/delete
records for my SSIS package to process into staging database.
Has anyone successfully implemented this? I need a short-term solution so we can
score our win(s) and then get more adequate funding for the project.
Have been all over the web but not quite sure what I am seeing as I have not had
to deal with this in the past. If you can at least point me to some articles I would
very much appreciate!
Thanks!
May 26, 2009 at 8:26 am
I did something somewhat similar several years ago. We just set up a linked server in SQL pointing to the AS/400 databases.
We didn't use SSIS or DTS or anything, we just had a bunch of scripts that dumped the raw data into a staging database, and then another group of scripts that pushed the data into the right "real" databases. We had the luxury or the tables in question being small enough that it didn't take that long, and a fairly large maint. window overnight.
As I recall it took me and the AS/400 DBA a little head-scratching to get the linked server properly working, but after that it worked pretty slick.
SSIS and DTS might buy you a little speed if you work at it because I think they both will let you set things up to run asynchronously which you can't really do with a regular script.
The Redneck DBA
May 26, 2009 at 8:32 am
Not sure why you no longer have the luxury of software or if you will get the luxury back......but just in case ...I worked on a project that used DBMoto to replicate the AS400 data to SQL...it worked very well.
HTH
May 26, 2009 at 8:58 am
Welll...I am trying like the dickens to avoid scripting all of this,
in addition to the problem that not all my AS400 tables have a last
change date and so forth so I could not be assured I would only
retrive the changed data and not reload the whole table.
In my case, we have many many huge transactional tables
so this is why I am hoping for an SSIS-based solution.
I have been advised that one of our AS400 guys can write
LANSA triggers over the files I need to identify the data I need,
and then I simply extract from that table.
But, I wanted to know what other folks had done in this
situation.
Thanks!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply