SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help with SSIS data insertion mystery


Help with SSIS data insertion mystery

Author
Message
Jonathan Marshall
Jonathan Marshall
SSC-Addicted
SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)

Group: General Forum Members
Points: 446 Visits: 373
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
sqlbi.vvamsi
sqlbi.vvamsi
SSC Veteran
SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)

Group: General Forum Members
Points: 240 Visits: 412
Perhaps header row is also inserted as data row into tables.As that C# program begins from -1 there'll be 1 row difference.
demonfox
demonfox
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2187 Visits: 1192
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 Ermm
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