| Author |
Topic |
|
pinky2012
Starting Member
5 Posts |
 Posted - 2012-02-28 : 12:05:31
|
| Hi,We have a SSIS package that is set up to load Data into our warehouse.This Package loads data to the ware house and processes the cubes once the dataware house load is complete.However there have been recent scenarios where empty cubes has been processesed due to some issues in the data load.However the package does not throw any error.It just processess empty cubes and some of the fact and Dim tables are empty.Can you provide a solution for this for example during the job execution before processing the cubes do Table count checks to determine if data build is successful or not.If there are any empty tables then send an error message and do not go ahead with the cube processing.Help greatly appreciated |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-28 : 12:47:30
|
| you can add a log control table for that. All steps which populate dim/fact tables should log status onto this including record counts. At end of population check for counts in this and proceed to cube process job only if the record count > 0------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
pinky2012
Starting Member
5 Posts |
Posted - 2012-02-28 : 13:52:48
|
| Hi ,Thanks for your reply.Since i am new to SSIS is there an example that you can provide.We have a job set up in SQL Server Management server 2008 that runs this package everyday.Thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
pinky2012
Starting Member
5 Posts |
Posted - 2012-02-28 : 15:42:51
|
| Hi,Thank you so much for your response.I went thru some sites and found the following solution which maybe what i am looking for.Step 1: A data flow task right before the processing of the cubes take place , that selects count of the last table that was processed and saves that count to a variable. Step 2: A script task that evaluates that count and fails if the count was 0, succeeds otherwise. Forking from this is a success route and a failure route.Is there any example that shows the above scenario.Thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-28 : 15:51:03
|
quote:
Originally posted by pinky2012
Hi,Thank you so much for your response.I went thru some sites and found the following solution which maybe what i am looking for.Step 1: A data flow task right before the processing of the cubes take place , that selects count of the last table that was processed and saves that count to a variable. Step 2: A script task that evaluates that count and fails if the count was 0, succeeds otherwise. Forking from this is a success route and a failure route.Is there any example that shows the above scenario.Thanks
thats fine so far as your table processing packages have a defined sequence and have check that parent has rows before processing child or dependent tablesOtherwise if there's an intermediate table whose population failed or has 0 records, then cube processing will still start------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
pinky2012
Starting Member
5 Posts |
Posted - 2012-02-28 : 16:29:44
|
| Thanks for your quick response visakh.We have always noticed a pattern that the last table to be processed before the cube processing takes place is always empty.Is there an example that you can provide that will enable me to do the following tasks.Step 1: A data flow task right before the processing of the cubes take place , that selects count of the last table that was processed and saves that count to a variable. Step 2: A script task that evaluates that count and fails if the count was 0, succeeds otherwise. Forking from this is a success route and a failure route.Thanks for your help. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-28 : 18:44:43
|
| why do you need a script task for this? i feel this can be done by using expression as precedence constraints------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
SSIS Package enhancement