文件处理后如何将文件移动到存档文件夹?

发布于 2024-11-20 00:00:01 字数 1320 浏览 2 评论 0原文

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

我将用户变量设置为 Foreach 循环容器中集合设置下的“名称和扩展名”项。该变量称为“fileName”,在脚本任务(用于从文件中解析信息并执行执行 SQL 任务)以及平面文件连接管理器中的数据流任务中使用,而平面文件连接管理器又由平面文件源元素。然后我解析该文件并将数据插入数据库。在我进入文件系统任务 (FST) 之前,一切都会按预期进行。

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

我已经测试了其他各种修改,包括完全执行我发布的第一个示例中的操作(但是,该修改实际上并未对数据流任务执行任何操作,因此我只是添加了平面文件源步骤没有目的地)并收到相同的错误集。我在这里不知所措,希望获得有关如何解决此问题的任何意见。

编辑: 似乎它在 FullArchivePathFileName 上一直失败 - 即使我将其设置为“True”,它也不会计算表达式。仍然困惑为什么它不评估它。因此,我使其表达式与 FullSourcePathFileName 相同,并验证 EvaluateAsExpression 标志设置为 True。它仍然不会评估该变量。FullSourcePathFileName 变量正在被很好地评估。

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

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

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.

EDIT:
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.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

少年亿悲伤 2024-11-27 00:00:01

以下在 SSIS 2005 中创建的示例从给定文件夹读取 CSV 文件并将数据插入到 SQL 表中。将数据导入 SQL 后,使用文件系统任务将文件移至存档文件夹。

分步过程:

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

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

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

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

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

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

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

  8. 在“控制流”选项卡上,放置一个 Foreach 循环容器数据流任务文件系统任务,如屏幕截图所示 #17

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

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

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

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

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

  14. 在“控制流”选项卡上,配置文件系统任务,如屏幕截图 #26 所示。请注意,在移动文件操作时,DestinationVariable只能指定为目录,不能指定为完整文件路径。如果指定文件路径,您将收到错误消息[文件系统任务]错误:发生错误,并显示以下错误消息:“找不到路径的一部分。”。

  15. 截图#28显示包执行前表中没有数据。

  16. 屏幕截图 #29 和 #30 显示“控制流”和“数据流”选项卡内的包执行。

  17. 屏幕截图 #31 和 #32 显示文件已移至 Archive 文件夹。

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

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

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:

1

屏幕截图#2:

2

屏幕截图 #3:

3

屏幕截图 #4:

4

屏幕截图 #5:

5

屏幕截图 # 6:

6

屏幕截图 #7:

7

屏幕截图 #8:

8

屏幕截图 # 9:

9

屏幕截图 #10:

10

屏幕截图 #11:

11

屏幕截图#12:

12

屏幕截图 #13:

13

屏幕截图 #14:

“14”

屏幕截图 #15:

15

屏幕截图 #16:

16

屏幕截图 #17:

17

屏幕截图 #18:

18

屏幕截图 #19:< /strong>

19

屏幕截图 #20:

20

屏幕截图 #21:

21

屏幕截图 #22:

22

屏幕截图#23:

23

屏幕截图 #24:

24

屏幕截图 #25:

“25”

屏幕截图 #26:

26

屏幕截图 #27:

27

屏幕截图 #28:

28

屏幕截图 #29:

29

屏幕截图 #30:

30

屏幕截图#31:

31

屏幕截图 #32:

32

屏幕截图 #33:

“33”

屏幕截图#34:

34

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.

Step-by-step process:

  1. Create a folder named Archive within path C:\temp. Create two CSV files named File_1.csv and File_2.csv and populate them with data. Leave the Archive folder empty. Refer screenshots #1 - #4.

  2. 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.

  3. 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.

  4. 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.

  5. 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.

  6. 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.

  7. 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.

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

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

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

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

  12. 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.

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

  14. 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.".

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

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

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

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

  19. 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 Scripts:

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

Screenshot #1:

1

Screenshot #2:

2

Screenshot #3:

3

Screenshot #4:

4

Screenshot #5:

5

Screenshot #6:

6

Screenshot #7:

7

Screenshot #8:

8

Screenshot #9:

9

Screenshot #10:

10

Screenshot #11:

11

Screenshot #12:

12

Screenshot #13:

13

Screenshot #14:

14

Screenshot #15:

15

Screenshot #16:

16

Screenshot #17:

17

Screenshot #18:

18

Screenshot #19:

19

Screenshot #20:

20

Screenshot #21:

21

Screenshot #22:

22

Screenshot #23:

23

Screenshot #24:

24

Screenshot #25:

25

Screenshot #26:

26

Screenshot #27:

27

Screenshot #28:

28

Screenshot #29:

29

Screenshot #30:

30

Screenshot #31:

31

Screenshot #32:

32

Screenshot #33:

33

Screenshot #34:

34

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文