如何从 SSIS Excel 文件名解析日期 [英] How to parse a date from an SSIS Excel filename

问题描述

我想使用 foreach 容器来遍历匹配以下内容的文件夹:Filename_MMYYYY.xls".这很容易做到；但我似乎无法找到一种方法来解析文件名中的 MMYYYY 并将其添加到一个变量(或其他东西)中，我可以将其用作 DimDate 表的查找字段.似乎可以使用平面文件数据源，但不能使用 excel 连接.我使用的是 Visual Studio 2005.请帮忙！

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.

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.

首先，创建一个新变量 - @FileDate - 作为 DateTime 类型.转到其属性窗口 (F4)，并将 EvaluateAsExpression 属性设置为 True.编辑表达式，然后输入如下内容(您可能需要调整):

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")

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

现在，如果您想获取该日期值并在您的数据流中使用它，您可以直接在派生列转换中使用它，或者在您的查找 SQL 语句的表达式中，或任何地方.

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.

这篇关于如何从 SSIS Excel 文件名解析日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！