SSIS error checking loop

• matthewk

  SSC Enthusiast

  Points: 167

  More actions

  December 29, 2009 at 2:39 pm

  #218991

  Here's the situation:

  One or multiple files are imported directly to a single table in a database. My process needs to review this table, perform a series of error checks on each row, write all errors to an error reporting table, and then process the error-free rows into separate tables. Each row needs to check for all possible errors, and if the row has one or more it will not be processed.

  The information contained in the table consists of groupings of 'families'. The table will hold multiple family groupings, each family group can have multiple family members, but there is only one family leader per group. Each row has a FamilyID (unique to the family group), and an identifier to mark whether the row is the family leader - IsLeader.

  There are error checks that need to happen to only the family leaders, and some of these checks can cause the entire family to be set to invalid. There are also error checks the need to happen to only family members.

  What I think needs to happen is something like the following.

  - Need to pull all of the rows from the table that are family leaders, but only the necessary columns for identification, error checking, and an additional column on each row to designate whether or not a row is valid and can be processed (IsValid). This block of data will need to be able to be read from and written to.

  - Need to pull all of the remaining rows from the table (should just be family members left) in a fashion identical to the family leaders (identification columns, checks, and an IsValid additional column).

  - Loop through all of the rows within the family leader block in a foreach loop container that would contain all of the necessary checks and error reporting; wherein each of the checks would be able to set the IsValid flag of the row to false. These checks would also potentially need to be able to set the IsValid flag to false for multiple members of a family (the second block of data).

  - Loop through all of the rows within the family members block in a foreach loop container and complete the checks which would set individual member's IsValid columns to false.

  Where I'm getting stuck is the implementation. I'll admit that I'm new to SSIS, but I've been trying a few things without success. For example, I had tried working with an ADO Recordset, but I didn't find a way to use the Recordset as a data source. I could store the recordset into a variable, but I didn't find a way to use either the variable or a recordset as a datasource within a Data Flow Task where I had intended to perform checks and error processing.

  I had seen suggestions for putting the information into flat files, but I'm hesitant to do so, as I would think there would be better ways to handle this.

  Has anyone had to do something similar in SSIS? Does my situation bring any ideas to mind? I'm very open to reasonable alternative implementations.

  Thank you for your consideration!