Monday, February 18, 2013 11:11 PMI 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
CSV data 2:
Relative Date: 14/02/2013
Run Date: 17/02/2013
Fourth Avenue, E12,130
In the Data Warehouse, I want the *`Relative Date`* and *`Run Date`* along the columns as shown below:
| 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 |
Tuesday, February 19, 2013 1:30 AM
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 ZhaoMicrosoft Contingent Staff, Moderator Thursday, February 28, 2013 8:19 AM