May 10, 2019 at 6:17 am
I have a daily file that I need to consume that has 10,000,000+ rows; it is a human readable report. The reason it needs to stay in order is there are multiple rows for each record. I have the code written that parses the file; as a work around during testing I used Ultra Edit to add a row number before bulk insert.
Eventually I'm planning on using SSIS to process this file. I believe there is something in SSIS that can add a row number? Seems like at a previous employer the programmers used C# code to insert a row number before import on the files where row order needed to be retained; I'm not sure though had the luxury there of some great programmers.
I'd appreciate suggestions, thanks.
May 10, 2019 at 9:35 am
Do you have a column you can order my in the data that will retain the order of the file? If not, then yes, using a Script Component instead would seem the "best" idea; as otherwise SSIS will treat the data as unsorted, meaning that it may not retain it when it inserts the data into the database (certainly just using an IDENTITY property won't ensure that the order is maintained on it's own).
Considering that we have no details on the file then that's as much advice I can offer. Considering it's got 10M+ rows, I assume it's a text file, rather than an Excel File, so I suggest looking at the StreamReader as a start point: How to: Read a Text File One Line at a Time (Visual C#)
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 10, 2019 at 12:46 pm
Unfortunately there is no way with the current file to keep order. When I did my testing before bulk insert I used Ultra Edit to add a row number. That was a manual process and was very slowwwwwwwwww. I'll look at the link you provide, thanks.
May 12, 2019 at 4:46 pm
As mentioned, I have a 12,000,000 line txt file; for testing I used a text editor (Ultra Edit) to add a row number. Obviously in a production situation would not want to do that.
I found this link on the web: https://hansmichielscom.wordpress.com/2013/06/20/how-to-add-a-rownumber-to-a-data-flow-task-in-ssis-2012/
Using SSIS, I was able to add the row number and the text file had the same order as my Ultra Edit row number.
QUESTION: I know using bulk insert the text file is not imported in row order but it looks like when SSIS is used on the flat file it keeps row order???? Is this true? Thanks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply