October 17, 2018 at 9:46 am
I have a 2 sql tables with 1000 rows each.
Initially
TableA - 1000 rows
TableB - 1000 rows
Every time a new row is added to TableA, it automatically needs to be added to table B.How do I do this in SSIS
Thanks
October 17, 2018 at 9:58 am
Why do you need to do this in SSIS? Just use a trigger. In fact, why do you need to do it at all? Having two versions of the truth isn't good for the integrity of your data.
By the way, there's no need to post the same question in more than one forum.
John
October 17, 2018 at 10:42 am
mtz676 - Wednesday, October 17, 2018 9:46 AMI have a 2 sql tables with 1000 rows each.
Initially
TableA - 1000 rows
TableB - 1000 rowsEvery time a new row is added to TableA, it automatically needs to be added to table B.How do I do this in SSIS
Thanks
As John said a trigger will do the job and work for any insert, or if you want to do it in a single SQL statement in some code that does the insert, this would do it:CREATE TABLE TableA(
Col1 nvarchar(3),
Col2 int,
Col3 date
);
CREATE TABLE TableB(
Col1 nvarchar(3),
Col2 int,
Col3 date
);
GO
INSERT TableB(Col1,Col2,Col3)
SELECT a.Col1, a.Col2, a.Col3
FROM (INSERT INTO TableA(Col1,Col2,Col3)
OUTPUT inserted.Col1, inserted.Col2, inserted.Col3
SELECT 'X', 1, '20181017') AS a
GO
SELECT * FROM TableA;
SELECT * FROM TableB;
GO
October 17, 2018 at 11:18 am
In your data flow can't you just set up two destination, one for table a and one for table b?
October 17, 2018 at 11:43 am
no..the source table is in domain A and the destination table is in Domain B
So a row gets added to table A, it should be immediately added to TableB in domain B.
Can this be achieved in SSIS
October 17, 2018 at 12:44 pm
What about if a row is updated or deleted? Do those need to go across too?
Still, SSIS is unlikely to be the roght tool here; especially if it needa to be immediate. Like the others have mentioned, a trigger would be better. Why can't you set up trust between the 2 domains though and simply have those in Domain B access the data in Domain A (or vice versa)?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 17, 2018 at 1:08 pm
mtz676 - Wednesday, October 17, 2018 11:43 AMno..the source table is in domain A and the destination table is in Domain B
So a row gets added to table A, it should be immediately added to TableB in domain B.
Can this be achieved in SSIS
Do you mean the source table is table A or that the source table is in domain A and it pushes data to table A and table B?
October 17, 2018 at 2:19 pm
mtz676 - Wednesday, October 17, 2018 11:43 AMno..the source table is in domain A and the destination table is in Domain B
So a row gets added to table A, it should be immediately added to TableB in domain B.
Can this be achieved in SSIS
Replication.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 17, 2018 at 11:55 pm
John Mitchell-245523 - Wednesday, October 17, 2018 9:58 AMWhy do you need to do this in SSIS? Just use a trigger. In fact, why do you need to do it at all? Having two versions of the truth isn't good for the integrity of your data.By the way, there's no need to post the same question in more than one forum.
John
I wanted to know if this can be done using t-sql and also if it can be done using SSIS, hence the 2 postings
Thanks
October 17, 2018 at 11:57 pm
ZZartin - Wednesday, October 17, 2018 1:08 PMmtz676 - Wednesday, October 17, 2018 11:43 AMno..the source table is in domain A and the destination table is in Domain B
So a row gets added to table A, it should be immediately added to TableB in domain B.
Can this be achieved in SSISDo you mean the source table is table A or that the source table is in domain A and it pushes data to table A and table B?
TableA is in Domain A;Table B is in Domain B
If a new row is added to table A it should be added to Table B;If a row gets deleted from Table A then it should be delleted from Table B;If a row is updated in Table A then it should be updated in Table B
Hope I am clear now
Thanks
October 18, 2018 at 12:39 am
drew.allen - Wednesday, October 17, 2018 2:19 PMmtz676 - Wednesday, October 17, 2018 11:43 AMno..the source table is in domain A and the destination table is in Domain B
So a row gets added to table A, it should be immediately added to TableB in domain B.
Can this be achieved in SSISReplication.
Drew
+1
...
October 18, 2018 at 4:52 am
HappyGeek - Thursday, October 18, 2018 12:39 AMdrew.allen - Wednesday, October 17, 2018 2:19 PMmtz676 - Wednesday, October 17, 2018 11:43 AMno..the source table is in domain A and the destination table is in Domain B
So a row gets added to table A, it should be immediately added to TableB in domain B.
Can this be achieved in SSISReplication.
Drew
+1
Yes, replication is the recommended way to do it.
An alternative (if you can create a linked server to connect to both) is to write a stored procedure that updates TableB from a query on TableA, then create a scheduled job and set it to run every 5 minutes or so.
October 19, 2018 at 7:19 am
Jonathan AC Roberts - Thursday, October 18, 2018 4:52 AMHappyGeek - Thursday, October 18, 2018 12:39 AMdrew.allen - Wednesday, October 17, 2018 2:19 PMmtz676 - Wednesday, October 17, 2018 11:43 AMno..the source table is in domain A and the destination table is in Domain B
So a row gets added to table A, it should be immediately added to TableB in domain B.
Can this be achieved in SSISReplication.
Drew
+1
Yes, replication is the recommended way to do it.
An alternative (if you can create a linked server to connect to both) is to write a stored procedure that updates TableB from a query on TableA, then create a scheduled job it to run every 5 minutes or so.
Or, if you can create a linked server, have a trigger on TableA update TableB via the linked server using the inserted/deleted tables.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply