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!
+1
A:
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.
Todd McDermid
2010-05-04 20:58:07
The problem is that I don't see the @FileName variable associated with the Foreach loop. I can create a variable called fileName, but it's not going to be associated with the actual filename. I do see system variables like machine name and create time, but no filename. Maybe I'm just missing something obvious; I'm not at all familiar with SSIS. This is the first time I'm attempting to use it.
2010-05-05 13:37:39
Create a package level variable @FileName as Todd explained. On the properties tab of your ForEach iterator select the "Variable Mappings" tab. Select your @FileName variable from the list and map it to Index 0. On a file ForEach iterator this will contain the path and file name of the current file.
tchester
2010-05-05 18:34:45
Ok, I didn't know that index 0 mapped to path and file name. I also just realized that my derived column had defaulted to a length of 0 which was why my variable was not working correctly. It is now correctly pulling that data. Thank you!
2010-05-05 20:05:36