SQL SSIS 动态 Excel 目标文件名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3283882/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SSIS Dynamic Excel Destination File Name
提问by sql rookie
How can I configure a dataflow task that takes data from a MS SQL Server 2008 datasource and puts it in an Excel file where the filename looks like 'date filename'.xls?
如何配置从 MS SQL Server 2008 数据源获取数据并将其放入文件名类似于“日期文件名”.xls 的 Excel 文件的数据流任务？
回答by HLGEM
Excel is the biggest pain to deal with in SSIS. Usually I store a template file that just has the column headers and nothing else. I start with a task to copy the template file to the processing directory. You can use variables to create the file name in an expression at this point. Alternatively, you can create the file in the dataflow and then rename the file in a step after the data flow. With text files, I have dynamically created the connection in an expression, but Excel seems to be funny about that.
Excel 是在 SSIS 中处理的最大痛点。通常我存储一个模板文件，它只有列标题，没有其他内容。我从一个将模板文件复制到处理目录的任务开始。此时您可以使用变量在表达式中创建文件名。或者，您可以在数据流中创建文件，然后在数据流之后的一个步骤中重命名该文件。对于文本文件，我在表达式中动态创建了连接，但 Excel 似乎对此很有趣。
回答by Arif
Provided that your column definition don't change.... you can go to
前提是您的列定义不更改....您可以转到
- Right Click on Excel Connection Manager
- Expression
- Select connectionstring
- bulid expression (for Example : (DT_WSTR, 50) GETDATE() + @[user::FileName] +".xlsx")
- 右键单击 Excel 连接管理器
- 表达
- 选择连接字符串
- bulid 表达式（例如：(DT_WSTR, 50) GETDATE() + @[user::FileName] +".xlsx"）
回答by Vijayanand Settin
Select the properties for Excel Connection Manager instance, Click on the ellipsis for 'Expressions 'property and set an expression for 'ExcelFilePath' to a variable with a valid path to an excel file, this takes cares of the connection string.
选择 Excel 连接管理器实例的属性，单击“表达式”属性的省略号并将“ExcelFilePath”的表达式设置为具有 Excel 文件有效路径的变量，这将处理连接字符串。
You do need a variable valid excel file at the design time, otherwise connection manager does not work, you can overwrite it at run time using a script task to point to the excel file that does not exist at design time.
您在设计时确实需要一个变量有效的 excel 文件，否则连接管理器不起作用，您可以在运行时使用脚本任务覆盖它以指向设计时不存在的 excel 文件。
回答by Weihui Guo
HLGEM's answer certainly helps. As @sql-rookie requested, I'll provide a sample about how to copy the excel file to a new file named with current date. Hope this will help others with the same question in the future.
HLGEM 的回答肯定有帮助。正如@sql-rookie 所要求的，我将提供一个关于如何将 excel 文件复制到以当前日期命名的新文件的示例。希望这能帮助其他人在未来遇到同样的问题。
Basically you just need to add an additional File System Taskafter the Data Flow Task. And use a variablefor the Destination File Path: "\\\\file\\"+SUBSTRING((DT_STR,30, 1252) GETDATE(), 1, 10) +".xlsx"
基本上你只需要File System Task在Data Flow Task. 并使用 avariable作为目标文件路径："\\\\file\\"+SUBSTRING((DT_STR,30, 1252) GETDATE(), 1, 10) +".xlsx"
