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?
如何配置一个dataflow任务，该任务从MS SQL Server 2008数据源获取数据，并将其放入一个Excel文件中，文件名看起来像'date filename'.xls ' ?
3 个解决方案
#1
2
Provided that your column definition don't change.... you can go to
提供你的列定义不改变....你可以去
- Right Click on Excel Connection Manager
- 右键单击Excel连接管理器
- Expression
- 表达式
- Select connectionstring
- 选择connectionstring
- bulid expression (for Example : (DT_WSTR, 50) GETDATE() + @[user::FileName] +".xlsx")
- bulid表达式(例如:(DT_WSTR, 50) GETDATE() + @[user:::FileName] +".xlsx")
#2
1
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文件。
#3
1
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中最头疼的问题。通常，我存储的模板文件只有列标题，没有其他内容。我从将模板文件复制到处理目录的任务开始。此时可以使用变量在表达式中创建文件名。或者，您可以在dataflow中创建文件，然后在数据流之后的步骤中重命名该文件。在文本文件中，我动态地创建了一个表达式中的连接，但是Excel似乎很有趣。
#1
2
Provided that your column definition don't change.... you can go to
提供你的列定义不改变....你可以去
- Right Click on Excel Connection Manager
- 右键单击Excel连接管理器
- Expression
- 表达式
- Select connectionstring
- 选择connectionstring
- bulid expression (for Example : (DT_WSTR, 50) GETDATE() + @[user::FileName] +".xlsx")
- bulid表达式(例如:(DT_WSTR, 50) GETDATE() + @[user:::FileName] +".xlsx")
#2
1
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文件。
#3
1
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中最头疼的问题。通常，我存储的模板文件只有列标题，没有其他内容。我从将模板文件复制到处理目录的任务开始。此时可以使用变量在表达式中创建文件名。或者，您可以在dataflow中创建文件，然后在数据流之后的步骤中重命名该文件。在文本文件中，我动态地创建了一个表达式中的连接，但是Excel似乎很有趣。