2

This is a bit of a conundrum I am trying to solve using SSIS and a conditional-split transformation. I have a .csv file that contains attribute data in one row for each unique user and the values for each attribute in another column. i.e.:

Attribute, Attribute Type

ID, 0000000001

Birthdate, 09/02/1976

Role, Manager

Or something of the sort. I need to split the attributes into columns that include the Attribute Type Data. So the desired outcome would be:

ID,                    Birthdate,              Role,

0000000001,             09/02/1976,            Manager,

I then need to insert them into one SQL table with the new columns.

I was able to accomplish this beautifully with a conditional-split transformation for one column (using the expression Attribute == "ID" for example and then mapping the entire Attribute column in the .csv source onto the ID column in the SQL destination table) but the problem is doing so for the other columns. I can't seem to get a Union All transformation to do what I want it to do.

Any advice?

Hadi
  • 31,125
  • 9
  • 49
  • 111
ManMadeNova
  • 71
  • 12
  • 1
    is using SQL Server/t-sql out of the question? This looks like just a pivot of data in SQL Server. Load the data into a table as is, query for that raw, loaded data with pivot to get your desired results will work, but curious if you are ruling out t-sql as a solution. If so, then ignore me :) – jamie Jun 10 '19 at 22:12
  • I actually tried using pivot in SQL Server before I received any answers here. And it works really well but the issue I'm running into is with the aggregate function. If I use MAX(Attribute Type) for Attribute in ([ID], [Birthdate], [Role]) the result set is returning only one value per user, when some users have several (i.e. they may have more than one Role in the company, such as Manager and Data Analyst, but it is only returning Manager). I am trying to figure out a solution to this now. – ManMadeNova Jun 11 '19 at 12:00

1 Answers1

1

You can achieve that using a script component:

  1. Add a script component
  2. Go to the Inputs and Outputs tab
  3. Add 3 Output columns : ID, BirthDate, Role
  4. Set the SynchronousInput property to None

enter image description here

  1. Inside the script editor, write a similar script:
string ID = "";
string BirthDate = "";
string Role = "";
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
   if(!Row.Attribute_IsNull && !String.IsNullOrWhiteSpace(Row.Attribute))
    {

        switch (Row.Attribute)
        {

            case "ID":
                ID = Row.AttributeType;
                break;

            case "BirthDate":
                BirthDate = Row.AttributeType;
                break;

            case "Role":
                Role = Row.AttributeType;

                Output0Buffer.AddRow();
                Output0Buffer.ID = ID;
                Output0Buffer.Role = Role;
                Output0Buffer.BirthDate = BirthDate;

                break;
            default:
                break;
        }
    }
}
  1. Map the Output columns to the OLE DB Destination
Hadi
  • 31,125
  • 9
  • 49
  • 111
  • @ManMadeNova check that the output name is `Output0` – Hadi Jun 17 '19 at 12:55
  • If I wanted to split Attribute into Columns with the Attribute Type column data as row data based on another column (for example, an ID column) how would I do this? So for example, each person has more than one Role (which is under the Attribute column) and that is attached to their ID, which is unique for each person. So for ID 0000000001 I need their Roles (let's say Manager and Analyst in separate rows) and Birthday in distinct columns. – ManMadeNova Jun 19 '19 at 14:16
  • @ManMadeNova why not posting a new question with more details? – Hadi Jun 19 '19 at 21:40