SSIS-动态将文件移动到具有匹配子字符串名称的文件夹 [英] SSIS - Dynamically moving Files to Folder with Matching Substring Name

问题描述

我正在使用foreach循环和文件系统任务将文件移动到特定文件夹(或至少尝试移动到).

I'm using a foreach loop and file system task to move files into specific folders (or trying to at least).

即

文件名可以是100000，并且需要转到文件夹1000 文件102000需要转到文件夹1020 文件103000需要进入文件夹1030 等等 等等

A file name can be 100000 and it needs to go to folder 1000 File 102000 needs to go to folder 1020 File 103000 need to go to folder 1030 etc etc

我正在努力将文件移动到正确的文件夹中.

I'm struggling with how to move the files to the correct folder.

我认为我可以在上级目录中使用一个变量，然后在foreach循环中使用文件名变量的子字符串

I thought I could use a variable with the upper level directory followed by a substring of the filename variable held in the foreach loop

例如

"D:\\Archive\\" + SUBSTRING(@[USER::Variable],1,4)

但这不起作用，并且我收到一条错误消息，指出不支持路径格式.

But that doesn't work and I get an error that the path format is not supported.

非常感谢您的帮助.

推荐答案

您的问题

首先，我认为错误是因为变量不仅包含文件名还包含完整路径，所以您必须使用类似的表达式:

Your issue

First of all,I think the error is because the variable contains the fullpath not only the filename so you have to use a similar expression:

"D:\\Archive\\" + LEFT(RIGHT( @[User::Variable] , FINDSTRING(REVERSE( @[User::Variable] ) , "\\", 1) - 1),4)

详细解决方案

第一种方法-使用脚本任务

尝试使用脚本任务来实现此目的，只需在脚本任务中选择变量作为ReadOnlyVariable.并使用类似的脚本(我用过Vb.net)

Try using a script task to achieve this, Just select your variable as a ReadOnlyVariable in the Script Task. and use a similar script (I Used Vb.net)

Public Sub Main()

    Dim strFile As String = Dts.Variables.Item("User::Variable").ToString

    Dim strFilename As String = IO.Path.GetFileName(strFile)


    'Create Directory
    If Not IO.Directory.Exists("D:\Archive\" & strFilename.Substring(0, 4)) Then
        IO.Directory.CreateDirectory("D:\Archive\" & strFilename.Substring(0, 4))
    End If

    'Copy File to destination
    IO.File.Copy(strFile, "D:\Archive\" & strFilename.Substring(0, 4) & "\" & strFilename)

    Dts.TaskResult = ScriptResults.Success
End Sub

第二种方法-使用文件系统任务

创建一个新变量@[User::DestinationPath]并将其属性设置为Evaluate As Expression = True，然后为其使用以下表达式:

Create a new Variable @[User::DestinationPath] and set it's property Evaluate As Expression = True, then use the following expression for it:

"D:\\Archive\\" + LEFT(RIGHT( @[User::Variable] , FINDSTRING(REVERSE( @[User::Variable] ) , "\\", 1) - 1),4)

可变屏幕截图

文件系统任务

这篇关于SSIS-动态将文件移动到具有匹配子字符串名称的文件夹的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！