Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Matched and Unmatched Rows in ssis 2005 Expand / Collapse
Author
Message
Posted Thursday, October 4, 2012 10:22 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 3:57 AM
Points: 93, Visits: 237
Hi,

Need to transfer data from database Db1 (Db1.sampletab1) to database Db2 (Db2.sampletab2) using ssis 2005.

Condition:
If matched data, leave as it is.
If unmatched data, then insert the records into DB2.sampletab2

Is this possible in SSIS 2005? If yes, could you please let me know the steps?


CREATE TABLE [dbo].[sampletab1](
[pKey] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[SasId] [nvarchar](max) NULL,
[Name] [nvarchar](max) NULL,
[TypeId] [nvarchar](max) NULL,
[UserName] [nvarchar](max) NULL,
[CreatedDateTime] [datetime] NULL,
[CompletedDateTime] [datetime] NULL,
[Boolean] [bit] NULL,
)

insert into sampletab1 values('sdf','werwer','s233','xzfdsdf',12/11/2011,14/11/2011,'TRUE')


Regards
SqlStud
Post #1368809
Posted Thursday, October 4, 2012 10:56 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 14, 2014 7:25 AM
Points: 539, Visits: 876
sqlstud (10/4/2012)
Hi,

Need to transfer data from database Db1 (Db1.sampletab1) to database Db2 (Db2.sampletab2) using ssis 2005.

Condition:
If matched data, leave as it is.
If unmatched data, then insert the records into DB2.sampletab2

Is this possible in SSIS 2005? If yes, could you please let me know the steps?


CREATE TABLE [dbo].[sampletab1](
[pKey] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[SasId] [nvarchar](max) NULL,
[Name] [nvarchar](max) NULL,
[TypeId] [nvarchar](max) NULL,
[UserName] [nvarchar](max) NULL,
[CreatedDateTime] [datetime] NULL,
[CompletedDateTime] [datetime] NULL,
[Boolean] [bit] NULL,
)

insert into sampletab1 values('sdf','werwer','s233','xzfdsdf',12/11/2011,14/11/2011,'TRUE')


Regards
SqlStud


Very much possible.You can simply handle it by writing except query or you can also use conditional split.

However few query here.
1. How you are recognising new record means what is the unique field.
2. Why are you leaving existing record as in ideal scenario we usually update the old records i.e. genearally we use UPINSERT logic for this type of scenario.


--rhythmk
------------------------------------------------------------------
To post your question use below link

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1368816
Posted Wednesday, October 10, 2012 12:31 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 3:57 AM
Points: 93, Visits: 237
Thanks Rhythmk..

I have loaded unmatched data via Erroroutput

Regards
Sqlstud
Post #1370707
Posted Wednesday, October 10, 2012 12:40 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:47 AM
Points: 199, Visits: 512
Option - 1: You can make use of LookUp and Condition Split Transformation to achive this.

Option - 2: You can do this by Creating a Procedure.

A. Load all the data's to Staging Table.
B. Create Procedure and load latest data's, by Comparing Staging and Fact table data's and then load the latest data's.
Post #1370713
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse