将Destination表与Source flat文件进行比较并相应地更新记录 [英] Compare Destination table to Source flat file and update records accordingly

问题描述

我完全没有时间限制让这个项目完成，所以我正在寻求帮助。 首先，我几天前刚刚开始使用SSIS，我对它和整个SQL都非常陌生。

I am quite under a time limit to get this project complete so I am reaching out for help.  To start, I JUST started working with SSIS a few days ago, I am VERY new to it as well as SQL as a whole.

我有来自Qlikview应用程序的数据存储为CSV。 此数据没有任何正确的主键或类似的东西。 在Qlikview中，我根据USERID，Role，SubRolename创建了一个哈希值（称为
字段PrimaryK）。 这很好，因为它为这些值的每个唯一组合创建一个唯一的字符串。

I have data coming out of a Qlikview application and being stored into a CSV.  This data does not have any proper primary keys or anything like that.  In Qlikview, I have it creating a hash number based on USERID, Role, SubRolename (called this field PrimaryK).  This is good since it creates a unique string for each unique combination of those values.

我有一个SQL表，用于在构建SSIS包后存储CSV中的信息。

I have a SQL table built to store the information from the CSV once the SSIS package is built.

我需要创建一个Type2 Slowly Changing维度，跟踪（并更新SQL表中名为Modified Date的字段）对现有记录的更改，插入新记录，并检测是否已删除记录。 

I need to create a Type2 Slowly Changing dimension that tracks (and updates a field in the SQL table called Modified Date) changes to existing records, inserts new records, and detects if a record has been removed. 

简而言之，我需要SSIS来比较平面文件源和目标SQL表。 如果目标中存在PrimaryK，则不执行任何操作，这些是现有记录。 如果目的地中不存在PrimaryK，请将其插入（这些是
新记录）。 如果源平面文件中不存在目标中的PrimaryK，请在目标表中使用今日日期在修改日期字段中更新此记录。

In a nutshell, I need SSIS to compare the flat file source to the destination SQL table.  If the PrimaryK exists in the destination, do nothing, these are existing records.  If the PrimaryK does not exist in the destination, insert it (these are new records).  If there is a PrimaryK in the destination that does not exist in the source flat file, update this record in the destination table with todays date in Modified Date field.

我希望这是有道理的。 感谢任何帮助，我对此非常新......所以lamen术语：）

I hope this makes sense.  Appreciate any help, I am very new to this so...lamens terms :)

推荐答案

嗨Josh，

基本上你需要将（带上文件）转移到SQL Server中强制执行PK的表中，然后运行

T-SQL MERGE 它和现有的固定表。

Basically you need to stage (bring the file) into a table in SQL Server that enforces the PK, and run the T-SQL MERGE against it and the existing stationary table.

在UPDATE / INSERT上做你所描述的。

On UPDATE/INSERT do what you described.

祝你好运！

这篇关于将Destination表与Source flat文件进行比较并相应地更新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！