I need to dump Access MDB files to SQL Server 2005. No extract and transfer,
just transfer the raw data. However, the mdb files can be reaching the
limitation of 2G.

Is there any benifit (especially performance) to use SSIS to load the MDB
files? Or a simple SQL statement (with openquery to open the oledb Jet
database, 'insert into '+@tablename + ' select * from openquery(....)) will
be the same?

there is a big difference !!!
SSIS use a bulk insert command which is far better then a simple insert
command.

you have to setup correctly the commit batch size.
for example, if you have 1 million of rows, setup the batch size to 10 000
insert 10 000 rows, commit this batch, then load the next 10 000 rows....
instead-o creating 1 transaction for the million of rows, multiple smaller
transaction are created.
this reduce the time taken to load the data, reduce the log file size
needed.

so the answer is clear: use SSIS.

"nick" <nick (AT) discussions (DOT) microsoft.com> wrote

Yes, transaction batch is the different. However, how SSIS comparing with
bulk insert? Anyway to implement a kind of bulk insert using TSQL that read
ACCESS MDB files (via openrowset?)...

"Jéjé" wrote:

openrowset its not a good option.
specially if you have to load a large volume of data.

the import data wizard will create the SSIS package for you with the bulk
insert tasks.
the TSQL version of the bulk insert works when the source is a flat file.

maybe a linked server could be used.

"nick" <nick (AT) discussions (DOT) microsoft.com> wrote