1

My file is called ... File - 20170101.xlsx

I have the following as a variable

FilePath, data type string, File - 20170101

as a derived column I have 

(DT_I8)LEFT(RIGHT(@[User::FilePath],8),4)

I Got this to work, but only gives me part of the values..(only 3 numbers, I need 8)

gives me output of 101.. do I need to save the file name as a different date format? I have tried File - 01012017

I have tried this but it does not work

(DT_I4)LEFT(RIGHT(@[User::FilePath],8),4)

I am trying to get the 20170101, then I was going to add a data conversion to turn it into a date

it does not work..any ideas please

Justin
  • 9,634
  • 6
  • 35
  • 47
pete
  • 101
  • 3
  • 11
  • It would be a lot easier to create a script component instead of trying to parse the string using VBScript functions – Panagiotis Kanavos Oct 09 '18 at 09:03
  • _What are the four numbersyou get?_ Based on your sample data you should just use `(DT_I8)RIGHT(@[User::FilePath],8)`. But perhaps your sample data is incorrect. It helps if you actually describe the output. – Nick.Mc Oct 09 '18 at 10:24
  • gives me output of 101.. do I need to save the file name as a different date format? I have tried File - 01012017 – pete Oct 09 '18 at 10:52
  • your formula will result in 101. – KeithL Oct 09 '18 at 12:48

2 Answers2

0

try a script task

string fpath=""; //Set your filepath here

string fname = System.IO.Path.GetFileNameWithoutExtension(fpath);

DateTime dt = DateTime.Parse(fname.Substring(5, 2) + "/" + fname.Substring(7, 2) + "/" + fname.Substring(1, 4));
KeithL
  • 5,348
  • 3
  • 19
  • 25
0

So the value of the variable FilePath is not File - 20170101 as you stated, it is File - 20170101.xlsx right?

Please take care when writing your question.

You can solve these things yourself with just a bit of experimentation.

Firstly,

RIGHT(@[User::FilePath],8)

gives you

101.xlsx

Therefore, 

RIGHT(@[User::FilePath],13)

will give you

20170101.xlsx

and

LEFT(RIGHT(@[User::FilePath],13),8)

will give you 

20170101

To turn that into a date is trickier.

This explains how to do it

How to convert string in format yyyyMMdd to date using SSIS expression?

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
  • thank you, I'm new to SSIS this helped a million. Also here is link to best page to help with the conversion . thank you team x https://social.msdn.microsoft.com/Forums/sqlserver/en-US/09ef7f45-d31b-44a4-b58e-0053a276e369/ssis-type-cast-a-datetime-column?forum=sqlintegrationservices&prof=required – pete Oct 16 '18 at 09:17