wrote:
> Any Microsoft guys out there?
>
> I posted this up yesterday and haven't got any response other than
> others having problems too.
>
> I'm trying to use the SCD on the Employee table for one of the bigger
> companies in the US. It works fine with small data loads but when I
> push the 100k employee feed through it every day, it errantly creates
> historical records. Upon comparison of the records with the same
> Business Key, the data seems to be exact.
>
> Of course I can do the whole process in a stored procedure with
> straight TSQL which runs 10 times faster, but I would rather use the
> robust new SSIS product to keep it out of the box and easily
> supportable by others following.
>
> Supportable seems to be the problem here. The SCD seems to be a black
> box. What do I have to do to trap the decision process that the SCD is
> making? I can see that the tasks appears to be setup correct ( my
> assumption, I'm not an BIDS expert yet ), and that the output is
> definetly wrong. How do I observe the SCD's errant decision making
> process??
>
> I believe the problem lies with the Business Key field itself as the
> problem is coming down the "new output" pipe which should only happen
> when it does not find an existing business key ( alphanumeric 7 char
> UserID ). The odd thing is that if I put a lookup above the SCD on the
> same field, trying to throw the errors ( no lookup found ) directly to
> an OLEDB Insert Destination, zero records fail the lookup. But then
> the SCD moves a small percent of the 100k records down the "New
> Output" path as if there was no pre-existing UserID. Upon comparison
> after the fact, I have new historical records for a given UserID with
> all data duplicated except for the EmployeeKey of course.

"KenWhitesideDBA" <kenwhitesidedba@gmail.com> wrote in message
news:1173881789.596634.267920@o5g2000hsb.googlegroups.com:

> On Mar 14, 7:20 am, "KenWhitesideDBA" <kenwhiteside...@gmail.com>
> wrote:
> > Any Microsoft guys out there?
> >
> > I posted this up yesterday and haven't got any response other than
> > others having problems too.
> >
> > I'm trying to use the SCD on the Employee table for one of the bigger
> > companies in the US. It works fine with small data loads but when I
> > push the 100k employee feed through it every day, it errantly creates
> > historical records. Upon comparison of the records with the same
> > Business Key, the data seems to be exact.
> >
> > Of course I can do the whole process in a stored procedure with
> > straight TSQL which runs 10 times faster, but I would rather use the
> > robust new SSIS product to keep it out of the box and easily
> > supportable by others following.
> >
> > Supportable seems to be the problem here. The SCD seems to be a black
> > box. What do I have to do to trap the decision process that the SCD is
> > making? I can see that the tasks appears to be setup correct ( my
> > assumption, I'm not an BIDS expert yet ), and that the output is
> > definetly wrong. How do I observe the SCD's errant decision making
> > process??
> >
> > I believe the problem lies with the Business Key field itself as the
> > problem is coming down the "new output" pipe which should only happen
> > when it does not find an existing business key ( alphanumeric 7 char
> > UserID ). The odd thing is that if I put a lookup above the SCD on the
> > same field, trying to throw the errors ( no lookup found ) directly to
> > an OLEDB Insert Destination, zero records fail the lookup. But then
> > the SCD moves a small percent of the 100k records down the "New
> > Output" path as if there was no pre-existing UserID. Upon comparison
> > after the fact, I have new historical records for a given UserID with
> > all data duplicated except for the EmployeeKey of course.
>
> I think I have figure out the problem. The definition of the "Current
> Record". I have some current records that are of a status of
> deactivated and therefore have an ending_date populated. The SCD
> appears to be ignoring these because the definition of current to the
> SCD is that ending_date must be null. Now I am going to have to get
> tricky and add my status_code field into the equation.