SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Is there a SSIS Component for Dynamic Updates
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mivey4
Yak Posting Veteran

USA
66 Posts

Posted - 09/11/2012 :  09:37:59  Show Profile  Reply with Quote
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

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 09/11/2012 :  11:26:50  Show Profile  Reply with Quote
looks like a logic you need to implement using execute sql task to me

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

mivey4
Yak Posting Veteran

USA
66 Posts

Posted - 09/11/2012 :  12:13:22  Show Profile  Reply with Quote
Thanks visakh16.

Those are my exact thoughts as well but I wanted to affirm by confirmation of several other Developers before concluding that T-SQL would be the only viable option.

Oracle OCA
Adaptec ACSP

Edited by - mivey4 on 09/11/2012 12:13:44
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000