How to convert the date values field on flat file as header columns within SSIS

Question

I am trying to upload multiple CSV files which has dates but I wanted the dates to be part of the column and I have no idea how its done in SSIS

CSV files look as shown below when opened in Excel.

   CSV data 1:

    Relative Date: 02/01/2013
    Run Date: 15/01/2013
    
    Organisation,AreaCode,ACount
    Chadwell,RM6,50
    Primrose,RM6,60

    CSV data 2:

    Relative Date: 14/02/2013
    Run Date: 17/02/2013
    
    Organisation,AreaCode,ACount
    Second Ave,E12,110
    Fourth Avenue, E12,130


In the Data Warehouse, I want the *`Relative Date`* and *`Run Date`* along the columns as shown below:

 Desired output:

    | Organisation      | AreaCode | ACount | RelativeDate | RunDate |
    | ---------------------|------------- | ---------- | ---------------- | --------------- |
    | Second Avenue  |  E12         |  110       |  14/02/2013  | 17/02/2013 |
    | Fourth Avenue   |  E12         |  130       |  14/02/2013  | 17/02/2013 |
    | Chadwell           |  RM6         |  50         |  02/01/2013  | 15/01/2013 |
    | Primrose            |  RM6         |  60         |  02/01/2013  | 15/01/2013 |

Answer 1

One way would be to create two connection managers for the file.  One connection manager has two columns divided by the colon, without a header.  You would read this in one data flow that uses a script component to assign the value from the Relative Date row to one variable and the Run Date to the other.  By the way, this is a bit more complicated than this.  You will need to assign the values to class level variables and then write the values of the class level variables to the SSIS variables.

Next you need a connection manager that skips the first 3 lines and has column headers.  You will use the comma delimitter.  Next in your data flow you would add a derived column transformation that adds the two SSIS variables to the flow.

---

Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com