如何通过 SSIS 中的参数设置文件源? [英] How to set file source via Parameter in SSIS?

问题描述

我为每个位置站点准备了多个包，每个站点包含 5 个其他子站点，每个月必须为其填充数据.因此，我有大约 20 个 excel 文件作为源数据，excel 文件作为源数据，我需要创建一个 excel 目标.由于有太多 excel 文件作为源，我在我的各种包中有多个 Excel 连接管理器(源 + 目标).

源文件名和位置每次都会改变，所以我想通过参数自动化文件路径.我按照文章中提到的步骤操作:

有人可以指导我到底做错了什么吗?

解决方案

看起来您正在使用 Excel 连接管理器ConnectionString"上的表达式财产.当该属性更改为仅不是该类型连接的有效连接字符串的文件路径时.

对于 excel 文件连接，情况略有不同，您需要使用 ExcelFilePath 属性，连接管理器在构建连接字符串时会使用该属性.

对于平面文件连接，是的，您将使用 ConnectionString 属性，Excel 连接，略有不同，使用 ExcelFilePath 属性.

I have multiple packages for each location site and each site contains 5 other sub sites for which data has to be populated every month. Hence I have approx 20 excel files as a source data for which the excel file acts as a source and I need to create an excel destination. Because of so many excel file as a source I have multiple Excel connection Managers (Source + Destination) across my various packages.

The source file name and the location will change everytime and so I was thinking to automate the file path via parameter. I follow the steps mentioned in the article: https://blogs.msdn.microsoft.com/sqlgardner/2015/06/18/ssis-tip-using-a-file-path-parameter-for-multiple-flat-files/

I followed every step but I got the error when I open the excel file (source). Below is the error snapshot:

Can someone guide me what exactly I am doing wrong here?

解决方案

Looks like you're using an expression on the excel connection manager "ConnectionString" property. When that property is changed to just a file path that isn't a valid connection string for that type of connection.

With excel file connections it's a little different and you'll want to use the ExcelFilePath property, which in turn is used by the connection manager when building out the connection string.

With Flat File Connections, yes you would use the ConnectionString property, Excel Connections, slightly different, use the ExcelFilePath property.

这篇关于如何通过 SSIS 中的参数设置文件源?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！