.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

How to insert new and update existing records using SSIS

Posted By:Rajeev Kumar Mehta       Posted Date: March 29, 2011    Points: 200    Category: DataBase    URL: http://www.dotnetspark.com  

This Articles will illustrates how to update existing records and insert new ones while incremental load using SSIS
 

How to insert new and update existing records using SSIS

We often need in the incremental load of various tables (from source to destination) that old record must be updated and new records to be inserted.

Step (i). Finding the columns of the source tables from which we can know that a particular records is a new record or it is the old one.

For e.g. I am taking a Student(Source) Table. It's Structrure is:

By looking it's table structure we easily found that we can determine old/new record on the basis of createdate and modidate.

New record ->  Createdate = Modidate
Old Record ->  Createdate <> Modidate


Step.(ii).Create an SSIS Package

(a).Open BIDS
(b) File - New Project
(b) Name - InsertUpdate



(d) Click Ok

Step.(iii). Add Oledb connection for source and destination

(a). Right click on connection manager pane and select new OLE DB Connection


(c). Enter Server name or IP and then select database name


(d). Click on Test Connection
(e). Click OK.

(f). Repeat step.(a) to step(d) to create an OLE DB connection for the destination

Step.(iv). Add a dataflow task into package




Step.(v).Rename it to Student_incr

Step(vi).Add three variable of the package level scope

Date_From : Int32,
Date_To: Int32
Query_Student: String

Set the value of package variable as shown above.

Here Query_Student variable will conatin the SQL incremental query :

"SELECT  * from Student WHERE  (CAST(CONVERT(VARCHAR(10), Createdate, 111) AS DATETIME) >=  CAST(CONVERT(VARCHAR(10), DATEADD(Day, "+(DT_WSTR,50)@[User::Days_From]+", GETDATE()), 111) AS DATETIME)) AND (CAST(CONVERT(VARCHAR(10), Createdate, 111) AS DATETIME) < CAST(CONVERT(VARCHAR(10),DATEADD(Day, "+(DT_WSTR,50)@[User::Days_To]+", GETDATE()), 111) AS DATETIME))"


Paste this query in the expression property of the Query_Student variable

Step.(vii). Double click Student_incr dataflow task

(a). Drag and drop OLE DB Source onto the Package
(b). Right click on the OLE DB Source and then select edit
(c). Set properties:
(d).Click Ok.

(e).drag and drop Conditional Split component onto the package
(g) Connect it to OLE DB Source and then right click on it and then select edit

(h).  Make two mutually exclusive condition as shown
Insert -> Createdate = Modidate
Update -> CreateDate != Modidate

(i). Click ok.


(j). drag and drop OLE DB destination and OLE DB Command components onto the package
(k).Connect OLE DB destination and OLE DB Command to Conditinal split.
Insert -> OLE DB destination
Update -> OLE DB Command


(l). Right click on the OLE DB destination and select Edit and then set properties


(m). Map the column on the mapping page and then click ok.

(n). Create a stored procedure in the destination database named
  "UpdateStudentProc" which has the following definition

USE [Destination]
GO
/****** Object:  StoredProcedure [dbo].[UpdateStudentProc]  Script Date: 03/29/2011 15:44:45 ******/
SET ANSI_NULLS ON
GO
SET
 QUOTED_IDENTIFIER ON
GO
Create
 Proc [dbo].[UpdateStudentProc]
@RollNo bigint,
@Name nvarchar(100),
@Address nvarchar(500),
@Class int,
@Createdate datetime,
@Modidate datetime
as
begin
update
 Student set Name=@Name,Address=@Address,Class=@Class,Createdate=@Createdate,Modidate=@Modidate
where RollNo=@RollNo
IF @@RowCount = 0
Begin
 INSERT INTO Student(RollNo,Name,Address,Class,Createdate,Modidate)
 values (@RollNo,@Name,@Address,@Class,@Createdate,@Modidate) 
end
end


this procedure will update the existing records and will insert the new ones.


(0).Right click on OLE DB Command and then select edit

(p). In the connection manager ,select localhost.destination


(q). In the Component Properties set Properties


(r).In the Column mappings tab map the output column to parameters of UpdateStudentProc and then click ok.

You are through.



 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend