SSIS Flat File Source Row Restructure

Question

I have a flat file source of thousands of records (> 100K in some cases). This source is externally procured, and I cannot request a layout revision.

In this flat file, each row contains four columns:

| User ID     | Status_1  | Status_2 | Status_3
|    1337     | Green     | Yellow   | Red
|    1234     | Red       | Red      | Green

The destination table was designed to accept two columns:

| User ID     | Status Codes
|    1337     |    Green
|    1337     |    Yellow
|    1337     |    Red
|    1234     |    Red
|    1234     |    Red
|    1234     |    Green

Until now, I have been running 3 different SSIS packages to my destination table, one for each Status Column in the Flat File.

What I would like is to use a single SSIS package, and create either another Flat File Destination or Temp Table to mirror the destination table, and import from there.

Is this achievable? If so, what are the best practice Tasks to use, rather than simply UPDATE & SET to the Temp Table.

Answer 1

heh looks like a case for good ole SQL. I would use an UNPIVOT on this one.

http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

That link has a great example which looks very similar to your data:

--Create the table and insert values as portrayed in the previous example.
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
    Emp3 int, Emp4 int, Emp5 int);
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM 
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
   FROM pvt) p
UNPIVOT
   (Orders FOR Employee IN 
      (Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO

Back when I was data warehousing, half my job seemed like it was using UNPIVOT on crap data I got through spreadsheets.