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!
我想使用foreach容器遍历一个文件夹，该文件夹匹配如下内容:“filename_mmyyy .xls”。这很简单;但我似乎找不到一种方法来解析文件名中的MMYYYY，并将它添加到一个变量(或其他东西)中，我可以将其用作DimDate表的查找字段。可以使用平面文件数据源，但不能使用excel连接。我使用的是Visual Studio 2005。请帮助!
1 个解决方案
#1
4
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.
我是否正确地理解您想要使用您的文件名，解构它，并从中得到一个日期类型的变量?如果是，那么您需要从Foreach循环获得的文件名变量开始——我将该变量命名为@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):
首先，创建一个新的变量—@FileDate—作为一个DateTime类型。转到它的properties窗口(F4)，并将EvaluateAsExpression属性设为True。编辑表达式，然后输入如下内容(可能需要调整):
(DT_DBTIMESTAMP)(SUBSTRING(@FileName, 12, 4) + "-" + SUBSTRING(@FileName, 10, 2) + "-01")
(DT_DBTIMESTAMP)(子字符串(@FileName, 12, 4) + "-" -" +子字符串(@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.
现在，如果您想要在数据流中使用该日期值并使用它，您可以直接在派生的列转换中使用它，或者在查询SQL语句或其他任何地方使用它。
#1
4
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.
我是否正确地理解您想要使用您的文件名，解构它，并从中得到一个日期类型的变量?如果是，那么您需要从Foreach循环获得的文件名变量开始——我将该变量命名为@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):
首先，创建一个新的变量—@FileDate—作为一个DateTime类型。转到它的properties窗口(F4)，并将EvaluateAsExpression属性设为True。编辑表达式，然后输入如下内容(可能需要调整):
(DT_DBTIMESTAMP)(SUBSTRING(@FileName, 12, 4) + "-" + SUBSTRING(@FileName, 10, 2) + "-01")
(DT_DBTIMESTAMP)(子字符串(@FileName, 12, 4) + "-" -" +子字符串(@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.
现在，如果您想要在数据流中使用该日期值并使用它，您可以直接在派生的列转换中使用它，或者在查询SQL语句或其他任何地方使用它。