SSIS Package Not Writing all the Rows to OLE DB Destination Although shows the complete numbers on dataflow Path

Question

SSIS Package Not Writing all the Rows to OLE DB Destination Although shows the complete numbers on dataflow Path

BackT0Work

Mr or Mrs. 500

Points: 525
More actions
April 19, 2011 at 1:20 am

#389314

my SSIS package loads data from excel file to SQL server table. The excel file has 5000 rows.
When I execute the Package the number of rows shown in the data flow path is 5000 leading to oledb destination. No errors. everything in green.
But when I check the sql table record count its only 100.
(Note: I have used - table or view - Fast Load. I have error output redirected to another table. Nothing is entered there as error.)
when I delete oledb destn and place Flat file destintion, all 5000 records are loaded.
When I create a new table(SSIS defined) from oledb destn, instead of original table, all 5000 rows are loaded. Again if I rename this new table to original table it loads only 100 rows.
The original table doesnt have any triggers/constraints.
What might be happening here?
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

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

brian118 Hall of Fame Points: 3506 More actions · Answer 1

BackT0Work (4/19/2011)
my SSIS package loads data from excel file to SQL server table. The excel file has 5000 rows.
When I execute the Package the number of rows shown in the data flow path is 5000 leading to oledb destination. No errors. everything in green.
But when I check the sql table record count its only 100.
(Note: I have used - table or view - Fast Load. I have error output redirected to another table. Nothing is entered there as error.)
when I delete oledb destn and place Flat file destintion, all 5000 records are loaded.
When I create a new table(SSIS defined) from oledb destn, instead of original table, all 5000 rows are loaded. Again if I rename this new table to original table it loads only 100 rows.
The original table doesnt have any triggers/constraints.
What might be happening here?
Thanks.

Can you place a rowcount component before the OLD DB destination and check it's value?

Also, have a look at the execution results pane in BIDS... [SSIS.Pipeline] Information: "component "OLE DB Destination" (nn)" wrote xx rows.

Regards

Regards
Brian Ellul
----------------------------------------------------------------------------------
Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
- Albert Einstein -

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

How do you check the rowcount in the destination table?

You didn't place a top 100 at the beginning, did you? (SSMS automatically adds TOP 1000 when you script out a select statement)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Darren Lesnever SSC Rookie Points: 33 More actions · Answer 3

Make sure you don't have the error output for the OLE DB destination set to ignore errors.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

Darren Lesnever (1/15/2013)
Make sure you don't have the error output for the OLE DB destination set to ignore errors.

Almost 2 years old this thread, so I hope the OP has already solved his issue 😉

Solid advice though.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP