How to parse a date from an SSIS Excel filename

Question

I want to use the foreach container to iterate through a folder matching something like: "Filename_MMYYYY.xls". That's easy enough to do; but I can't seem to find a way to parse the MMYYYY from the filename and add it to a variable (or something) that i can use as a lookup field for my DimDate table. It seems possible with a flat file data source, but not an excel connection. I'm using Visual Studio 2005. Please help!

Answer 1

Do I understand correctly that you want to take your filename, deconstruct it, and get a date-typed variable out of it? If so, then you need to start with the filename variable that you get from the Foreach Loop - I'll call that variable @FileName.

First, make a new variable - @FileDate - as a DateTime type. Go to its properties window (F4), and set the EvaluateAsExpression property to True. Edit the expression, and type in something like this (you may need to tweak):

(DT_DBTIMESTAMP)(SUBSTRING(@FileName, 12, 4) + "-" + SUBSTRING(@FileName, 10, 2) + "-01")

Now, if you want to take that date value and use it in your Data Flow, you can just use it straight in a Derived Column transform, or in an expression on your Lookup SQL statement, or wherever.