Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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

2 Answers 2

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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