How to Remove Extension from a SSIS package Variable

SSIS and Import/Export
1

Hi experts,
So I have a variable named FileName in my SSIS package. The value is MyFileName.csv
(I need to preserve that value)
But I want to derive another variable which would contain MyFileName

I need a vbscript script to save the value, without the.csv, into another var name. And it needs to be in VBscript because I don't know C#.

Fairly easy to do, I'm sure. Thanks

2

how/where will you use that other variable after you strip off the extension?

3

You can cheat in SQL

declare @FileName varchar(50) = 'MyFileName.csv'

select Reverse(Right(Reverse(@FileName), Len(@FileName) - CharIndex( '.', Reverse(@FileName))))
4

Thanks mike012. But my variable is a SSIS package variable.

5

Thanks, yosiasz. I'm using this SSIS variable in my Task
which renames files. Mostly, I need the var to contain MyFileName.csv. But for the renaming, I need to
derive another variable that doesn't contain the ".csv"

6

mike01 gave me an idea.
select Reverse(Right(Reverse(@FileName), Len(@FileName) - CharIndex( '.', Reverse(@FileName))))

But, I can't use a TSQL Task because that requires a Connection.

I may be wrong, but I think this can be done with an expression. In the new variable
FileNameWithoutExt have an expression
like select Reverse(Right(Reverse(@FileName), Len(@FileName) - CharIndex( '.', Reverse(@FileName))))

but I don't know how to code the expression to populate the new variable.

7

rename from MyFileName to what?

8

So I tried this expression in my new variable

Reverse(Right(Reverse(@[User::vFileName]), Len(@[User::vFileName]) - CharIndex( '.', Reverse(@[User::vFileName]))))
Get a Parsing error:

image

9

:yawning_face:

So after modifying it what do you pass it to?

10

I'm using the new variable as the Destination in a Rename File task. Just need to get that .csv out of the name before I do other things with it. Can you help with the expression I posted? And Thanks.

11

I will try to get you to the best solution. so if you are going to rename it obviously you will give it something more in the file name . what is it, give us the whole story please.

12

The FileName variable is Details.csv Of course, I'm looping thru several files so the file name is always changing.

In this example, the destination file name, in the Rename File tasks needs to be Details20200706.csv

(Currently the destination file name is Details.csv.20200706.csv)

The Rename task is the last in the package. Everything else is working fine. Just need to get the .csv ojut of the FileName and i thought a variable expression might be the best way. I think the code mike01 provided give s us a hint but it is TSQL code, does not work in an expression in SSIS variable. Thanks.

13

I created a script task. I also have 2 variables (FileName and NewFileName). The first is passed in as ReadOnly and the second is Read/Write. The code is below:

Dts.Variables("NewFileName").Value = System.IO.Path.GetFileNameWithoutExtension(Dts.Variables("Filename").Value.ToString())

2 Likes
14

hey guys - yosiasz amd mike01 - I want to thank you for the tips you provided. Everything is working well now.
The package has these tasks:
A For Each loop which loops thru the .csv files
Data Flow task imports into a SQL Table (using a Dynamic Flat File connection)
Task to Move file to Processed folder
Script Task to Remove the File Extension
Task to Rename the file to .....CurrentDate.csv
SQl Task to Insert a Row into Processed table

Thanks very much for your ideas.
John