通过SSIS上传前验证数据 [英] Validate data before uploading through SSIS

问题描述

我有一个SSIS包将数据从Excel文件上传到Sql Server 2005表中。

I have a SSIS package to upload data from Excel file into an Sql Server 2005 table.

excel文件将有不同的数据线，范围从20k到30k线条。

The excel file will have varied lines of data ranging from 20k - 30k lines.

当所有数据正确时，上传工作正常。但是即使在单行中也存在小问题，显然会失败。我们要在上传之前验证excel文件，并想要告诉用户哪一行和第一行是不可修改的值（数据类型不匹配）等。

The upload works fine, when all the data are correct. But obviously fails when there is a small problem even in a single row. Examples like mandatory values presented null, inconvertable values (data type mismatch) etc.

列有错误...

任何想法，如何完成这个，而不需要花费太多时间和资源。

Any idea as to how to accomplish this, without consuming much time and resources.

谢谢

推荐答案

加载到没有任何强制值等待检查的临时表中可能最为简单在将其附加到主表之前。

It might be easiest to load into a temporary table that does not have any mandatory values etc and check that before appending it to the main table.

编辑重新评论

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset 

''This is not necessarily the best way to get the workbook name
''that you need
strFile = Workbooks(1).FullName

''Note that if HDR=No, F1,F2 etc are used for column names,
''if HDR=Yes, the names in the first row of the range
''can be used. 
''This is the Jet 4 connection string, you can get more
''here : http://www.connectionstrings.com/excel

 strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

''Note that HDR=Yes
''Pick one:
strSQL = "SELECT Frst, Secnd FROM TheRange WHERE SomeField Is Null" ''Named range
strSQL = "SELECT Frst, Secnd FROM [Sheet1$C3:C67] WHERE Val(Secnd)=0" ''Range
strSQL = "SELECT Frst, Secnd FROM [Sheet1$] WHERE First<Date()" ''Sheet

rs.Open strSQL, cn

Sheets("Sheet2").Cells(2, 1).CopyFromRecordset rs

这篇关于通过SSIS上传前验证数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！