The case of the missing records (SSIS Fast Load losing records?)

Question

The case of the missing records (SSIS Fast Load losing records?)

gnewcom

SSC Veteran

Points: 227
More actions
October 3, 2008 at 8:53 am

#198818

I have a package that has an OLE DB source that queries a table and returns about 1.6 million rows, after which there is some processing on these records and they are then inserted into a staging table. The logic in the package passes ALL source records into the target OLE DB destination. After the package runs, I query the target table for a specific record, and the record is missing but the record was in the source table.
So, I debug the package by adding a WHERE clause in the OLE DB source to constrain just to the record in question, run the package and the package passes 1 row through all the logic to the OLE DB destination. After the package runs to completion, I query the target table for the specific record, and the record IS in the target table.
My source is SQL Server 2005, and my target is SQL Server 2005 (same database, different table). The OLE DB source and destination are using the same connection manager which is configured using the OLE DB\SQL Native Client provider.
OK SSIS gurus, any idea what is going on here?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

tshw SSC Enthusiast Points: 104 More actions · Answer 1

I have the same issue... my source is a complex database view and when I filter the view to return less records, the records are there, but when returning more data, there are about 1000+ records missing from 58,0000+ total output.

2005 server with SP2 as source.

I workaround the issue by using "SQL command" as data access mode instead of "table or view".

Is this a known bug?

tony c-440037 Mr or Mrs. 500 Points: 577 More actions · Answer 2

Hi

Could it be that the destination task is aggregating your records?

I noticed today that my data flow contained a flag column that along with other columns made the record unique.

I dropped this flag (which created duplicate records, no pk or unique index on table) before the fastload insert into my table and noticed the duplicate records were aggregated in the table.

I had planned to aggregate the records but forgot to but ended up with the desired result anyway.

P Jones SSChampion Points: 12361 More actions · Answer 3

Is it set up such that one record failing to import will lose the whole batch that that record is in?

I've had this before and either changing to "slow load" mode or putting more error trapping and reducing the batch size in the destination component is the solution.

Do some googling and there's info available - that's how I found it but I haven't more details at present.

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 4

what is the transformation or process that you are doing after getting the data from the source table.

what will be the error handling you are using when there are some error records in getting from source and in the transformation process.

is there any row locks are happening in the source table or these records might have changed when the ssis package is running.

tshw SSC Enthusiast Points: 104 More actions · Answer 5

tshw (12/23/2011)
I have the same issue... my source is a complex database view and when I filter the view to return less records, the records are there, but when returning more data, there are about 1000+ records missing from 58,0000+ total output.
2005 server with SP2 as source.
I workaround the issue by using "SQL command" as data access mode instead of "table or view".
Is this a known bug?

I was too haste to say my issue was same as original poster.

My issue was simply sourcing the data from the database. As posted, I can work around the issue by simply using "select * from <view>" instead of selecting the view from the dropdown list of tables and views.

xpuneet Valued Member Points: 59 More actions · Answer 6

I am facing the same issue, In my scenario, I am parsing a XML file ( 60k records) and inserting records in staging tables then DFD will run and Insert the records in Destination table (Source and Destination tables have same columns).

I am running this scenario 200 times to generate the load and for performance testing.

Surprisingly I have Noticed that one table in destination missing fewer records only at one instance and ran 199 times perfectly.

I am not able to find out the root cause of it.

DFD structure.

1.Source(Fetch the record using SQL Command Stored procedure.)--> Lookups-->Destination ( Oledb Destination)

Thanks,

Puneet