SSIS File System and For Each Loop Container

Question

I have set of files which arrive in the below fashion

mmyy_dgmaker.csv mmyy_xlsx

I have attached the photo of the files

In my SSIS task I have taken for each loop container

In expression I have given file directory as varfiledirectory
variable as varfilename Inside for each loop I have taken file system task editor Source variable is : var source Destination is var _ destination for var destination I have used below expression

Now the problem is it is replacing for all the file in the folder with 15 characters from right which should not be the case need help so that it should take only those files specifically has more length and the rest should be imported as it is.

Thanks.

piotreks-uw-edu · Accepted Answer · 2023-05-05 11:03:51Z

0

If I have understood you correctly the script should look like:

@[User::Var_Destination_Path] + 
    (LEN(@[User::Var_File_Name]) > 15 ? 
        LEFT(@[User::Var_File_Name], LEN(@[User::Var_File_Name]) - 15) + ".csv" : 
        @[User::Var_File_Name])

This expression evaluates whether the filename has more than 15 characters. If the condition is met, it removes the final 15 characters from the filename and adds the "*.csv" extension. If the filename has 15 or fewer characters, it remains unchanged.

Instead of replacing with empty characters I used LEFT function.

edited May 5, 2023 at 11:03

answered May 5, 2023 at 10:39

piotreks-uw-edu

1245 bronze badges

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

1 Comment

user3073172 Over a year ago

I used the above expression in my task I'm getting error as The length -15 specified for function "LEFT" is negative and is not valid. The length parameter must be positive. code 0xC00470E7

Collectives™ on Stack Overflow

SSIS File System and For Each Loop Container

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related