|
mivey4
Yak Posting Veteran
USA
66 Posts |
 Posted - 09/11/2012 : 09:37:59
|
I have a table with potentially billions of records. The requirement is to set certain fields for a group of records within the table to a previous value until that value changes, then replace the previous value with the new value.
Consider the following as an example of the records being retrieved from the original source:
Event EventDesc DescId Desc2Id Desc3Id Desc4Id Desc5Id
---------------------------------------------------------------
1 EventOne | 1 | 102 | 1 | 1 | 1 |
2 EventTwo | 2862 | 1 | 1 | 1 | 131 |
3 EventThree | 1 | 1 | 1 | 1 | 1 |
6 CloseRecord | 1 | 102 | 1 | 102 | 1 |
1 EventOne | 1 | 105 | 1 | 1 | 1 |
2 EventTwo | 2873 | 1 | 1 | 1 | 131 |
3 EventThree | 1 | 1 | 106 | 1 | 1 |
6 CloseRecord | 1 | 101 | 1 | 102 | 1 |
The desired output after insertion into the final table would be:
Event EventDesc DescId Desc2Id Desc3Id Desc4Id Desc5Id
---------------------------------------------------------------
1 EventOne | 1 | 102 | 1 | 1 | 1 |
2 EventTwo | 2862 | 102 | 1 | 1 | 131 |
3 EventThree | 2862 | 102 | 1 | 1 | 131 |
6 CloseRecord | 2862 | 102 | 1 | 102 | 131 |
1 EventOne | 1 | 105 | 1 | 1 | 1 |
2 EventTwo | 2873 | 105 | 1 | 1 | 131 |
3 EventThree | 2873 | 101 | 106 | 1 | 131 |
6 CloseRecord | 2873 | 101 | 106 | 102 | 131 |
As you can see, each field should acquire the value of the previous field's record unless it is a value of one. Also, the range of records that repeat this rule are grouped and range from 1 through 6 as the Event value.
Is there an SSIS component that can gracefully accomplish this task during the initial record insertions with minimal complexity as opposed to performing complex SQL updates to the table after it's population?
It was suggested to me that the Cache Transform task may be the solution but after reading up on how to implement it, it doesn't seem to be the appropriate solution for the problem.
If there isn't any SSIS package task that could accomodate as a solution, what are some other possible alternatives? I am open to any reasonable suggestions.
Thanks
Oracle OCA
Adaptec ACSP |
|
Is there a SSIS Component for Dynamic Updates
