Sql-server – Optimizing SSIS Packages

sql-server-2008ssis

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?

Best 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.

Related Question