Optimizing SSIS Packages

Question

I have a SSIS package that loads data in an Access DB to tables on a SQL Server DB. Currently I double click the package and excute it. There are times when there is a lot of data to load. My question is there a way I can optimize the SSIS package so that it loads faster?

Answer 1

The largest thing I look for when trying to optimize an ssis package is data sorts. Since these operations have to complete before the data stream can continue they can be very costly for performance. Without seeing or knowing much about the package its hard to give much more than general advice.

Answer 2

You'd have to optimize the source and I'm not sure what optimizations are available for Access - a quick Google search reveals this and something about indexes here but I'm honestly clueless about how Access indexes work.

It occurs to me that you could create a linked server to the Access database and then just schedule a job to run a query against the link.

EXEC master.dbo.sp_addlinkedserver 
    @server = N'MyAccessSource', 
    @srvproduct=N'', 
    @provider=N'Microsoft.Jet.OLEDB.4.0', 
    @datasrc=N'C:\my\accessdb.mdb';

or even query the source using OPENROWSET

SELECT CustomerID, CompanyName
   FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
      'C:\my\accessdb.mdb';Table);
GO

or

select * from 
  openrowset('Microsoft.ACE.OLEDB.12.0', 'C:\my\accessdb.mdb',
  'select * from Table');
GO

I guess this would save you the overhead of firing up SSIS packages and worrying about data flow tasks.