SSIS自动插入更新excel文件格式问题
我必须插入和更新一些每天来自的值 excel 文件，但由于日常 excel 文件格式不同 那么请告诉我其他可能的自动插入更新方法吗？
i have to insert and update some values which is daily coming from
excel file but as everyday excel file format is different
so tell me other possible ways to automate insert update ?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
Excel 文件是否确实具有不同的格式，或者 Excel 只是认为它们不同？如果列仍然处于相同的序数位置，但它们被解释为具有不同的数据类型，那么是的，您可以向驱动程序提供提示来克服。
否则，您可以使用 C#/vb.net 并查询工作表，将其转储到数据集中，将其写入变量，然后粉碎该对象，但它很难看。事实上，以编程方式处理 Excel 总是很丑陋，最好避免。
Are the excel files really in different formats or does Excel just think they are different? If the columns are still in the same ordinal positions but they are being interpreted as having different data types, then yes, you can provide hints to the driver to overcome.
Otherwise, you could use C#/vb.net and query the worksheet, dump that into an dataset, write that to a variable and then shred that object but it's ugly. In fact, dealing with Excel in a programmatic fashion is always ugly and best avoided.
如果您的文件每天都有不同的格式，那么您就不走运了。这是一个问题，并且确实没有简单或有效的方法来解析和插入/更新基于此。无论数据来源是什么，您都需要确保其一致。
如果它是您可以相应测试和处理的少数格式，那么您总是可以在 SSIS 包中拥有一些数据流逻辑，但如果这不是预先确定的，那么您将无法处理这些案例。
If your file is a different format each day then you are out of luck. That is a problem, and there is really no easy or efficient way to parse and insert/update based on that. Whatever the source of the data is, you need to ensure that it becomes consistent.
If it is a handful of formats that you can test and handle accordingly then you could always have some data flow logic inside the SSIS package, but if this isn't predetermined then you would have no way of handling these cases.
您可以通过将文件返回给提供商并要求他们每天以相同的方式提供来处理此问题。如果文件格式不正确，那么您的 SSIS 包应该拒绝该文件。当您尝试这样做时，如果他们发送 .txt 或 .csv 文件，您遇到的问题就会少得多。 Excel 支持非常差。
YOu deal with this by returning the file to the provider and requiring them to provide in the same fashion every day. Then your SSIS pacakge should reject the file if it is not in the correct format. While you aer at it you will have far fewer problems if they send .txt or .csv file. Excel support is exceedingly poor.