July 4, 2016 at 4:35 am
Hi,
Can someone please advise on how to copy only new records from table A to table B using SSIS ?
I tried to achieve this using lookup transformation but the rows are not getting copied over.
Any inputs will be appreciated.
Thanks.
July 4, 2016 at 4:53 am
If the Scenario is to get the increment load (Insert only mode) then you can use the below.
If your Source data is a SQL SERVER, then you can use the LastExecutionDate/ Inserted date from your fact or any audit table you are using.
and query the source using SourceDateColumn > [YourLastExecutionDate].
If you are getting from the file then 1st you dump the raw data in a staging table, then filter the data.
If somehow you cannot perform the above mentioned suggestion, you would have to do the Lookup transformation for this you can check the following Implementing Lookup Logic in SQL Server Integration Services[/url]
July 4, 2016 at 7:15 am
twin.devil (7/4/2016)
If the Scenario is to get the increment load (Insert only mode) then you can use the below.If your Source data is a SQL SERVER, then you can use the LastExecutionDate/ Inserted date from your fact or any audit table you are using.
and query the source using SourceDateColumn > [YourLastExecutionDate].
If you are getting from the file then 1st you dump the raw data in a staging table, then filter the data.
If somehow you cannot perform the above mentioned suggestion, you would have to do the Lookup transformation for this you can check the following Implementing Lookup Logic in SQL Server Integration Services[/url]
Thanks for your suggestions.
I have tried using lookup transformation using the link you provided but it still doesn't copy data to the new table. I am still facing issues with it. Thanks.
July 4, 2016 at 8:48 am
Can someone please advise on how to copy only new records from table A to table B
how are determining what the new records are in Table A?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 5, 2016 at 1:22 am
pwalter83 (7/4/2016)
twin.devil (7/4/2016)
If the Scenario is to get the increment load (Insert only mode) then you can use the below.If your Source data is a SQL SERVER, then you can use the LastExecutionDate/ Inserted date from your fact or any audit table you are using.
and query the source using SourceDateColumn > [YourLastExecutionDate].
If you are getting from the file then 1st you dump the raw data in a staging table, then filter the data.
If somehow you cannot perform the above mentioned suggestion, you would have to do the Lookup transformation for this you can check the following Implementing Lookup Logic in SQL Server Integration Services[/url]
Thanks for your suggestions.
I have tried using lookup transformation using the link you provided but it still doesn't copy data to the new table. I am still facing issues with it. Thanks.
If your data is not arriving at the Not Match Flow direction then it means your complete data is available in the destination table. Which mean you are missing something. Because lookup transformation is quite a commonly use task. I suspect issue might be in the logic not in the lookup transformation but who knows.
i have put some scenario how lookup behave on the data. This might help you in understanding the issue you are facing right now.
create table Source_table(id int , name varchar(25) ,age int );
create table Destination_table1(id int , name varchar(25) ,age int );
-------------------- Case # 1 : Blank Destination
------ Source data
insert into Source_table values(1 , 'Anand' , 25) ;
insert into Source_table values(2 , 'BALA' , 25) ;
insert into Source_table values(3 , 'JP' , 25);
---------- will show all the records.
select *
from Source_table src
left join Destination_table1 dest on src.name = dest.name
where dest.name is null
-------------------- Case # 2 : partial fill Destination
------ Destination data
insert into Destination_table1 values(1 , 'Anand' , 25) ;
insert into Destination_table1 values(2 , 'BALA' , 25) ;
---------- will show new/remaining the records.
select *
from Source_table src
left join Destination_table1 dest on src.name = dest.name
where dest.name is null
-------------------- Case # 3 : full Destination
------ Destination data
insert into Destination_table1 values(1 , 'Anand' , 25) ;
insert into Destination_table1 values(2 , 'BALA' , 25) ;
insert into Destination_table1 values(3 , 'JP' , 25);
---------- will show no the records.
select *
from Source_table src
left join Destination_table1 dest on src.name = dest.name
where dest.name is null
drop table Source_table
drop table Destination_table1
Kindly go through with the example and see what kind of scenario you are facing right now.
And also if you could share some raw data, like what already in the destination and what will be coming back from the source that would help as well.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply