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 Agent failed with error

My Snapshot was successful but an error occurs when I try to run the
distribution agent.
Invalid object name 'PA_COLUMN_NAMES1_V'.
(Source: ADMIN1SERVER\ADMINSQL (Data source); Error number: 208)
------
Unable to replicate a view or function because the referenced objects or
columns are not present on the Subscriber.
(Source: AGENT (Agent); Error number: 00000)
Any help here would be appreciated,
Rick Rushing
"Rick Rushing" schrieb:

> My Snapshot was successful but an error occurs when I try to run the
> distribution agent.
> Invalid object name 'PA_COLUMN_NAMES1_V'.
> (Source: ADMIN1SERVER\ADMINSQL (Data source); Error number: 208)
> ------
> Unable to replicate a view or function because the referenced objects or
> columns are not present on the Subscriber.
> (Source: AGENT (Agent); Error number: 00000)
> --
> Any help here would be appreciated,
> Rick Rushing
1. Check if the referenced table is part of the publication (if yes: try two
seperate publications: the first one includes the referenced table and the
second one includes the function).
2. Check the ownership of the objects (error 208 in replications often
appears if the owner of the objects in the publisher is not 'dbo'). The use
of a different owner than 'dbo' for the objects + the use of the rather
inflexible replication wizzards unfortunately is a little buggy
|||The view refers to either a table which isn't part of the publication, or
another view in which case the metadata doesn't reflect the dependencies.
AFAIK this error can't refer to a function or stored procedure, as both use
deferred name resolution, consequently the absence of dependant objects on
the subscriber at synchronization time is not an issue. You could use
logging to find out the objects causing dependancy problem. If it is between
views then you could try to fix it by refreshing the objects, assuming they
are all replicated together in the same publication. If it's a dependency
between the view and table, you could follow Christian's good advice and
make sure the table is also replicated - either the same publication, or
another which initializes before this one.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)