Hi,

  I am importing external data(oracle) to local sql server DB thru linked server. This is actual replica of the external data(not normalized). I need to run this import for every 2 minutes. Its working well.
 Now, I need to split this data into child tables to fulfil the normalization. For that, I have created a C# class which gets all records(newly added/modified) and passing these values to stored procedure as parameters.
The SP will check if that record exists, it will update or it will insert into my local tables.

I made DLL and I need to run this DLL for every 2 minutes(this is my client requirement). If I have 1500 records, this import consumes 10 minutes(to establish connection, retrieve, and calling SP).
But this is more time. Its not acceptable.

How can I reduce the time? Is it correct way what I am doing or any other way is possible?

Pls suggest me an idea.

Thanks in advance

Hi,

How are you importing data? Can you use DTS or Integration Services [if u are using sql2k]? If so, you will have greater control on how the data is transformed in the target database. So right at the time of importing, you can populate appropriate tables and you will not need any external program to do it !!

Anubhav Kumar

Hi,
 Tx for the reply. How can I pass value to a query that is to be executed in DTS?
I want to retrieve all the records from external database based on the last updated date time.

means, the query should look like

select * from oracledb.table where update_dtm > ?

How can I do this in DTS?

Hi,

Please refer to books online, on How to use variables in DTS packages. It will give you clue to proceed. Additionally, U can control many things by writing a VB module in DTS and calling it from within. Please refer to documentation for this. Currently I cannot share my code with you. But it is not that much tough!!

Anubhav Kumar