April 22, 2015 at 4:45 pm
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
April 23, 2015 at 12:31 am
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.
May 1, 2015 at 1:39 pm
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