admin 管理员组

文章数量: 1086019

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
Share Improve this question asked Mar 28 at 18:43 DonDon 2241 silver badge7 bronze badges 2
  • 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
Add a comment  | 

1 Answer 1

Reset to default 0

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

本文标签： SSISreplace file extension in filenameStack Overflow

版权声明：本文标题：SSIS - replace file extension in filename - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.roclinux.cn/p/1744018484a2519361.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。