SSIS Data Transformation - Rows to Columns

Question

I have a situation in m current ssis project. I have a huge excel with customer data. The 2 columns have identifiers for the customer data. Something like below. Rest of columns have actual data.

COLUMN A | COLUMN B
--------------------
NAME     | XYZ
ADDRESS1 | 1 STREET
ADDRESS2 | APT A    
ZIP      | 12345

The challenge is to Read the values into a variable or to a column. This is required for validations to be performed. After the transformation, I need data in below format.

NAME | ADDRESS1 | ADDRESS2 | ZIP
--------------------------------------
XYZ  | 1 STREET | APT A    | 12345

I may not be able to use Pivot transformation because this data is read using script component for a dynamic columns. Can anyone please provide me a solution for this ? Thanks for your time.

score 0 · Answer 1 · answered Oct 22 '13 at 19:09

Nothing out of the box is going to help you. The challenge you face is that your key-value data has an implicit grouping. That is, every time you see the Name key, the next N rows are associated back to the opening row.

Since you're already reading data in from a Script task, do what makes sense and instead of outputting in a KVP, redefine your output buffer to be Name, Address1, Address2, Zip with appropriate lengths. Then, in your script task, don't actually call the AddRow() method until you've reached the end of the file or you've encountered a Name row.

score 0 · Answer 2 · answered Oct 23 '13 at 16:17

Use a Script component. The code is given below. Assumption: There are no missing field values. This code will get you started. After that you would need to tweak it according to your special requirements.

public class ScriptMain : UserComponent
{
string name = string.Empty;
string address1 = string.Empty;
string address2 = string.Empty;
string zip = string.Empty;

public override void PreExecute()
{
    base.PreExecute();
}
public override void PostExecute()
{
    base.PostExecute();
 }
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    if (Row.Key.ToUpper().Equals("NAME"))
    {
        name = Row.Value;
    }
    else if (Row.Key.ToUpper().Equals("ADDRESS1"))
    {
        address1 = Row.Value;
    }
    else if (Row.Key.ToUpper().Equals("ADDRESS2"))
    {
        address2 = Row.Value;
    }
    else if (Row.Key.ToUpper().Equals("ZIP"))
    {
        zip = Row.Value;

        OutputRowBuffer.AddRow();
        OutputRowBuffer.Name = name;
        OutputRowBuffer.Address1 = address1;
        OutputRowBuffer.Address2 = address2;
        OutputRowBuffer.Zip = zip;           
    }
}

}

SSIS Data Transformation - Rows to Columns

2 Answers2