处理文件后如何将文件移动到存档文件夹? [英] How do I move files to an archive folder after the files have been processed?

问题描述

在完成一些数据流任务项目后，我尝试将一组文件从源路径归档到归档路径.在 Foreach 循环容器内部，我有:脚本任务 -> 数据流任务 -> 执行 SQL 任务 -> 文件系统任务

I am trying to archive a set of files from the source path to an archive path once I have completed doing some Data Flow Task items. Inside of a Foreach Loop Container I have: Script Task -> Data Flow Task -> Execute SQL Task -> File System Task

我有一个用户变量设置为 Foreach 循环容器中集合设置下的名称和扩展名"项.该变量称为fileName"，用于脚本任务(用于从文件中解析信息并执行执行 SQL 任务)以及平面文件连接管理器中的数据流任务，后者依次由平面文件源元素.然后我解析文件并将数据插入到数据库中.一切正常，直到我到达文件系统任务 (FST).

I have a User variable set to the "Name and extension" item under the Collection settings in the Foreach Loop Container. The variable is called "fileName" and is used in both the Script Task (used to parse out info from file and perform the Execute SQL Task) as well as in the Data Flow Task in the Flat File Connection Manager which in turn is used by the Flat File Source element. I then parse the file and insert the data into a database. All works as it should until I get to the File System Task (FST).

我想要的是在插入完成后将文件移动到存档文件夹.使用多个在线链接(此处、这里 和 这里) 我添加了不同的变量，这些变量要么是硬编码的，要么是从按摩其他变量.无论如何，在 FST 上我会收到诸如路径中的无效字符"或未知路径"之类的错误.如果我尝试修改上面链接的示例以适应我的文件系统结构，我现在在数据流任务中的平面文件源步骤中出现错误，指出它找不到指定的文件.这是因为它a) 找不到文件路径，因为没有给出文件路径，只有 filname.extb) 无法解析包含源文件完整路径的变量(@FullSourcePathFileName 其值设置为 @[User::SourcePath] + @[用户::文件名])

What I would like is to have the file moved to an archive folder once its insert is completed. Using several links online (here, here and here) I have added different variables that are either hard-coded or derived from massaging other variables. In any case what happens is that on the FST I get errors like 'invalid characters in path' or 'unknown path'. If I try to massage the examples linked above to fit my filesystem structure I now get an error on the Flat File Source step in the Data Flow Task stating it cannot find the file specified. This is caused because it a) cannot find the path to the file because no file path is give, just the filname.ext b) cannot parse the variable that contains the full path to the source file (@FullSourcePathFileName which has its value set to @[User::SourcePath] + @[User::fileName])

我已经测试了其他各种修改，包括完全按照我发布的第一个示例执行的操作(但是那个实际上并没有执行任何数据流任务，所以我只是添加了一个没有目标的平面文件源步骤)并收到了相同的错误集.我在这里不知所措，想就如何解决这个问题提供任何意见.

I have tested other various modifications including doing exactly what is in the first example I posted (however that one does not actually do anything the Data Flow Task so I just added a Flat File Source step with no destination) and received the same set of errors. I am at a loss here and would like any input on how to solve this issue.

似乎它在 FullArchivePathFileName 上一直失败 - 即使我将它设置为True"，它也从不评估表达式.仍然对为什么不对其进行评估感到困惑.因此，我将其表达式设置为与 FullSourcePathFileName 相同，并验证了 EvaluateAsExpression 标志设置为 True.它仍然不评估这个变量.FullSourcePathFileName 变量正在被评估.

Seems that it keeps failing on the FullArchivePathFileName - it never evaluates the expression even though I have it set to 'True'. Still confused as to why it is not evaluating it. So I made its expression the same as FullSourcePathFileName and verified the EvaluateAsExpression flag is set to True. It still does not evaluate this variable.The FullSourcePathFileName variable is being evaluated just fine.

推荐答案

在 SSIS 2005 中创建的以下示例从给定文件夹读取 CSV 文件并将数据插入 SQL桌子.将数据导入 SQL 后，然后使用文件系统任务将文件移动到存档文件夹.

Following example created in SSIS 2005 reads CSV files from a given folder and inserts data into an SQL table. After importing data into SQL, the files are then moved to an Archive folder using File System Task.

分步过程:

1. 在路径 C:\temp 中创建一个名为 Archive 的文件夹.创建两个名为 File_1.csv 和 File_2.csv 的 CSV 文件，并用数据填充它们.将存档文件夹留空.请参阅屏幕截图 #1 - #4.

在 SSIS 包上创建 5 个变量，如屏幕截图 #5 所示.将变量 RootFolder 设置为值 C:\temp\.使用值 *.csv 设置变量 FilePattern.

On the SSIS package create 5 variables as shown in screenshot #5. Set the variable RootFolder with value C:\temp\. Set the variable FilePattern with value *.csv.

选择变量 FilePath 并按 F4 打开属性，将属性 EvaluateAsExpression 更改为 True 并将 Expression 属性设置为值 @[User::RootFolder] + @[User::FileName] 如屏幕截图 #6 所示.

Select the variable FilePath and press F4 to open properties, change the property EvaluateAsExpression to True and set the Expression property with value @[User::RootFolder] + @[User::FileName] as shown in screenshot #6.

选择变量 ArchiveFolder 并按 F4 打开属性，将属性 EvaluateAsExpression 更改为 True 并将 Expression 属性设置为值 @[User::RootFolder] + "Archive\\" 如屏幕截图 #7 所示.

Select the variable ArchiveFolder and press F4 to open properties, change the property EvaluateAsExpression to True and set the Expression property with value @[User::RootFolder] + "Archive\\" as shown in screenshot #7.

