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.

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'?

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.

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'?

• ssis

• 1 Instead of appending .zip to the filename that contains .dat, you could use REPLACE() to replace .dat with .zip. @[User::FileDest] + REPLACE(@[User::Filename],".dat",".zip") – devlin carnate Commented Mar 28 at 19:38
• That worked. So simple except for when you don't know it. Thank you. – Don Commented Mar 28 at 20:09

1 Answer 1

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")