ODBC Data

[Iainn]

Hi all,
Could someone tell me if it is possible, and if so, how, to filter out records from an ODBC data source which already exist within my SQL server database. Every hour my dts package is brining over 50000 rows from the ODBC data source into a temp table, working on the data locally and filtering out only the new records to insert into the SQL Server Table, which could be as little as 5!!! The system grinds to a halt while this process is running. Any ideas???

Nicka

 

[DrewConn]

When you say it works on the data locally, do you mean on your desktop PC rather then on the server?

If so , why don't inport the new hourly data onto the server in a temp table in insert the unique records into your main table.

 

[Iainn]

Sorry, when it downloads from the ISAM database it downloads into a temp table on the server and works on the data there. I was just wondering if there was any way to filter out records that already exists in the SQL Server table on the transformation stage of the dts package as you would using 'where not exists(...)' in an SQL query using 2 SQL tables.

 

[theguru97321]

Not sure if this would be any faster...

But you could query your 'local' database first, get all the results into an ARRAY.

Then query the 'ISAM' with "WHERE results ne '$result[0]' AND results ne '$result[1]'....

using a FOREACH to create a $sql_query variable.

if ($query_result_from_local[0])
{$sql_query = "AND ";}
foreach $result(@query_result_from_local){
$sql_query .= "results ne '$result' AND ";
}
Get it??? again, not sure if this would be any faster. but it should lag your local server.