none
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 |
    Monday, February 18, 2013 11:11 PM

Answers

  • 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

    • Marked as answer by Eileen Zhao Thursday, February 28, 2013 8:19 AM
    Tuesday, February 19, 2013 1:30 AM