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

Help with SSIS data insertion mystery Expand / Collapse
Author
Message
Posted Thursday, August 2, 2012 8:19 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 24, 2014 6:10 PM
Points: 98, Visits: 297
Scenario
Used SSIS bulk insert to loop through a series of flat files and load them into a database.
The package returned success on all files loaded into the database.
Ran a count of rows per file loaded. A C# program created by a co-worker was created to verify the count in the actual raw file to the database count. The program starts the count off @ -1 to discount the file header. When comparing the results in the database to the raw files. Some of the counts in the database where 1 row less then the raw file. About 10% of the results were this way. The filecount of the raw files checks for nulls in the file.
I do not have access to the log for the job because I did not put in an increment every hour into the log creation. I do not have access to the data files because of the sensitivity of the data. I cannot think of an explanation at this juncture. Any suggestions.

Marshall
Post #1339201
Posted Saturday, August 4, 2012 10:00 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 28, 2014 9:05 AM
Points: 54, Visits: 359
Perhaps header row is also inserted as data row into tables.As that C# program begins from -1 there'll be 1 row difference.
Post #1340235
Posted Sunday, August 5, 2012 1:34 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:15 PM
Points: 1,129, Visits: 1,163
Jonathan Marshall (8/2/2012)
Scenario
The filecount of the raw files checks for nulls in the file.
Marshall


I don't think counting a null would be an issue , It depends how are you counting a row.As null counting is ignored by sql server on a column basis. The c# program may have been counting based on row breakers , probably the new line, may be one of your row is not inserted .If you are having ignore on failure marked, then , i think the C# code may have been adding something based on the raw file data structure.


~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one
Post #1340240
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse