Insertion / updation problem in SSIS

“I have a scenario where i am trying to insert 200,000 lac records & update 200,000 lac record in destination table using SISS Package (SQL SERVER 2005) but I am not able to neither update nor insert the records . while executing the package its not showing any error also . what could be the problem ? “

We have business logic in Package creation 1) Insert New records and 2) Update Existing Records using the follow Data flow diagram

For update we are using OLEDB command, for insert we are using OLEDB Destination.

We are using merge join for spliting record into insert and update.

Perhaps there is blocking on the destination table. This can often happen if you're attempting 2 operations simultaneously.

Execute sp_who2 to see if there's any blocking going on.

-Jamie

|||

is there any other solution for this problem, is this not possible to run for achive both insertion and updation in the same package? if i try to run the package for less records, it is succeded, if i try to run the package for more records, then same problem coming again and again

Thanks & Regards

S.Nagarajan

|||

In that case I am even more sure that blocking is a problem. Did you bother to execute sp_who2 like I advised?

There is an easy fix to this problem. Continue to do the insert but push teh adta to be updated into a raw file. You can then use the contents of the raw file in another data-flow in order to do the update,.

-Jamie

|||I ran sp_who2 and found the blocking, how can i remove the blocking, is there any query to remove the blocking. I couldn't get your solution clearly, please brief me your alternate solution.|||

Do you know what raw files are? If not, go away and study them. When you have finished read #1 here: http://blogs.conchango.com/jamiethomson/archive/2006/02/17/2877.aspx

It describes a different scenario for using raw files but the usage is the same.

-Jamie

|||

We have completed upto move to flat destination files using raw files, could you please help to move this flat files data (for update) to database. what logic we need to use? is it required to add dataflow diagram after raw file destination component

|||

Please stop writing the same question in multiple threads simultaneously. People are here to help and don't want to waste their time clicking through and reading the same thing more than once.

-Jamie