Inline structure of SSIS string resources
I have a flat file with thousands of records (> 100K in some cases). This source is in externals and I am unable to request a revision of the layout.
In this flat file, each line 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
So far, I have had three different SSIS packages in my destination table, one for each status column in a flat file.
I would like to use one 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 tasks are best used and not simply UPDATE
and SET
for Temp table.
source to share
heh looks like a case for good ole SQL. I would use UNPIVOT on this.
http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
This link has a great example that looks a lot like 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 a data warehouse, half of my work seems to have been using UNPIVOT for the crap data I got through spreadsheets.
source to share