在 SSIS 包的连接管理器上，创建一个名为 CSV 的新平面文件连接.请参阅屏幕截图 #8.如屏幕截图 #9 - #13 所示配置平面文件连接.此外，创建名为 SQLServer 的 OLE DB 连接以连接到 SQL Server 数据库.创建连接后，它应该如屏幕截图 #14 所示.

On the SSIS package's connection manager, create a New Flat File Connection named CSV. Refer screenshot #8. Configure the flat file connection as shown in screenshots #9 - #13. Also, create an OLE DB connection named SQLServer to connect to the SQL Server database. After connections are created, it should look like as shown in screenshot #14.

右键单击平面文件连接 CSV 并选择属性并使用省略号按钮使用值 @[User::FilePath] 配置 ConnectionString 表达式，如屏幕截图 #15 - #16.

Right-click on flat file connection CSV and select properties and configure the ConnectionString Expression with value @[User::FilePath] using the Ellipsis button as shown in screenshots #15 - #16.

使用 SQL 脚本 部分下提供的脚本在 SQL Server 中创建一个名为 dbo.Items 的表.CSV 文件数据将插入到此表中.

Create a table named dbo.Items in the SQL Server using the scripts provided under SQL Scripts section. The CSV files data will be inserted into this table.

在控制流选项卡上，放置一个Foreach Loop container、Data Flow Task 和File System Task，如截图所示#17.

On the Control flow tab, place a Foreach Loop container, Data Flow Task and File System Task as shown in screenshot #17.

配置 Foreach 循环容器，如屏幕截图 #18 - #19 所示.

Configure the Foreach Loop container as shown in screenshots #18 - #19.

在数据流任务中，放置平面文件源、派生列转换和 OLE DB 目标，如屏幕截图 #20 所示.

Inside the Data Flow Task, place a Flat File Source, Derived Column transformation and an OLE DB Destination as shown in screenshot #20.

配置平面文件源，如屏幕截图 #21 和 #22 所示.这将从 CSV 文件中读取数据.

Configure the Flat File Source as shown in screenshots #21 and #22. This will read the data from CSV files.

配置派生列转换，如屏幕截图 #23 所示.这用于使用同名变量创建 FilePath 列值.

Configure the Derived Column transformation as shown in screenshot #23. This is used to create the FilePath column value using the variable of the same name.

配置 OLE DB 目标，如屏幕截图 #24 和 @25 中所示.这会将数据插入到 SQL 表中.

Configure the OLE DB Destination as shown in screenshots #24 and @25. This will insert the data into the SQL table.

在控制流"选项卡上，配置文件系统任务，如屏幕截图 #26 所示.请注意，移动文件操作时，DestinationVariable只能指定为目录，不能指定为完整文件路径.如果指定文件路径，则会收到错误消息 [File System Task] Error: An error occurred with 以下错误消息:Could not find a part of the path.".

On the Control Flow tab, configure the File System Task as shown in screenshot #26. Please note that while Move file operation, the DestinationVariable can only be specified as a directory and it cannot be specified as full file Path. If you specify the file path, you will get the error message [File System Task] Error: An error occurred with the following error message: "Could not find a part of the path.".

屏幕截图 #28 显示在包执行之前表中没有数据.

Screenshot #28 shows that there is no data in the table before the package execution.

屏幕截图 #29 和 #30 显示了控制流和数据流选项卡内的包执行情况.

Screenshots #29 and #30 show package executions inside Control Flow and Data Flow tabs.

屏幕截图 #31 和 #32 显示文件已移至存档文件夹.

Screenshots #31 and #32 show that the files have been moved to the Archive folder.

屏幕截图 #33 显示了包执行后表中的数据.

Screenshot #33 shows the data in the table after the package execution.

在文件系统任务中，属性 OverwriteDestination 被设置为 False(这是默认值).如果您将同名文件移动到存档文件夹，您将收到错误 <代码>[文件系统任务] 错误:发生错误并显示以下错误消息:当该文件已存在时，无法创建文件. ". 显示在屏幕截图 #34 中.为避免这种情况，请将 OverwriteDestination 设置为 True 或其他选项是重命名文件并将其复制到存档文件夹，然后将其删除.

On the File System Task, the property OverwriteDestination was set to False (this is the default value). If you are moving files of same names to the Archive folder, you will get the error [File System Task] Error: An error occurred with the following error message: "Cannot create a file when that file already exists. ". shown in screenshot #34. To avoid this set the OverwriteDestination to True or the other option is to rename the files and copy it to Archive folder and then delete them.

希望有所帮助.

SQL 脚本:

CREATE TABLE [dbo].[Items](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ItemNumber] [varchar](30) NOT NULL,
    [Price] [numeric](18, 2) NOT NULL,
    [FilePath] [varchar](255) NOT NULL,
CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO

屏幕截图 #1:

屏幕截图 #2:

屏幕截图 #3:

屏幕截图 #4:

屏幕截图 #5:

屏幕截图 #6:

屏幕截图 #7:

截图 #8:

屏幕截图 #9:

屏幕截图 #10:

屏幕截图 #11:

屏幕截图 #12:

屏幕截图 #13:

屏幕截图 #14:

屏幕截图 #15:

屏幕截图 #16:

屏幕截图 #17:

屏幕截图 #18:

屏幕截图 #19:

屏幕截图 #20:

屏幕截图 #21:

屏幕截图 #22:

屏幕截图 #23:

屏幕截图 #24:

屏幕截图 #25:

屏幕截图 #26:

屏幕截图 #27:

屏幕截图 #28:

屏幕截图 #29:

屏幕截图 #30:

屏幕截图 #31:

屏幕截图 #32:

屏幕截图 #33:

屏幕截图 #34:

这篇关于处理文件后如何将文件移动到存档文件夹?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！