Hi, so I'm new to SQL Server and I recently started at a new job. We currently have about 50 jobs that run throughout the night that pull from a linked server and insert into our data warehouse.
Most of the jobs take a few minutes, but there are some that take several hours. The jobs are set up through SQL Agent as T-SQL jobs. We truncate the tables in our data warehouse daily and then load the records. Is there a more efficient way to do these loads in SSIS or a way to only load the changes that occur from the previous day?
Here's an example:
insert into dbo.claimdetail fields
select fields
from LinkedServer.prod1.dbo.clmdet
where exists (select * from LinkedServer.prod1.dbo.ClaimsToPull
where blah blah)