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.