Saving Date based in flat file into variable (SSIS)

Question

I've a flat file with many columns, one of which is Date. Format is as following '25.01.2016'. I've many rows, and for each row date is the same ('25.01.2016'). Flat file is rewritten every day, so date changes every day.

I have created a dataflow to move data from txt to database table. Now, I want to add Send Mail Task and in subject I want to use that date (ex. Sales for 25.01.2016). For that reason I need to save that date in a variable. How can I do that?

Answer 1

suppose your file has the format as bellow

id    | product     |  dateLoad
1     |  dell       | 25-01-2016 16:23:14 
2     |  hp         | 25-01-2016 16:23:15 
3     |  lenovo     | 25-01-2016 16:23:16

the best think to save some text in variable:use the Script Task

• Firstly create a new variable inside your package in my case i created one and i called DATEVAR the data type of the variable is String

-Secondly add a new component called the Script Task see the picture as bellow

-Thirdly we are going add some code inside Script Task

• Click in the Script Task and select your variable in my case the name of my variable is DATEVAR see the picture as bellow

• Then click Edit Script in side the main method add the code as bellow

String path =@"Destination of your file ";

using (StreamReader sr = new StreamReader(path))
{
  String line;
  String[] sp;
  for (int i = 1; i <=2; i++)
  {
      line = sr.ReadLine();
      sp=line.Split('|'); 
      Dts.Variables["User::DATEVAR"].Value = sp[2]; 
  }
}
Dts.TaskResult = (int)ScriptResults.Success;

More details see the picture as bellow