有没有办法对 SSIS 平面文件源中的字段重新排序？
我有一个 SSIS 包，使用带有大量字段的制表符分隔的平面文件源。 最近，制表符分隔平面文件的提供商决定通过在文件中随机添加几十个新字段来更改平面文件的格式。 不用说，这已经把包装搞砸了。
有没有办法对平面文件源中的字段重新排序，而不是重建另一个平面文件源并重新定义所有字段、类型和长度？ 如果微软允许您在“高级列”窗格中移动字段，那就太好了，但是不行。
任何帮助表示赞赏。
I have an SSIS package using a tab delimited flat file source with a TON of fields. Recently the provider of the tab delimited flat file has decided to change the format of the flat file by sprinkling a couple dozen new fields at random into the file. Needless to say, this hosed the package.
Rather than rebuild another flat file source and redefine all the fields, types, and lengths all over again, is there a way to reorder the fields in the flat file source? Sure would have been nice if Microsoft allowed you to move the fields around in the Advanced Columns pane, but noooooo.
Any help is appreciated.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
如果您只需要向文件添加列，则可以在平面文件连接编辑器中执行此操作。 在高级窗口中，您可以选择新字段旁边的字段，然后单击“新建”按钮旁边的 V 形符号。 它会让您选择在之前插入或在之后插入。
如果确实需要移动内容，则需要编辑 XML 源。 如果您使用现有文件定义作为指导，则可以相对轻松地在 Excel 或 T-SQL 中构建新文件。 至少比重新输入所有内容要容易。
If you only need to add columns to your file, you can do that in the Flat File connection editor. In the advanced window, you can select the field next to the new one and click the chevron next to the New button. It will give you the choice insert before or insert after.
If you truly have to move things around, you'll need to edit the XML source. If you use the existing file definition as a guide, you can build the new one in Excel or T-SQL relatively easily. Easier than typing everything in all over again at least.
我遇到了类似的问题：我需要更改平面文件目标中的列顺序。 我选择的节省时间的方法是：
不是问题的直接答案，但我来这里寻找有关“如何重新排列平面文件目标列”的建议，也许这会对某人有所帮助。
I had a similar issue: I needed to change the order of columns in my flat file destination. The time-saving approach I settled on:
Not a direct answer to the question, but I came here looking for advice on "how to rearrange flat file destination columns", perhaps this will help someone.
我还没有看到该问题的解决方案。 SSIS 在更改元数据方面不是很强。 您可以尝试在记事本中执行此操作，但这非常棘手且有很多错误。 我不会向你推荐这个。
I haven't seen an solution for that problem. SSIS isn't very strong in changing metadata. You could try to do it in notepad, but that is very tricky and very buggy. I would not recommand that to you.
在 IDE 下面的连接管理器中，您可以双击文件名并编辑您想要的所有内容。
In the connection managers below of your IDE you can double click your file name and edit everything you want.
这仍然是SSIS的一个“特性”。 为了解决这个问题，我创建了一个名为“NULL”的平面文件连接，其中包含一个名为“NULL”的列。 使用“新建”按钮添加列。 我将默认列名称从“Column 0”更改为“NULL”。 此列名称不得与要重新填充的列表中的任何列名称匹配。 如果您有一个名为“NULL”的真实列，请为未使用的列名选择其他内容。 您可以在项目中保留“NULL”平面文件连接以供以后使用。 （我希望在此项目中多次需要它。）
在此示例中，我使用平面文件目标。 更改平面文件目标以使用 NULL 连接。
检查映射以查看没有映射的列。 保存此操作将重置为映射存储的元数据。
最后，将平面文件目标更改回正确的连接，以获得新的映射，而不会干扰元数据。
我的示例是平面文件目标。 它应该适用于重置元数据的平面文件源。 它类似于在使用 ODBC 源等时将查询更改为“select 1 as [NULL]”并返回到清除元数据的技巧。
This is still a "feature" of SSIS. To work around this I create a flat file connection called "NULL" with a single column named "NULL". Use the "New" button to add the column. I change the default column name from "Column 0" to "NULL". This column name must not match any column name in the list to be re-populated. If you have a real column named "NULL", pick something else for the column name that's not in use. You can keep the "NULL" flat file connection in the project for later use. (I expect to need it a few more times in this project.)
For this example, I use a flat file destination. Change the Flat File Destination to use the NULL connection.
Check the mapping to see there are no columns mapped. Saving this resets the metadata stored for the mapping.
Finally, change the Flat File Destination back to the correct connection to get a new mapping without metadata interference.
My example is a flat file destination. It should work for a flat file source for resetting the metadata. It is similar to the trick of changing a query to "select 1 as [NULL]" and back to purge metadata when using a ODBC source or such.
您可能可以尝试一些东西，但我还没有测试过..使用表达式为您的平面文件源设置所有内容？ 关闭设计时验证
you could probably try something, but i havent tested.. use expressions to set everything for your flat file source? turn design time validation off