SSIS Excel 数据源 - 是否可以覆盖列数据类型? [英] SSIS Excel Data Source - Is it possible to override column data types?
问题描述
当在 SSIS 中使用 Excel 数据源时，每个单独列的数据类型都来自列中的数据.是否可以覆盖此行为?
When an excel data source is used in SSIS, the data types of each individual column are derived from the data in the columns. Is it possible to override this behaviour?
理想情况下，我们希望从 excel 源提供的每一列都是字符串数据类型，以便可以在数据流的后续步骤中对从源接收的数据执行数据验证.
Ideally we would like every column delivered from the excel source to be string data type, so that data validation can be performed on the data received from the source in a later step in the data flow.
目前，错误输出"选项卡可用于忽略转换失败 - 相关数据为空，包将继续执行.但是，我们想知道原始数据是什么，以便可以为该行生成适当的错误消息.
Currently, the Error Output tab can be used to ignore conversion failures - the data in question is then null, and the package will continue to execute. However, we want to know what the original data was so that an appropriate error message can be generated for that row.
推荐答案
是的，你可以.只需进入 Excel 源上的输出列列表并为每一列设置类型.
Yes, you can. Just go into the output column list on the Excel source and set the type for each of the columns.
要进入输入列列表，请右键单击 Excel 源，选择显示高级编辑器"，单击标有输入和输出属性"的选项卡.
To get to the input columns list right click on the Excel source, select 'Show Advanced Editor', click the tab labeled 'Input and Output Properties'.
一个可能更好的解决方案是使用派生列组件，您可以在其中为 Excel 中的每一列实际构建新"列.这样做的好处是
A potentially better solution is to use the derived column component where you can actually build "new" columns for each column in Excel. This has the benefits of
- 您可以更好地控制转换目标.
- 您可以添加控制更改的规则(即，如果 null 给我一个空字符串，但如果有数据则给我一个字符串的数据)
- 您的数据源不直接与流程的其余部分相关联(即您可以更改数据源，您唯一需要做的工作是在派生列中)
这篇关于SSIS Excel 数据源 - 是否可以覆盖列数据类型?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
