Generate report on data movement from source to target

mchivuku · Post by **mchivuku** » Mon Jul 27, 2009 8:04 am

Hi,

My requirement is to generate a report that gives the following details for each job that runs on the server.
Number of rows read from source - a
Number of rows inserted into target - b
Number of rows rejected - c
So in any case a = b+c

This kind of reconciliation is a part of the process in our project.
If this report is available in graphical format, then that would be appreciated.

I have tried the options in dsjobs command,DSJobReport in after job sub-routine and the metadata workbench.

But the end result is expected to be something like the DS Job Monitor which shows the data flow through the various stages in the job.

Please advice me on how to proceed.

Thanks,
Murali

kduke · Post by **kduke** » Mon Jul 27, 2009 10:50 am

Unless you have a reject link then you will never capture these numbers. There are lots of links which drop rows like remove dups. So not all jobs will add up.

EtlStats will save you a lot time building your own solution.

chulett · Post by **chulett** » Mon Jul 27, 2009 11:19 am

You may be able to compute "c" from "a-b" but as noted it's not always true rejects. You'd need to specifically capture those and even then things may not foot.

kduke · Post by **kduke** » Mon Jul 27, 2009 1:09 pm

Some lookups can create dups if more than one row comes back from lookup.

nagarjuna · Post by **nagarjuna** » Tue Jul 28, 2009 5:31 pm

we are doing similar sort of thing in our project ( Auto balancing control ABAC ) ..we are capturing the reject count , input count , o/p count .If o/p is not equal to input + reject then it means some records are dropped while passing through some of the stages . Again this will depends on what level you want to reconcile .

mchivuku · Post by **mchivuku** » Wed Jul 29, 2009 3:29 am

Thanks for all your replies!!!
I definitely agree only if rejects are designed accordingly, we will be able to reconcile source,target and rejects.
Our jobs are designed and tested fine in such a way that source is always an addition of target count and reject count.
Now the issue is only with the generation of an appropriate report.
For eg : In the job monitor,I am able to see the data count processed by each stage in the job.Hence we have renamed the appropriate stages to give more meaning for our reconciliation.

Please advice me on how a graphical report can be generated which gives me the data flow through each stage in the jobs.

Thanks,
Murali

ray.wurlod · Post by **ray.wurlod** » Wed Jul 29, 2009 5:00 pm

Run in Director, press the Print Screen when it's done and paste into MS Paint or similar. I don't know any other graphical report.