September 12, 2012 at 12:54 pm
I have an ETL process against a table in an Oracle datamart/WH. Due to the size (1.5 million rows x 50 col) of the data set, the Oracle team will only populate the table the first business day of the month and only for as long as it takes for me to run the ETL. After I complete the process, the table is cleared.
I would like to create an SSIS package that does this:
flag = false
If Today = FirstBusinessDayOfMonth Then
While flag = false AND select count(*) from oracle_table where rownum >= 1
execute ETL package
Set flag = true
END IF
I would run this logic every 30 minutes until either 1) the ETL package executes or 2) until midnight.
Thanks in advance for any suggestions....
September 14, 2012 at 11:59 am
labenroth (9/12/2012)
I have an ETL process against a table in an Oracle datamart/WH. Due to the size (1.5 million rows x 50 col) of the data set, the Oracle team will only populate the table the first business day of the month and only for as long as it takes for me to run the ETL. After I complete the process, the table is cleared.I would like to create an SSIS package that does this:
flag = false
If Today = FirstBusinessDayOfMonth Then
While flag = false AND select count(*) from oracle_table where rownum >= 1
execute ETL package
Set flag = true
END IF
I would run this logic every 30 minutes until either 1) the ETL package executes or 2) until midnight.
Thanks in advance for any suggestions....
Two or may be three suggestions...
1- no reason to include rownum >=1 on count(*)
2- be sure Oracle process commits only once, at the end of the process. That way you wll be certain that all 1.5 million rows are there when you get a positive count.
3- instead of counting on base table, I would ask Oracle side to insert a single row in a flag table. No rows on flag table would mean no rows on staging table, one row on flag table would mean your data load is waiting for you.
_____________________________________
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.Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply