I need to periodically import a (HUGE) table of data from an external
data source into SQL Server, with the following scenarios:

1- Some of the records in the external data source may not exist in
SQL.
2- Some of the records in the external data source may have a different
value at different imports, but this records are identified univocally
by the same primary key in the external datasource and in SQL Server.
3- Some of the records in the external data source may be the same in
SQL.

Due to the massive volume of the import, I would like to import only
the records which are different from what I have in SQL Server (cases 1
and 2 above)

I thought of making a query with a left outer join between the data in
the external data source table (SOURCE) and the data in the SQL Server
table (DESTIN). The join is done on the primary keys. One of the WHERE
conditions will be that the value in SOURCE is different from the value
in DESTIN.

The result of this query would be exactly what I need to import.

How to do this in SSIS???
I couldn't figure out how to join tables in different data sources yet.

Hello cgian31,


Have you had a look at the Merge Join?


This component should do exactly what you want.

That said it will require that you bring in ALL rows from both sides of the
join before doing the joining.


Have you looked at linked severs?


Allan

I think linked servers is not a possibility since the source is a MS
Access file.

The Merge Join needs the data to be sorted, which is really
discouraging in terms of performances...

But thanks anyway!

Allan Mitchell ha scritto:

Hello cgian31,

Access can be a linked server. have you looked at OPENDATASOURCE()?

Yes Merge Join requires a sorted input but you can do that on the source
and flag the Source Adapter as the data is already sorted.

Allan

It sounds great, thank you.

the database is running on a win 2003 64 bit machine, when I try to use
opendatasource it mentions that oledb.jet.4.0 is not registered on this
pc.
Therefore it seems i cannot use it.