SQL 如何在 SSIS 中为 Excel 文件设置动态文件路径？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15231486/
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
How do I set up a dynamic file path in SSIS for an Excel file?
提问by RJ.
The name of the file changes based on months. Every month you have a new file.
文件的名称根据月份而变化。每个月你都有一个新文件。
I:\Test\Data_201303.xlsx
How do I set up a connection manager that will work with variable file paths?
如何设置可以使用可变文件路径的连接管理器？
回答by criticalfix
Look for the "expression" property on the connection manager. That's where you set it to USER::VariableName.
在连接管理器上查找“表达式”属性。这就是您将其设置为 USER::VariableName 的地方。
More detail: if you "edit" the connection manager it will show you the edit window. That's not the one you want. Look at the "properties" window, which is where you will find the "Expressions" entry, in front of an empty box.
更多细节：如果您“编辑”连接管理器，它将显示编辑窗口。那不是你想要的。查看“属性”窗口，您可以在该窗口中找到“表达式”条目，位于一个空框前面。
Click on the empty box, and it will show you a button with three dots on it. Click on that button. This pulls up the "Property Expression Editor". There is a dropdown with properties like "ConnectionString" on the left. On the right, there is ANOTHER button with three dots. Click on that button to pull up the "Expression Builder". If you have declared any variables, you will find them listed in the upper left hand corner, e.g., as User::VariableName, and you can then drag them into the Expression box, where they will appear as @[User::VariableName].
单击空框，它会显示一个带有三个点的按钮。单击该按钮。这会调出“属性表达式编辑器”。左侧有一个包含“ConnectionString”等属性的下拉列表。在右侧，有一个带有三个点的 ANOTHER 按钮。单击该按钮以拉出“表达式生成器”。如果您已经声明了任何变量，您会发现它们列在左上角，例如，作为 User::VariableName，然后您可以将它们拖到 Expression 框中，它们将显示为 @[User::VariableName] .
Not obvious, but doable.
不明显，但可行。
回答by criticalfix
You need to set the expression for the ServerNameor ExcelFilePathproperty to modify ConnectionStringof Excel connection manager dynamically using an SSIS package variable.
您需要使用 SSIS 包变量为 Excel 连接管理器动态修改ServerName或ExcelFilePath属性设置表达式ConnectionString。
Here are some SO answers that deal with looping multiple Excel files :
以下是一些处理循环多个 Excel 文件的 SO 答案：
How to loop through Excel files and load them into a database using SSIS package?
如何使用 SSIS 包循环遍历 Excel 文件并将它们加载到数据库中？
How to import Excel files with different names and same schema into database?
