June 13, 2012 at 4:36 am
I am using SSIS to load data from my Oracle database into my SQL landing database. I would like to know if there is a way to set a trigger that can say the data is loaded into our Oracle environment and is ready to be push to our SQL environment. The problem is data gets pushed to our oracle environment, and sometimes the data is ready by 6am, 7am, or even 9am or around that time, It always changes. Need a way to say the data is ready to be loaded into our SQL database and to start our ETL packages. Data will be loaded daily. Any help is appreciated. Thanks
June 13, 2012 at 6:25 am
How would you manually tell if the Oracle db has been loaded for that day? Is there a status table or some such thing that gets updated? Is it the presence of records with that day's date in a table? Have your SSIS package check the same way to see if that condition (data loaded) exists.
HTH,
Rob
June 13, 2012 at 6:46 am
they check their logs files to determine when the data is loaded in the oracle database. I already know that the completion times are different from the logs. Are you suggesting like a status table in oracle that has an value, such as "completed", "success", "data loaded" or "failed". Then in SSIS I can have that condition as an indicator to start my package? What control flow item would I use in SSIS? Once again appreciate it. Thanks
June 13, 2012 at 1:27 pm
I don't know if you have a status table, but that's what I'm suggesting you check in your SSIS data flow. Are the log files .txt or .csv? You could use SSIS to check these to see if the load process is complete -- you may have to do some text processing on these though (I have no idea what these look like).
If the Oracle load process has completed for that day and the SQL Server process has not run for that day, then use a conditional split and either proceed with the SQL processing or end. You could schedule a SQL Agent job to run this periodically checking to see if your conditions are met.
Just some ideas,
Rob
June 13, 2012 at 6:55 pm
Yes your suggestion for the SQL Agent Job to run a process periodically to check if conditions are met is a great idea, I will try some testing in SSIS on the conditional split. Thanks for your help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply