重命名文件源 [英] Renaming a file source

问题描述

我一直在从平面文件源开发SSIS包.该文件每天发送一次，文件名具有如下所示的日期时间指示:

I've been developing an SSIS package from a flat file source. The file comes daily and the file name has datetime indication like this:

Filename_20190509042908.txt

Filename_20190509042908.txt

我想知道如何才能通过日期部分；我希望程序包动态读取文件，但是它应该不带最后6位数字通过，我只是不需要最后6位数字，因为它不一致.

I was wondering how I can pass until the date part; I want the package to read the file dynamically, but it should pass without the last 6 digits I just don't need the last 6 digit numbers as it is not consistent.

我想传递Filename_20190509.txt

I want to pass Filename_20190509.txt

我已经弄清楚了如何删除日期部分之前的文件名.因此，我无法通过忽略文件扩展名前的后6位数字来使程序包动态读取文件名.

I have figured out how to take the filename until date removing the time part. Hence, I've trouble to let the package read the file name dynamically by ignoring the last 6 digits before file extension.

有人可以帮我吗?

推荐答案

从完整文件路径中删除时间部分

假定完整文件路径存储在名为@[User::FilePath]

您必须添加一个字符串类型的变量(例如:@[User::Filename])，在数据流任务之前添加一个Expression Task 并使用以下表达式:

You have to add a variable of type string (example: @[User::Filename]), Before the data flow task add an Expression Task and use the following expression:

@[User::Filename] = SUBSTRING(@[User::FilePath], 1, LEN(@[User::FilePath]) - 
FINDSTRING(REVERSE(@[User::FilePath]), "\\", 1)) + "\\" + 
LEFT(TOKEN(@[User::FilePath],"\\",TOKENCOUNT(@[User::FilePath],"\\")),
LEN(TOKEN(@[User::FilePath],"\\",TOKENCOUNT(@[User::FilePath],"\\"))) - 10) + ".txt"

示例:

如果@[User::FilePath]的值为

C:\ New Folder \ 1 \ Filename_20190503001221.txt

C:\New Folder\1\Filename_20190503001221.txt

然后@[User::Filename]将是:

C:\ New Folder \ 1 \ Filename_20190503.txt

C:\New Folder\1\Filename_20190503.txt

---

如果您只有文件名作为

---

If You have only the file name as

文件名_20190503001221.txt

filename_20190503001221.txt

并且文件夹路径存储在另一个变量中，只需使用以下表达式:

and the folder path is stored in another variable, just use the following expression:

@[User::Filename] = @[User::Folderpath] + "\\" + 
LEFT(TOKEN(@[User::FilePath],"\\",TOKENCOUNT(@[User::FilePath],"\\")),
LEN(TOKEN(@[User::FilePath],"\\",TOKENCOUNT(@[User::FilePath],"\\"))) - 10) + ".txt"

从变量读取文件源

单击用于读取源文件的平面文件连接管理器，按 F4 以显示属性"选项卡，单击表达式"属性并将以下表达式分配给connectionstring属性:

Read File Source from Variable

Click on the flat file connection manager used to read the source file, press F4 to show the properties tab, Click on the expression property and assign the following expression to the connectionstring property:

@[User::Filename]

现在将数据流任务 Delay validation属性值更改为True.

Now change the Data Flow Task Delay validation property value to True.

• 动态平面SQL Server Integration Services中的文件连接

这篇关于重命名文件源的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！