如何在导入SSIS中的sql表之前验证excel数据？ [英] how to validate excel data before importing into sql table in SSIS?

问题描述

我有一个包含6列的excel文件，现在我需要验证每列中每个单元格的值。

例如

1.如果是日期栏，那么我需要验证日期格式并输入

2.如果是数字然后我需要验证它是否为数字？



假设我在excel中有超过10000行，现在我需要在导入之前运行验证过程进入sql表。如果验证失败而不是回滚导入过程。



建议我这样做的最好方法。

解决方案

你只需点击Excel源右键=>输入输出属性=> Excel dsource output =>输出列然后更改数据类型

您好murugesan.velusamy，



您可以在将数据从excel传输到SQL Server thruogh SSIS之前进行MAPPING。

请参考下面的You tube链接。



SSIS验证 [ ^ ]

在SSIS和导入中验证Excel [ ^ ]

MS BI Blog：SSIS：数据验证 [ ^

I have a excel file with having 6 columns, Now I need to validate the value from each cell from each column.
Eg
1.if it is a date column then i need to validate the date format and type
2.If it is a number then i need to validate it is a number or not?

Assume that i have more than 10000 rows in the excel and now i need run the validation process before i import into sql table. If validation fails than rollback the import process.

Suggest me the best way to do this.

解决方案

Hi, you have just to click on Excel source right button => input output properties =>Excel dsource output=>output column and then you change Data type

Hi murugesan.velusamy,

You can do the MAPPING before you transfer the data from excel to the SQL Server thruogh SSIS.
Please refer the below You tube link.

SSIS Validation[^]
Validating Excel in SSIS and Importing [^]

MS BI Blog: SSIS:Data Validation[^]

这篇关于如何在导入SSIS中的sql表之前验证excel数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！