Sql Server SSIS 包平面文件目标文件名模式(日期、时间或类似)? [英] Sql Server SSIS package Flat File Destination file name pattern (date, time or similar)?

问题描述

我正在安排一个 SSIS 包，用于将数据导出到平面文件.

但我想生成带有一些日期信息的文件名，例如 foo_20140606.csv

有可能吗?

谢谢

解决方案

借助表达式，您可以使连接动态化.

从连接管理器窗格中选择您的平面文件连接.在属性窗格中，单击 Expression(...).然后从下拉列表中选择 ConnectionString 属性并在 Expression(...) 中放置您的表达式并对其进行评估.

表达式构建 -

对于天: (DT_STR,4,1252)DAY( DATEADD( "dd", -1, getdate() ))对于月份: (DT_STR,4,1252)MONTH( DATEADD( "dd", -1, getdate() ))对于年份: (DT_STR,4,1252)YEAR( DATEADD( "dd", -1, getdate() ))

示例表达式(您需要根据您的要求进行调整)-

"E:\\Backup\\EmployeeCount_"+(DT_STR,4,1252)DATEPART( "yyyy" , getdate() ) + RIGHT("0" + (DT_STR,4,1252)DATEPART("mm" , getdate() ), 2) + RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , getdate() ), 2) +".txt"

将 E:\Backup\EmployeeCount_20140627.txt 作为值.

请注意 - 您需要一个工作平面文件连接，因此首先创建平面文件连接，其 connectionString 属性将被表达式自动替换.

您也可以按照这些分步文章进行操作.

ssis 动态命名目的地

SSIS 动态平面文件连接

I'm scheduling a SSIS package for exporting data to flat file.

But i want to generate file names with some date information, such as foo_20140606.csv

Is it possible?

Thanks

解决方案

With the help of expressions you can make connection dynamic.

Select your flat file connection from Connection Managers pane. In Properties pane, click on Expression(...). Then choose ConnectionString Property from drop down list and in Expression(...) put your expression and evaluate it.

Expression build -

For day : (DT_STR,4,1252)DAY( DATEADD( "dd", -1, getdate() ))
For month: (DT_STR,4,1252)MONTH( DATEADD( "dd", -1, getdate() ))
For Year:  (DT_STR,4,1252)YEAR( DATEADD( "dd", -1, getdate() ))

Example expression(you need to tweak as per your requirement) -

"E:\\Backup\\EmployeeCount_"+(DT_STR,4,1252)DATEPART( "yyyy" , getdate() ) + RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , getdate() ), 2) + RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , getdate() ), 2) +".txt" 

which is giving E:\Backup\EmployeeCount_20140627.txt as value.

Please note - You need a working flat file connection so first create flat file connection whose connectionString property is then going to be replaced automatically by expression.

You can follow these step by step articles as well.

ssis dynamically naming destination

SSIS Dynamic Flat File Connection

这篇关于Sql Server SSIS 包平面文件目标文件名模式(日期、时间或类似)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！