Dividing the sale amount by month

Hello,

I want to process a row from a source table by dividing the sales amount in that row over the period of the sale by month.

For instance if an item is sold for 500$ and it's duration is 5 months from 1/15/2004 till 6/15/2004, I want to divide the sale amount by month as follows:

Month 1: 50$

Month 2: 100$

Month 3: 100$

Month 4: 100$

Month 5: 100$

Month 6: 50$

I know I can create a script component and do the calculation for each month and insert 6 records in the fact table for each row in the source table, where each record holds the amount for the corresponding month. However I was wondering if there is another technique that utilizes the components of SSIS to do it more efficiently.

Thanks,

Grace

Its definately possible but I would say if you've already figured out how to do it in script component why bother trying to do it elsewhere? I doubt you'd be able to do it more efficiently either!

-Jamie

|||

Thanks Jamie,

I just wanted to confirm my method. I always tend to think that script component is not much efficient especially when i'm using it to open connection to the database and insert multiple rows. It is faster when SQL Destination is used. But in my case i can't use the destination component.

Grace

|||

There's no problem with perf of the script component. It is compiled code (different from DTS) so it is very quick!

-Jamie

Distribution cleanup

Are transactions that have been distributed to all subscribers always stored the maximum retention period in the distribution database?

I'm using peer-to-peer replication.

No, they are stored the minimum retention period and are cleanup by the distribution clean up task. If a subscriber is offline, the transactions will be stored for the maximum retention period unless the subscriber is expired by this history retenion level.|||

But if the minimum retention period is set to 0 shouldn't all delivered transactions be cleaned up in that case.

In my peer-to-peer replication I have a publication with four subscribers and everything works fine all transactions are replicated and I don't have any latency problems.

So why is the MSrepl_commands still growing.

I can see in the output for distribution cleanup job that transactions actually are cleaned up but the MSrepl_commands grows. From time to time a the distribution cleanup job gets selected as a deadlock victim.

The size of the distribution db is now about 50GB with 100'000'000 rows in the MSrepl_commands table.

/Peter