Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Row count difference between flat file source and sql table Expand / Collapse
Author
Message
Posted Tuesday, April 9, 2013 2:02 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:14 PM
Points: 133, Visits: 281
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.
Post #1440549
Posted Tuesday, April 9, 2013 2:22 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:14 AM
Points: 4,977, Visits: 11,669
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1440561
Posted Tuesday, April 9, 2013 2:40 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:14 PM
Points: 133, Visits: 281
No
I have a sequential numbering for the first column (APPNUMBER) and they are all in the table as well as the flat file.
Post #1440567
Posted Wednesday, April 10, 2013 6:58 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:14 PM
Points: 133, Visits: 281
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
Post #1440784
Posted Wednesday, April 10, 2013 7:04 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:14 AM
Points: 4,977, Visits: 11,669
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1440787
Posted Wednesday, April 10, 2013 8:40 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:14 PM
Points: 133, Visits: 281
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?
Post #1440851
Posted Wednesday, April 10, 2013 9:13 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:14 AM
Points: 4,977, Visits: 11,669
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1440874
Posted Wednesday, April 10, 2013 11:00 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:14 PM
Points: 133, Visits: 281
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?
Post #1440947
Posted Wednesday, April 10, 2013 11:42 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:50 PM
Points: 23,045, Visits: 31,570
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.



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)
Post #1440956
Posted Thursday, April 11, 2013 7:00 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 1:21 PM
Points: 809, Visits: 1,160
Look for the line breaks in the export. That will cause the difference in count.
Post #1441224
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse