Automate Identification of Reject Data

[Former Member]

Hey everyone! Quick question - is there a way to automate the identification and retrieval of Reject Data following a dump & load task?

I have a custom SSIS package that runs each night and loads transaction data. Most of the master data changes have been automated, however some of the dimensions are still maintained manually. Because of this, on occasion, data is brought in through the custom package for dimension members that have not yet been added to BPC. When this happens, the users will add the dimension members at their next availability and then re-run the package. What I want to happen is have the rejected records automatically get added to a SQL table after each package run, so that if the dimension changes aren't made for multiple days it simply keeps appending to the table and I have a single list. My problem is that I can't seem to automate the identification of the rejected records. They get dumped into a reject log, but is there a dynamic constant for this reject list, or is there another location where these can be retrieved in an automated fashion?

Once i have this table, I have another package that will simply run the import using the data in this Reject SQL table, and users will be able to run this on demand once they've made their dimension changes.

Any help would be greatly appreciated!

Josh

[JohnL · Accepted solution]

Hi Josh,

That reject list which gets stored to the PrivatePublications\TempFiles folder has the following naming convention:

<PackageName>_<SequenceNumber>_reject_<RandomNumber>.Log

The sequence number (SEQ column) and PackageName (PackageID) appear in the tblDTSLog table within your AppSet DB.

You can figure out a way to read that file but the only issue is you won't know what that RandomNumber is.

I think an easier way to do what you want is read in the transaction data to a staging table, cross reference the dimension ID's from the staging table with the mbr<DIMENSION> tables (i.e. mbrEntity) and store the invalid fact records that exist in the staging table but not in your mbr<DIMENSION> tables into another reject table.

Thanks,

John

[Former Member]

Yeah, that's what I figured - I was just hoping for something a little cleaner! Thank you very much John!