SSIS发挥作用后需要进行数据验证 [英] Data validation needed after SSIS works it's magic

问题描述

大家好，

在这种情况下，我正在使用SSIS从excel电子表格(特定格式)中读取数据.该程序包可以正常工作，并将数据从电子表格导入SQL Server数据库.我现在唯一的问题是，我需要确保电子表格中的两个字段将是唯一的，换句话说，我(或者我应该说这个包)不应插入任何特定记录，碰巧这两个字段都已经在数据库中了.

现在我不认为我可以在SSIS包本身中做这种事情.所以我在想，如果我阅读所有内容(而不用担心这两个主键)并将它们放入一个表(在同一个数据库中将其称为临时表)，然后可能以存储过程的形式进行验证(这就是我正在努力解决的问题)并运行该存储过程.

在验证了这两个字段之后，假设ssn和emp ID是唯一的(临时表中不存在)，然后相同的存储过程将以某种方式创建具有所有约束和数据的EXACT SAME TABLE，并将其放入另一个表中，然后我可以用它来报告.

这只是一个浮想联翩的想法.我什至不确定这是否可行，如果可行，怎么办?

我也乐于接受任何其他可能会使我的生活更轻松的想法.

在此先多谢.

〜Tina

Hello guys,

Here''s the scenario, I''m reading data from an excel spreadsheet(specific format) using SSIS. The package works fine and brings the data into the SQL server database from the spreadsheet. The only issue that I have now is that I need to make sure that two fields that are coming in from the spreadsheet are going to be unique, in other words I (or perhaps I should say the package) should not insert any particular record that happened to have both those fields already in the database.

Now I don''t think I can do that kind of stuff in the SSIS package itself. So I was thinking, what if I read everything (without worrying about those two primary keys) and put them into a table (lets call it a temp table in the same database. Then maybe do the validation in the form of a stored procedure (which is what I''m struggling with) and run that stored procedure.

After validating that those 2 fields, lets say ssn and emp ID are unique (are not present in the temp table) then the same stored procedure will some how create the EXACT SAME TABLE with all the constraints and data and put it in another table, which then I can use for reporting.

This is just a wild thought that came to mind. I''m not even sure if this is doable, and if so how?

I''m also open to any other ideas which might make my life a little easier.

Thanks a lot in advance.

~Tina

推荐答案

我发现 ^ ].

不知道它是否对您有帮助.

I found Eliminating Duplicate Primary Keys in SSIS[^].

Don''t know if it helps in your case.

感谢您的答复，但这并不是我所要的...

谁能帮忙吗?

Thanks for the response, but that''s not quite what I was asking for...

Can anyone please help?

这篇关于SSIS发挥作用后需要进行数据验证的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！