SSIS中的文件夹的多个平面文件输出
我正在使用SSIS2017，而我正在做的一部分涉及运行几个（30ish）SQL脚本，以将其输出到同一文件夹中。我的问题是，要这样做，我必须创建30个新文件连接，还是有办法定义我希望所有输出的文件夹并将它们保存在那里？
我只是真正考虑保留一个整洁的连接管理器选项卡。如果有一种比30多岁的文件连接更有效的方法吗？
I am using SSIS2017 and part of what I am doing involves running several (30ish) SQL scripts to be output into flat files into the same folder. My question is, to do this do I have to create 30 New File Connections or is there a way to define the folder I want all the outputs to go to, and have them saved there?
I am only really thinking of keeping a tidy Connection Manager tab. If there's a more efficient way to do it than 30something file connections that would be great?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
由于性能原因，数据流与内定义的列和类型紧密绑定。
如果您的用例是“我需要在过去30ish的一年中生成销售提取””，那么，您可以使用单个平面文件连接管理器来做，因为列和数据类型不会更改 - 您只是在细分数据。
但是，如果您的用例是“我需要提取销售，员工，地址等”，那么每个实体/数据形状都需要平面文件连接管理器（最好是数据流）。
我的经验是，将其设计为30ish软件包（SQL源 - 平面文件目的地），可以很好地为您提供完美的服务，该包装使用了整个编排程序软件包，该程序包使用执行软件包任务来运行依赖性流程。 最佳好处
为 me ，我也会看 biml ，看看您是否不能仅仅脚本脚本那。
将评论转到
未来的证明位置信息，我将定义一个诸如
basefilepath之类的项目参数（假设最有可能更改的是Dev I使用C：\ ssisdata \ Input \ input \ file1之类的路径。c：\ ssisdata \ input，然后分配\\ server \ share \ share \ input，用于通过配置为项目的生产。至关重要的部分是确保在Flat File Connection Manager的
ConnectionsTring属性上存在表达式，部分是按参数的值驱动的。同样，作为一个编程性懒惰的人，我有一个名为currentfilepath的变量，其表达式为@[project $ :: basefilepath] +“ \\ file1.csv”ffcm然后使用 @[用户:: CurrentFilePath]来确保将文件写入正确的位置。而且，由于我每个提取物创建1个软件包，因此我不必担心每个平面文件连接管理器都会创建一个变量，因为它们都是相同的模式。
A data flow is tightly bound to the columns and types defined within for performance reasons.
If your use case is "I need to generate an extract of sales by year for the past 30ish" then yes, you can make do with a single Flat File Connection Manager because the columns and data types will not change - you're simply segmenting the data.
However, if your use case is "I need to extract Sales, Employees, Addresses, etc" then you will need a Flat File Connection Manager (and preferably a data flow) per entity/data shape.
It's my experience that you would be nicely served by designing this as 30ish packages (SQL Source -> Flat File Destination) with an overall orchestrator package that uses Execute Package Task to run the dependent processes. Top benefits
Being me, I'd also look at Biml and see whether you can't just script all that out.
Addressing comments
To future proof location info, I'd define a project parameter of something like
BaseFilePath(assuming the most probably change is that dev I use a path of something like C:\ssisdata\input\file1.txt, C:\ssisdata\input\file3.csv and then production would be \server\share\input\file1.txt or E:\someplace\for\data\file1.txt) which I would populate with the dev valueC:\ssisdata\inputand then assign the value of\\server\share\inputfor the production to the project via configuration.The crucial piece would be to ensure that an Expression exists on the Flat File Connection Manager's
ConnectionStringproperty to driven, in part, by the parameter's value. Again, being a programmatically lazy person, I have a Variable namedCurrentFilePathwith an expression like@[Project$::BaseFilePath] + "\\file1.csv"The FFCM then uses @[User::CurrentFilePath] to ensure I write the file to the correct location. And since I create 1 package per extract, I don't have to worry about creating a Variable per flat file connection manager as it's all the same pattern.