SSIS - Dynamically moving Files to Folder with Matching Substring Name

Question

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

i.e.

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.

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

e.g.

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

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

Any help is greatly appreciated, thanks.

before asking your first question is it better to read the Tour Page to learn more on asking question and other helpful informations, also to get your first informed badge — Hadi
– Hadi, Commented Jun 16, 2017 at 14:33
Try casting your variable to a string: SUBSTRING((DT_WSTR, 6) @[USER::Variable,1,4) — Aaron Dietz
– Aaron Dietz, Commented Jun 16, 2017 at 14:34
Are you moving file using a File System Task or Script Task or something else? — Hadi
– Hadi, Commented Jun 16, 2017 at 14:46

Hadi · Accepted Answer · 2017-06-16 15:10:45Z

3

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)

Detailed Solutions

First Method - Using script Task

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

Second Method - Using File System Task

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)

Variable Screenshot

File System Task

edited Jun 16, 2017 at 15:10

answered Jun 16, 2017 at 14:56

Hadi

37.5k13 gold badges73 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nick Lea-Trengrouse Over a year ago

Hi,Thankyou so much, i used the scripting task method but I'm getting a runtime error

Hadi Over a year ago

Check that you wrote the variable name correctlu, also note that it is case sensitive

Collectives™ on Stack Overflow

SSIS - Dynamically moving Files to Folder with Matching Substring Name

1 Answer 1

Your issue

Detailed Solutions

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Your issue

Detailed Solutions

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related