将多种文件类型导入 SSIS/映射字段
我正在开发一个新的数据仓库,试图从许多不同的提供商导入许多不同格式的文件。
每个月的文件名可能相同,例如 MonthlyReturns.xls/.csv,或者某种模式,例如 NorthWestSalesData20100101.csv)。
我们不能要求提供商更改其命名约定。
我们是否必须创建一个 SSIS 包来从每个提供程序导入每种文件类型,或者是否有一种方法可以创建一个映射来将传入字段(假设它们有标题列)与我们的数据仓库中的字段相关联?
受欢迎的解决方案可能会采用 SSIS,尽管如果有一种简洁而优雅的方法来减少我离开后维护该解决方案所需的所有管理工作,则不一定非得如此。
我自己正在积极研究这个问题的解决方案,并将在这里发布我采用的解决方案,但我想将其扔给社区,以便对我的问题进行健全性检查。
预先感谢您的所有精彩回复。
I am working on a new Datawarehouse trying to import a number of different format files from a number of different providers.
The filenames may be the same each month, such as MonthlyReturns.xls/.csv, or a pattern, such as NorthWestSalesData20100101.csv).
We can't ask the providers to change their naming convention.
Do we have to create an SSIS package to import every kind of file-type from every provider, or is there a way we can create a mapping to relate the incoming fields (assuming they have header columns) to the fields in our Datawarehouse ?
The favoured solution is likely to adopt SSIS, although is doesn't necessarily have to if there's a neat and elegant way to cut down on all the administrivia needed to maintain the solution after I'm gone.
I'm actively working on a solution for this problem myself, and will post my adopted solution here, but I wanted to throw it out to the community to get a sanity-check on my question.
thanks in advance for all your great responses.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
您可以为每个容器创建一个
来迭代文件。然后将文件名分配给一个变量。这样您就可以使用任何文件,而无需事先知道其名称。可以根据文件扩展名过滤每个容器。
然后,您可以根据文件名、文件扩展名、文件格式或列标题创建映射工具。
可以找到为每个容器使用 a 的示例 这里。
You can create a
for each container
to iterate over the files. The filename is then assigned to a variable. This way you can work with any file without having to know its name beforehand. The for each container can be filtered based on file extension(s).You can then create a mapping facility based on the file name, file extension, file format, or column headers.
An example of using a for each container can be found here.