September 1, 2009 at 8:57 am
I am not sure if this is the right forum to put this in, but I figured it was the most logical choice. I will try to detail out what I am trying to do while keeping the menusha out of it. So basically, I have some SSIS packages created that will take data from an AS400 and copy to staging tables on my SQL Server. Once this is done, I then push the data over to an Oracle database. It's a pretty simple and straight forward process. What I am trying to figure out is how to be efficient in the data transfer process. Rather than use the SQL Server Agent and run a job at 1:00 a.m. each day, I want to create a "Control" Table that gets populated when the AS400 data has been processed. For example, I have an Item Master table on the AS400 and a process that runs to pull in new items, prices, and so on. When this job runs, I insert a record into my contol table with the following 3 fields: INTEGRATION_NAME, DATE, PROCESSED.
From the SQL Server Side, I basically want the SSIS to run every so often (let's say once every 6 hours) to read the Control table and look for the inserted record. If It finds a record in the Integration Name field with a value of "Item Integration" then I want to run the SSIS package that pulls the data from the AS400 and push to Oracle. When it runs, I want to delete the record from the Control table (I am also thinking of just changing the PROCESSED field to another value).
Conceptually this makes sense to me but where I am struggling is figuring out what tasks to run within SSIS that will read a table, delete and record, and then continue on. I am not a programmer by nature so perhaps that is why I can't "see the code." Any help would be much appreciated. Thanks!!
September 7, 2009 at 3:51 pm
Why is SQL Server part of this picture?
The most efficient way would be for Oracle side to pull data from AS400 in a single step.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 8, 2009 at 7:05 am
Yes, agreed! Option #1 was to pull directly from Oracle but there is more knowledge in house with the SQL Server side over Oracle. The Oracle DB sits on an AIX box and the AS400 is on IBM. One would think that the connection would be a cake walk, but for whatever reason we can't make the systems talk to each other. To avoid any lengthy process, I figured using SSIS would be the easiest way to transfer data.
September 9, 2009 at 6:15 am
I feel your AS400 pain 🙂
I use the OLEDB for DB2 provider to retireive the data off our AS400, runs faster than an ODBC connection.
You'll want to, among other details, set up a data flow task on the control flow pane and then read the table 9OLEDB source task) you need and insert into your staging table (OLEDB destination task) on SQL Server.
Hope this helps at least a little bit...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply