Creating an AVP file with multi-valued attributes...

  • I have an SSIS package that currently results in a couple of tables, for simplicity:

    TableA has one row for each entity

    TableB has none or more rows for each entity and contains the values for the multi-valued attributes.

    Example Table A:

    FirstName LastName EmployeeNumber

    Peter Griffin 12345

    Glen Quagmire 54321

    Joe Swanson 54123

    Example Table B:

    EmployeeNumber AttributeName AttributeValue

    12345 JobCodeHistory B123

    12345 JobCodeHistory B056

    54123 JobCodeHistory B345

    I need to coalesce this information into an AVP file. It might look like this:

    EmployeeNumber: 12345

    FirstName: Peter

    LastName: Griffin

    JobCodeHistory: B123

    JobCodeHistory: B056

    EmployeeNumber: 54321

    FirstName: Glen

    LastName: Quagmire

    EmployeeNumber: 54123

    FirstName: Joe

    LastName: Swanson

    JobCodeHistory: B345

    The tables are in SQL Server 2012. What's the best way to approach this in SSIS?

    I figure it's easy enough to use a script component to create the file from the first table, but how do you do the lookups to get the multi-valued stuff in there?

    There are probably about 1.5 million records in TableA and over 2 million in TableB.

    Any insight is appreciated.

    J

  • One way of achieving this is by creating your source query such that you get one row per job code history. So, get results that look like this:

    (EmployeeNumber, FirstName, LastName, AttributeName, AttributeValue)

    12345,Peter,Griffin,JobCodeHistory,B123

    12345,Peter,Griffin,JobCodeHistory,B056

    54321,Glen,Quagmire,,

    Process this file using an asynchronous script component.

    This could work like this, for each row in the file:

    1) Is the employee on this row different from the one on the preceding row?

    a) Yes: Write out EmployeeNumber, FirstName, LastName, AttributeName, AttributeValue

    b) No: Write out AttributeName, AttributeValue.

    With some additional logic to create the blank row between employees.

    The script component needs to be async because the number of rows going into it is different from the number coming out.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Works like a charm... Thanks for the insight.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply