Get Date from filename or file path and load it in table using SSIS

+2 votes
asked Dec 10, 2015 in SSIS by user234 (980 points)

I have a list of Files in a Folder. All file names end with date ex (XYZ20141225).
Ive to store the date value at the end of each filename into a variable called xvar and map that variable to a column called ULoadDate(datetime) in my DB

1 Answer

+1 vote
answered Dec 10, 2015 by darsh (3,020 points)
selected Feb 5, 2016 by Admin
 
Best answer

Based on Example
xyz20151228

(SUBSTRING(Substring(RIGHT( @[User::loop],FINDSTRING(REVERSE(
@[User::loop] ),"\",1) ),4,8) , 1, 4) + "-" +
SUBSTRING(Substring(RIGHT( @[User::loop], FINDSTRING(REVERSE(
@[User::loop] ),"\",1) ),4,8), 5, 2) + "-" +
SUBSTRING(Substring(RIGHT( @[User::loop],FINDSTRING(REVERSE(
@[User::loop] ),"\",1) ),4,8), 7, 2))



It will return
2015-12-28

Note :- @[User::loop] is variable which u are going to declare in for each loop container
Use it in your Data Flow, you can just use it straight in a Derived Column transform

                                    OR 

Make a new variable - @Datefromfilename - as a DateTime type. then go to variable property window (F4), and set the EvaluateAsExpression property to True. Edit the expression, and type in following expression

(DT_DBTIMESTAMP(SUBSTRING(Substring(RIGHT( @[User::loop],
FINDSTRING(REVERSE( @[User::loop] ),"\",1) ),4,8) , 1, 4) + "-" +
SUBSTRING(Substring(RIGHT( @[User::loop],FINDSTRING(REVERSE(
@[User::loop] ),"\",1) ),4,8), 5, 2) + "-" +
SUBSTRING(Substring(RIGHT( @[User::loop],FINDSTRING(REVERSE(
@[User::loop] ),"\",1) ),4,8), 7, 2))


Welcome to I Can Crack, where you can ask questions and receive answers from other members of the community.

68 questions

47 answers

3 comments

5,318 users

...