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 execute 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?

  • 2
    why not a linked server?
    – swasheck
    Nov 6 '12 at 19:07
  • 1
    IS this a straight data transfer inside SSIS (Source -> Destination) or are you performing operations on the data in flight?
    – billinkc
    Dec 6 '12 at 22:42

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', 

or even query the source using OPENROWSET

SELECT CustomerID, CompanyName
   FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',


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

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


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.