how to do find what column has changed in ssis

Previously I've wrote about blueprint and implementation an
UPSERT with SSIS. UPSERT is near Update existing records, and Insert new
records. Today I want to extend this to encompass DELETED records every bit well. So
method used in this post tin be used to find INSERTED / UPDATED / DELETED
records from the source tabular array and utilize those changes into the destination
table.

In this instance I used Merge Join Transformation,
Conditional Split, and OLE DB Command transform to implement the solution.
Beginning we employ a total outer join on source and destination table on primal
column(s) with Merge Join transformation. Then we use a conditional split to
discover out the change type (removed, new, or existing records). Existing records
will require another processing to discover out is in that location any changes happened or
not? We employ another conditional split to compare value of equivalent columns in
source and destination.

Source table used in this example is Department table from
AdventureWorks2012 sample database which you tin can download online for complimentary.

Solution:

one- Create an OLE DB Source for source table, utilise select command
below to select data:

select *

from dbo . Department

order by DepartmentID

Note to the ORDER BY Clause in this statement. That function is
required because Merge Join transform require sorted sources as input. Proper noun
this component as Source Table

2- Create another OLE DB Source for destination table. In this
instance source and destination has same table proper noun but are in different
databases. Then we employ same script as step ane for this one too. Name this
component every bit Destination Table.

3- Right click on OLE DB Source, choose Show Advanced Editor.
In the Advanced Editor window get to Input and Output Properties tab. Select the
OLE DB Source Output, and change the IsSorted Property to true.

4- Expand OLE DB Source output, and then under Output Columns
select DepartmentID. Then modify the SortKeyPosition to ane.

5- Utilise steps 3 and 4 for both OLE DB Sources (Source Table
and Destination Table)

half-dozen- Drag and drib a Merge Bring together transformation, connect 2 OLE
DB Sources to this. Prepare Source Table as left and Destination Table every bit correct
input of this transformation.

seven- Get to Merge Join transformation editor, DepartmentID will be
used as joining cavalcade (selected based on sort properties of previous components).
Note that if y'all don't sort input columns of the merge join transformation then
you cannot get into the editor of this transformation and yous face the error
regarding sorting of inputs.

Select all columns from Source and Destination tables in the
merge join transform, and rename them every bit moving-picture show beneath shows (add Source or
Destination prefix to each column)

8- Add a Provisional Separate transformation and write two
expressions below to find out new records, and removed records. Also rename
default output as existing records and screenshot below shows

Expressions used in this sample are very piece of cake and simply
find record changes. For instance expression below:

!ISNULL(SourceDepartmentID) &&
ISNULL(DestinationDepartmentID)

Used to find new records. And literally means records that
has SourceDepartmentID but not DestinationDepartmentID.

And this script used to discover deleted records:

ISNULL(SourceDepartmentID) &&
!ISNULL(DestinationDepartmentID)

9- Add an OLE DB Destination and connect NEW RECORDS output to
it. Set up configuration for destination table and employ columns with Source prefix
in the column mapping of the OLE DB destination. This destination component
will insert new records into the destination table.

x- Add an OLE DB Command and connect Removed RECORDS output to
it. Create a connection to destination database, and write script below to
delete records past input department ID:

delete from dbo . department where DepartmentID =?

In the column mappings, map DestinationDepartmentID to the
parameter of statement.

eleven- Add together another Provisional Split and connect Existing Records
output to information technology. We use this component to find but records that had a change in
one of the values. And so we compare equivalent source and destination columns to
discover non-friction match data.

This is the expression used to find match information in screenshot
below:

(SourceName == DestinationName) && (SourceGroupName
== DestinationGroupName) && (SourceModifiedDate ==
DestinaitonModifiedDate)

12- Create a stored procedure in destination database to update
the Department table.

CREATE PROCEDURE dbo . UpdateDepartment

@DepartmentID smallint

, @Name nvarchar (50)

, @GroupName nvarchar (50)

, @ModifiedDate datetime

Equally

Begin

SET NOCOUNT ON ;

UPDATE [dbo] . [Department]

Ready

[Name] = @Proper name

, [GroupName] = @GroupName

, [ModifiedDate] = @ModifiedDate

WHERE [DepartmentID] = @DepartmentID

Cease

13- Add another OLE DB Control and employ non match output as the
input information stream to it. Connect it to destination database, and write below
argument in Component Properties tab's SQLCommand property.

exec dbo . UpdateDepartment ?,?,?,?

xiv- Map input columns (with source prefixes) to parameters in
the stored process as screenshot below shows

15- Run the package and you will run into changes will be applied to
destination table.

Testing the solution:

Here is data rows from source table

And data rows from destination table

Yellowish records are new records

Pink records are updated records

Green record is deleted record (in destination table)

After running the packet you will see records will exist
redirected to data path every bit implemented:

And destination table will pick changes:

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than xx years' experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His manufactures on different aspects of technologies, especially on MS BI, tin be found on his weblog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was besides an agile member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very pop book Power BI from Rookie to Stone Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published past Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business organization Applications Summit, Data Insight Summit, Laissez passer Pinnacle, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza's passion is to help you lot find the best data solution, he is Information enthusiast.

fulmertragstenk.blogspot.com

Source: https://radacad.com/insert-update-and-delete-destination-table-with-ssis

Share this post