etl - How can I extract values from a custom flat file header into variables? -

i have been stuck while problem , have no clue. trying upload multiple csv files has dates wanted dates stored date variables use date variables form part of column in table using script componet , have no idea how create dates date variables in ssis.

csv files 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 

i want relative date , run date stored date variables. hope made sense.

your best solution use script task in control flow. pre-process csv files - can parse first 2 rows, retrieving wanted dates , storing them 2 variables created beforehand. (http://msdn.microsoft.com/en-us/library/ms135941.aspx)

important make sure when passing variables script task set them readwritevariables. use these variables in way desire afterwards.

---

updated quick walkthrough:

i presume csv files want import located in same directory:

add foreach loop container loop through files in specified directory , inside, script task responsible parsing 2 dates in each of files , data flow task use file import.

create variables using - 1 filename/path, 2 two dates want retrieve. these won't fill in done automatically in process.

set-up foreach loop container:

1. select foreach file enumerator
2. select directory folder contain files. (even better, add variable take in path specify. can read enumerator using expression builder)
3. wildcard files searched in directory.

you need map each filename enumerator generates variable created earlier.

open script task, add 3 variables readwritevariables section. important, otherwise won't able write variables.

this script used purpose. not best, works example.

public void main() {   string filepath = this.dts.variables["user::filename"].value.tostring();   using (streamreader reader = new system.io.streamreader(filepath))   {     string line = "";     bool getnext = true;      while (getnext && (line = reader.readline()) != null)     {       if(line.contains("relative date"))       {         string date = getdate(line);         this.dts.variables["user::relativedate"].value = date;          // test event information         bool fireagain = false;         this.dts.events.fireinformation(1, "rel date", date,                                         "", 0, ref fireagain);       }       else if (line.contains("run date"))       {         string date = getdate(line);         this.dts.variables["user::rundate"].value = date;          // test event information         bool fireagain = false;         this.dts.events.fireinformation(1, "run date", date,                                         "", 0, ref fireagain);          break;       }     }   }   dts.taskresult = (int)scriptresults.success; }  private string getdate(string line) {   regex r = new regex(@"\d{2}/\d{2}/\d{4}");   matchcollection matches = r.matches(line);   return matches[matches.count - 1].value; } 

the results execution of script task 2 csv files. dates can used in way fancy in data flow task. make sure skip first rows don't need import in source configuration.