ssis

SSIS - replace file extension in filename


I have an ssis package that creates 7 .dat files. Each file needs to be compressed in a zip file with an extension of Filename.zip. It cannot be Filename.dat.zip. The first step to create the .dat file uses this expression which has the file path in a variable called 'FileDest' and then adds the filename with YYYYQQ datetime and extension. The SourceDate variable is used in the calculation of the YYYYQQ.

@[User::FileDest]+ "Filename_"+(DT_WSTR,4) YEAR( @[User::SourceDate] ) +"Q" +  RIGHT("00" + (DT_WSTR,1) DATEPART("QQ", @[User::SourceDate]  ),1 ) + "_"+ (DT_WSTR,4)DATEPART("yyyy",GetDate()) + 
 RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) + 
 RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + "_" +
RIGHT("0" + (DT_WSTR,2)DATEPART("hh",GetDate()),2) +
RIGHT("0" + (DT_WSTR,2)DATEPART("mi",GetDate()),2) +
RIGHT("0" + (DT_WSTR,2)DATEPART("ss",GetDate()),2) +".dat"

Then a ForEachLoop container captures the filename in a variable and zip each file. The filename variable contains the name and extension. If I do name only it will not find the file without including the extension.

enter image description here

The expression to save the zip file is @[User::FileDest] + @[User::Filename] + ".zip". This produces a file with filename.dat.zip which I cannot use. How do I remove the '.dat'?

enter image description here


Solution

  • Instead of appending ".zip" to the filename that contains ".dat", you could use REPLACE() to replace ".dat" with ".zip".

    So, your expression would be:

    @[User::FileDest] + REPLACE(@[User::Filename],".dat",".zip")