Why doesn't SSIS OLE DB Command transformation insert all the records into a table?

I have an SSIS package that takes data from Tables in an SQL database and insert (or update existing rows) in a table that is another database.

Here is my problem, after th开发者_运维知识库e lookup, I either insert or update the rows but over half of the rows that goes into the insert are not added to the table.

For the insert, I am using an Ole Db Command object in which I use an insert command that I have tested. I found out why the package was running without error notification but still not inserting all the rows in the Table.

I have checked in sqlProfiler and it says the command was RCP:Completed which I assume means it supposedly worked.

If I do the insert manually in sql management studio with the data the sql profiler gives me (the values it uses toe execute the insert statement with), it works. I have checked the data and everything seems fine (no illegal data in the rows that are not inserted).

I am totally lost as to how to fix this, anyone has an idea?

Any specific reason to use OLE DB Command instead of OLE DB Destination to insert the records?

EDIT 1:

So, you are seeing x rows (say 100) sent from Lookup transformation match output to the OLE DB destination but only y rows (say 60) are being inserted. Is that correct? Instead of inserting into your actual destination table, try to insert into a dummy table to see if all the rows are being redirected correctly. You can create a dummy table by clicking on the New... button on the OLE DB destination. It will create a table for you matching the input columns. That might help to narrow down the issue.

EDIT 2:

What is the name of the table that you are trying to use? I don't think that it matters. I am just curious if the name is any reserved keyword. One other thing that I can think of is whether there are any other processes that might trigger some action on your destination table (either from within the package or outside of the package)? I suspect that some other process might be deleting the rows from the table.