| Author |
Topic |
|
zion99
Posting Yak Master
141 Posts |
 Posted - 2008-07-15 : 06:59:15
|
| Hi,Using SSIS scripts, data is being populated into some 10 tables. After this, i use a join on these tables to populate data into 11th table. Surprisingly, i have seen that when i run the SSIS scripts for the first time, (i.e. when ALL 11 tables dont have any records), data is not populated into the 11th table. i have written a stored procedure which i call from SSIS scripts; the sole purpose of this sp is to fill the data into 11th table. But this doesnt happen for the first time. when i run the script for the second time, data is populated in the 11th table. isolation mode is Serializable for all the SSIS data flow tasks. I dont know what is going wrong.... btw... all tasks display that they have executed successfully. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-15 : 11:49:30
|
| Didnt understand wht is problem here. How do you expect 11th table to have data when 10 tables which are used in join dont have any data? |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-15 : 11:51:43
|
| Or is it that SSIS runs completing all steps but you dont have data in 11th? Have you tried putting a breakpoint before 11th table population script. and checked the data coming from data flow path? |
|
|
|
zion99
Posting Yak Master
141 Posts |
Posted - 2008-07-16 : 01:53:44
|
yeah, Data is populated properly in all the 10 tables. but data is not populated in the 11th table. just a little background.this 11th table is a fact table (has foreign key constraints with all the 10 tables) and thus can be populated only after data is populated in all the 10 tables. ACTUAL PROBLEM: when there are 0 records in ALL the tables. when i run the SSIS scripts for the first time, fact table does not have any records. But after that it is populated with records...i simply cant understand what goes wrong during the first time...  |
|
|
|
zion99
Posting Yak Master
141 Posts |
Posted - 2008-07-16 : 02:31:54
|
| i just debugged it... for all the data flow tasks (DFT) except for the 11th table, there are records inserted into the tables. (the arrows from source to destination in the Data Flow window show the same), but the DFT for the 11th tables returns NOTHING (it does not even show even 0 records copied from source to destination)i have put RunInOptimizedMode for all DFTs to be false, but it doesnt help. i guess, commit for data does not happen till the the complete package is executed & that's y my join query of 10 tables returns nothing... is there any way to ensure that a commit happens before this DFT |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-16 : 06:49:56
|
quote:
Originally posted by zion99
i just debugged it... for all the data flow tasks (DFT) except for the 11th table, there are records inserted into the tables. (the arrows from source to destination in the Data Flow window show the same), but the DFT for the 11th tables returns NOTHING (it does not even show even 0 records copied from source to destination)i have put RunInOptimizedMode for all DFTs to be false, but it doesnt help. i guess, commit for data does not happen till the the complete package is executed & that's y my join query of 10 tables returns nothing... is there any way to ensure that a commit happens before this DFT
How are you extracting the data from 10 tables for fact? what all fields are there in fact?Are you bringing data from your source onto warehouse and using it to lookup and get surrogate keys from dimensions? |
|
|
|
zion99
Posting Yak Master
141 Posts |
Posted - 2008-07-16 : 07:58:19
|
| 1. i m bringing data from source to warehouse (10 tables)2. i bring another lookup table (say LKPTBL) from source to warehouse (this contains details for connecting 10 tables)till this point, everything goes fine.Next step is using a join query to connect this LKPTBL with other 10 dimension tables. this step fails, no records are populated in the Fact table. Next time i run the SSIS script, it works fine... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-16 : 08:07:07
|
quote:
Originally posted by zion99
1. i m bringing data from source to warehouse (10 tables)2. i bring another lookup table (say LKPTBL) from source to warehouse (this contains details for connecting 10 tables)till this point, everything goes fine.Next step is using a join query to connect this LKPTBL with other 10 dimension tables. this step fails, no records are populated in the Fact table. Next time i run the SSIS script, it works fine...
Have you used any containers in package? |
|
|
|
zion99
Posting Yak Master
141 Posts |
Posted - 2008-07-16 : 08:36:10
|
| yeah, for some dimension tables which have a pretty large number of records, i use Sequence containers --> contains 1 data flow task & 2 Execute SQL tasks (I copy the data from source tables to a temporary table (say TMPTBL). this TMPTBL is truncated during every load. After this is filled, i use Checksum function to compare this TMPTBL with the destination table (say DSTTBL). both TMPTBL & DSTTBL are exact replica. in the Stored procedure, based on the checksum values, either insert or update happens on DSTTBL.For some dimension tables with very few number of records, i use Slowly changing dimensions. The benefit is doing away with temporary tables and stored procs. disadvantage is it is pretty slow, hence i use it only for tables with fewer records.Finally, the fact table data-fill is also covered in a Sequence container (one temporary table which is truncated during every load, compared with the Original fact table using checksum.) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-16 : 10:32:50
|
quote:
Originally posted by zion99
yeah, for some dimension tables which have a pretty large number of records, i use Sequence containers --> contains 1 data flow task & 2 Execute SQL tasks (I copy the data from source tables to a temporary table (say TMPTBL). this TMPTBL is truncated during every load. After this is filled, i use Checksum function to compare this TMPTBL with the destination table (say DSTTBL). both TMPTBL & DSTTBL are exact replica. in the Stored procedure, based on the checksum values, either insert or update happens on DSTTBL.For some dimension tables with very few number of records, i use Slowly changing dimensions. The benefit is doing away with temporary tables and stored procs. disadvantage is it is pretty slow, hence i use it only for tables with fewer records.Finally, the fact table data-fill is also covered in a Sequence container (one temporary table which is truncated during every load, compared with the Original fact table using checksum.)
What have you set for transaction property of Sequence containers. |
|
|
|
zion99
Posting Yak Master
141 Posts |
Posted - 2008-07-16 : 23:31:24
|
| do u mean - "TransactionOption" - i have not made any change... it is "Supported" for all, even for Data flow tasks using Slowly changing dimensions. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-17 : 01:39:23
|
quote:
Originally posted by zion99
do u mean - "TransactionOption" - i have not made any change... it is "Supported" for all, even for Data flow tasks using Slowly changing dimensions.
ok. i was just checking if you're using some explicit transactions inside.are you executing this population step in parallel to another one which populates one of 10 tables? |
|
|
|
zion99
Posting Yak Master
141 Posts |
Posted - 2008-07-17 : 01:42:36
|
| no... it executes after all the tasks have executed... i have used precedence constraint (Completion) to take care of this... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-17 : 11:58:38
|
quote:
Originally posted by zion99
no... it executes after all the tasks have executed... i have used precedence constraint (Completion) to take care of this...
can you explain briefly on the tasks inside this data flow task? |
|
|
|
zion99
Posting Yak Master
141 Posts |
Posted - 2008-07-18 : 07:48:30
|
| There is one 'Execute SQL Task' --> Truncate lookup table (say LKPTBL) (SUCCESS)'Data flow task' --> get data for LKPTBL from source to warehouse (this contains details for connecting 10 tables) (SUCCESS)'Execute SQL Task' --> execute stored procedure (using LKPTBL & 10 dimension dimensions, there is a join created to fetch the data into FACT table. (FAILURE when run for the first time) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-18 : 08:13:34
|
quote:
Originally posted by zion99
There is one 'Execute SQL Task' --> Truncate lookup table (say LKPTBL) (SUCCESS)'Data flow task' --> get data for LKPTBL from source to warehouse (this contains details for connecting 10 tables) (SUCCESS)'Execute SQL Task' --> execute stored procedure (using LKPTBL & 10 dimension dimensions, there is a join created to fetch the data into FACT table. (FAILURE when run for the first time)
whats the error given in progress tab for failure of sp? |
|
|
|
zion99
Posting Yak Master
141 Posts |
Posted - 2008-07-18 : 08:30:43
|
| sorry... i didnt mean a FAILED task. I meant it runs successfully; yes, it shows that all the 3 tasks have completed successfully. but when i fire a select query on the FACT table, it returns 0 records... by that way i meant failure... BUT, when i run the package second or any time.. FACT table is properly updated.when i say FIRST time, i mean, all the dimension tables including the FACT table have NO records. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-18 : 10:04:05
|
quote:
Originally posted by zion99
sorry... i didnt mean a FAILED task. I meant it runs successfully; yes, it shows that all the 3 tasks have completed successfully. but when i fire a select query on the FACT table, it returns 0 records... by that way i meant failure... BUT, when i run the package second or any time.. FACT table is properly updated.when i say FIRST time, i mean, all the dimension tables including the FACT table have NO records.
didnt understand how you're populating the dimensions and facts in the same procedure. ho do you get source table for dimensions in procedure? |
|
|
|
|
Strange problem in SSIS