Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Row count difference between flat file source and sql table


Row count difference between flat file source and sql table

Author
Message
jdbrown239
jdbrown239
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 390
I have an SSIS package that imports flatFile data to a table. The package has error output for flat file and oledb destination. The execution results show the flafile to have 176,863 rows and writes the same number to the destination but when I query the table there are only 93,456 rows in the table. Can anyone explain this?

[Source - scorecardts_tab [1]] Information: The total number of data rows processed for file "C:\Scorecardapp Test\scorecardts.tab" is 176864.
[FlatFile Errors [2210]] Information: The final commit for the data insertion in "component "FlatFile Errors" (2210)" has started.
[FlatFile Errors [2210]] Information: The final commit for the data insertion in "component "FlatFile Errors" (2210)" has ended.
[Destination - SCORECARDAPP_TEST_NEW [1094]] Information: The final commit for the data insertion in "component "Destination - SCORECARDAPP_TEST_NEW" (1094)" has started.
[Destination - SCORECARDAPP_TEST_NEW [1094]] Information: The final commit for the data insertion in "component "Destination - SCORECARDAPP_TEST_NEW" (1094)" has ended.
[Scorecardapp Table Errors [1652]] Information: The final commit for the data insertion in "component "Scorecardapp Table Errors" (1652)" has started.
[Scorecardapp Table Errors [1652]] Information: The final commit for the data insertion in "component "Scorecardapp Table Errors" (1652)" has ended.
[SSIS.Pipeline] Information: Post Execute phase is beginning.
Progress: Post Execute - 0 percent complete[Source - scorecardts_tab [1]] Information: The processing of file "C:\Scorecardapp Test\scorecardts.tab" has ended.
Progress: Post Execute - 100 percent complete[SSIS.Pipeline] Information: "component "Destination - SCORECARDAPP_TEST_NEW" (1094)" wrote 176863 rows.
[SSIS.Pipeline] Information: "component "Scorecardapp Table Errors" (1652)" wrote 0 rows.
[SSIS.Pipeline] Information: "component "FlatFile Errors" (2210)" wrote 0 rows.
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8348 Visits: 19490
Are there duplicate rows (by PK) in the source data?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
jdbrown239
jdbrown239
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 390
No
I have a sequential numbering for the first column (APPNUMBER) and they are all in the table as well as the flat file.
jdbrown239
jdbrown239
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 390
I think I know why the count does not match.

When I exported the data from the sql table (93,456 rows) to a txt file and then imported it to excel the count was 176,863 so all of the data is there. My guess is that sql stores data more efficiently than a (.csv,tab or txt) file. That would explain why the SSIS execution stated it wrote 176,863 rows. If anyone has ever seen documentation on this please share it.

Thanks for you help and suggestions!

Doug
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8348 Visits: 19490
jdbrown239 (4/10/2013)
I think I know why the count does not match.

When I exported the data from the sql table (93,456 rows) to a txt file and then imported it to excel the count was 176,863 so all of the data is there. My guess is that sql stores data more efficiently than a (.csv,tab or txt) file. That would explain why the SSIS execution stated it wrote 176,863 rows. If anyone has ever seen documentation on this please share it.

Thanks for you help and suggestions!

Doug


That sounds unlikely to me. 93,456 rows in SQL Server should equate to 93,456 in a text file and in Excel.

I would be looking VERY closely at what is going on. Check the original text file in an editor that gives you a row count. If the row count is 176,863, something has gone wrong in the export from SQL Server (or did you perhaps append rows to a file which was not empty?).


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
jdbrown239
jdbrown239
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 390
The original file has a row count of 176,864 and even though the row count from sql is 93,456 when I export to txt file and import to excel for row count it is 176,864. Any ideas why?
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8348 Visits: 19490
jdbrown239 (4/10/2013)
The original file has a row count of 176,864 and even though the row count from sql is 93,456 when I export to txt file and import to excel for row count it is 176,864. Any ideas why?


None.

Do not involve Excel if you can avoid it - stick with text files. Excel and SSIS do not always work well together.

Are you saying that you can export from a SQL table containing 93,456 rows to a text file, and that this text file will then contain 176,864 rows?

If so, stop right there and do some analysis - this should not be happening. I suggest that you use something like Notepad ++ rather than Notepad when checking the text file.

Find a row that is in the text file and not in SQL Server and work out why.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
jdbrown239
jdbrown239
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 390
After some more digging I don't think the problem is with the bulk load from the SSIS package. I think the row count in the database is the issue. Exporting the data showed me it was all there and the execution stated how many rows were written. I think the row count (Select column_name = count(*) from table_name) is giving the wrong count due to some sql bug even afetr I updated the stats on the table. Has anyone seen this before?
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24198 Visits: 37961
I have a problem with 176,864 rows (text file) -> 93,456 rows (SQL table) -> 176,864 rows (text file). Something does not add up here, but since we can't see what you see there really isn't much we can do.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Neeraj Dwivedi
Neeraj Dwivedi
SSC Eights!
SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)

Group: General Forum Members
Points: 887 Visits: 1319
Look for the line breaks in the export. That will cause the difference in count.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search