Insert new records from one database to another using SSIS
-
I have a table with Table 1 ( taskid bigint , name varchar(200), tasktype varchar(500)) , what I trying to achieve is every day at 5pm , what ever you new records inserted ( this table is only insert) I need to keep
insert the new records to another table with same structure as table 1 .How I can achieve this is SSIS , any example or details will be really helpful .
- RatanDeep Saha - Wednesday, August 22, 2018 12:58 PM
Are both of the databases on the same SQL instance? If so, I'd do this using a stored proc & leave SSIS out of it.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - Phil Parkin - Wednesday, August 22, 2018 1:15 PM
Both are on different servers
-
OK, then we are in SSIS territory. This is a fairly simple task, assuming TaskId is an incrementing IDENTITY column:
1) Create an int64 package-scoped variable called MaxTaskId.
2) Add an ExecuteSQL task to the control flow which runs the following queryDECLARE @MaxTaskId BIGINT = 0;
SELECT @MaxTaskId = Max(TaskId) from TargetTable1and assigns the result to the package variable.
3) Create a data flow task from SourceTable1 to TargetTable1 which has a dynamic WHERE clause, built using the package variable MaxTaskId
SELECT .. from SourceTable1
WHERE TaskId > MaxTaskIdThe absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Why not "just" use replication or log shipping for this?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks for your inputs ,
Not clear about step 3) . Do you mean SQL command from Variable in Source Connection . - Jeff Moden - Wednesday, August 22, 2018 4:57 PM
That will be little muggy , reason the source for this data is subscriber from actual production. We don't want to touch production for fetching data which very huge amount of inserts and also we don't need the data in real time .
More like incremental load for the differential data ( just insert) . - RatanDeep Saha - Wednesday, August 22, 2018 5:03 PM
Yes. Set the value of the variable using a suitable Expression and then use SQL Command from Variable.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - RatanDeep Saha - Wednesday, August 22, 2018 12:58 PM
How are you going to know which rows are new if you don't store a datestamp on the table to filter on?
You can do it with a dataflow that looks like this:
You need two database connections (one for the source and one for the destination)
It uses a pipelined data pump so is also fast. - Jonathan AC Roberts - Thursday, August 23, 2018 9:45 AMRatanDeep Saha - Wednesday, August 22, 2018 12:58 PM
How are you going to know which rows are new if you don't store a datestamp on the table to filter on?
You can do it with a dataflow that looks like this:
You need two database connections (one for the source and one for the destination)
It uses a pipelined data pump so is also fast.The taskID field is primary key and they table gets INSERTS only and no UPDATES
- RatanDeep Saha - Thursday, August 23, 2018 11:37 AM
Just because a column is a PK does not necessarily help here. It could be a GUID, for example, in which case you'd find the whole process rather unpleasant to code.
Which is why I asked you whether the column was an IDENTITY column in the source table (and it needs to be NOT an IDENTITY in the target table).The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - RatanDeep Saha - Thursday, August 23, 2018 11:37 AMJonathan AC Roberts - Thursday, August 23, 2018 9:45 AMRatanDeep Saha - Wednesday, August 22, 2018 12:58 PM
How are you going to know which rows are new if you don't store a datestamp on the table to filter on?
You can do it with a dataflow that looks like this:
You need two database connections (one for the source and one for the destination)
It uses a pipelined data pump so is also fast.The taskID field is primary key and they table gets INSERTS only and no UPDATES
You have two choices, you can either truncate the destination table and insert all the rows from the source table every day. Or if TaskId is an identity column then you need to store somewhere on the destination database the last highest value that was inserted then get all the rows from the source that are higher than this value.
- RatanDeep Saha - Wednesday, August 22, 2018 5:09 PM
I would add the keeping the solution as simple as possible is almost always the way to go. SSIS wins on this for most cases unless someone knows these technologies but doesn't know SSIS.
- Jeff Moden - Wednesday, August 22, 2018 4:57 PM
I would use replication (and did in the bad old days of Sql 2000). But I can't see any point in going for log shipping (other than that caused by replcation) when only one table and only new records are required. Of course I may have missed the meaning of teh OP, perhaps modifed records must not be passed on,only first versions, in which case neither log-shipping nor replication can handle it, unless there's some way of eliminating updates with one of those two (there wasn't last time I looked seriously, but that was in 2009 or earlier). But I am sure there must be a better way than using SSIS (than which nothing can be worse). .
Tom
- RatanDeep Saha - Wednesday, August 22, 2018 5:09 PM
Ah... Ok. Wasn't sure what you were driving at.
We used to use SAN Snapshots to do such a thing. Basically, it made a copy of prod on a different "server" and did so in about 6 seconds and that was way back in the year 2005. It also served as a real handy place to load test solutions before they got to production.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply