November 14, 2011 at 1:20 pm
I have uncovered an interesting issue in loading data to a table from a flat file.
The last row of data in the flat file is not being inserted into the table.
When I execute the package from BIDS, everything works fine. However, when executed from the server the last row does not get inserted.
I uncovered this issue in QA, so this is relatively stable code.
In both DEV (SQLK8R2 SP1) and QA (SQLK8R2 RTM) the packages complete without an error. The behavior appears across both dimension and fact table load packages. The load pattern is a simple open the flat file; add in PK Column using a derived column and then Fast Load the table. There is nothing fancy going on.
Has anyone seen this type of behavior before?
November 14, 2011 at 1:38 pm
Open it up in Notepad++ or something similar and confirm the last End of Row/End of File operators are there.
I've seen this before in mainframe outputs that directly include CR/LFs to the end of rows as row terminators and have it left off the end as an assumption to end of stream.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 14, 2011 at 2:11 pm
When I open the file in a Hex editor, I do see a line feed character {0A}, but that is all.
November 14, 2011 at 2:16 pm
michael.french 172 (11/14/2011)
When I open the file in a Hex editor, I do see a line feed character {0A}, but that is all.
Does it match end of row for the other rows? In SSIS, what's your row termination character(s) in the flatfile definition (go to advanced and take a look at the last column listed)? LF alone if it wants CR/LF will not end the row. It's allowed for multi-line entries in longer text structures that you're feeding into a larger VARCHAR().
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 14, 2011 at 2:27 pm
Yes, each row ends in {0A} and uses a column separator of {1F}. I have verified the connection Manager is set for the column delimiter on the last column is the {LF}.
Here are the last two rows of data from the flat file:
Offset 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
00000096 4D 4F 4E 44 41 59 0A 32 30 31 31 30 35 1F 37 1F MONDAY 201105 7
00000112 53 41 54 55 52 44 41 59 0A 32 30 31 31 30 35 1F SATURDAY 201105
00000128 31 1F 53 55 4E 44 41 59 0A 1 SUNDAY
In every case, Monday and Saturday get inserted and Sunday does not.
November 14, 2011 at 2:30 pm
michael.french 172 (11/14/2011)
Yes, each row ends in {0A} and uses a column separator of {1F}. I have verified the connection Manager is set for the column delimiter on the last column is the {LF}.Here are the last two rows of data from the flat file:
Offset 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
00000096 4D 4F 4E 44 41 59 0A 32 30 31 31 30 35 1F 37 1F MONDAY 201105 7
00000112 53 41 54 55 52 44 41 59 0A 32 30 31 31 30 35 1F SATURDAY 201105
00000128 31 1F 53 55 4E 44 41 59 0A 1 SUNDAY
In every case, Monday and Saturday get inserted and Sunday does not.
Yep, look at the ordering of the values. 1 SUNDAY does not have a row terminator.
You need to reorganize the data like this, unless this is fixed width:
Row 1: 00000096 4D 4F 4E 44 41 59 0A
Row 2: 32 30 31 31 30 35 1F 37 1F MONDAY 201105 7 00000112 53 41 54 55 52 44 41 59 0A
Row 3: 32 30 31 31 30 35 1F SATURDAY 201105 00000128 31 1F 53 55 4E 44 41 59 0A
Row 4: 1 SUNDAY
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 14, 2011 at 2:37 pm
Sorry, my fault in formatting.
The first set of data is the character offset. The next set of data is the Hex Values and the third set is the ASCII Values
Here are the last two rows of data from the flat file:
Offset || 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15||
00000096|| 4D 4F 4E 44 41 59 0A 32 30 31 31 30 35 1F 37 1F || MONDAY 201105 7
00000112 || 53 41 54 55 52 44 41 59 0A 32 30 31 31 30 35 1F || SATURDAY 201105
00000128 || 31 1F 53 55 4E 44 41 59 0A || 1 SUNDAY
I added the double pipes to separate the three sets of data.
November 14, 2011 at 3:03 pm
Ah, sorry, didn't realize that was your editor's output instead of the raw data in the file.
Can you post a screenshot of the advanced tab in the flatfile editor? I'm wondering if your columns aren't lining up. Also, can you post the last three rows of the raw data in the file, instead of from the editor? Just crack it in notepad and copy/paste with the code="plain" tag.
Here's the reason: You'll notice the difference between those three lines, they are lined up with different column mechanics in the 'text' area.
According to the rules of 0A being the Row Terminator and 1F being the column terminator, the data in the file should look like:
<something> Monday
201105 7 Saturday
201105 1 Sunday
I'm curious what Monday looks like. You're right though, unless somewhere in here one of the columns got stripped or is offset somehow, that row SHOULD be pumped in.
Another thing to check is the destination OLEDB object in SSIS. Confirm that there's no error controls inside the component that are 'ignore errors' or 'redirect row'. We just want to make sure it's not actually erroring on FK constraint or something similar and you're just not being informed.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 14, 2011 at 3:16 pm
Screen Shot Attached and the error output is set to fail component
Raw Data Here:
2011050UNKNOWN
2011054WEDNESDAY
2011055THURSDAY
2011056FRIDAY
2011053TUESDAY
2011052MONDAY
2011057SATURDAY
2011051SUNDAY
November 14, 2011 at 4:02 pm
michael.french 172 (11/14/2011)
Screen Shot Attached and the error output is set to fail componentRaw Data Here:
2011050UNKNOWN
2011054WEDNESDAY
2011055THURSDAY
2011056FRIDAY
2011053TUESDAY
2011052MONDAY
2011057SATURDAY
2011051SUNDAY
My apologies. I'm afraid I no longer have a clue what's wrong with this. That data, the hex information, and everything else looks good.
That's one heck of a glitch you've got.
EDIT: I'll be calling in backup shortly.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 15, 2011 at 7:54 am
The file is marked as delimited. What type of delimiter are you using?
And do you have multiple files that this issue is showing on or just this one?
November 15, 2011 at 8:01 am
Each row ends in {0A} and uses a column separator of {1F}.
This behavior appears to be happening across the board with both my Dimension and Fact Table Loads.
I load one month of data at a time and it is even happening across multiple months.
I rarely look for a silver bullet to fix things, however in this case I think it is possible. I would also accept user error on my part.
I very much appreciate everyone looking at this.
November 15, 2011 at 8:47 am
Sorry, I don't know Hex, so I can't translate {1F} into a character I would understand.
I will suggest changing your connection manager from a Delimited to a Ragged Right (you'll have to re-input all your column information on the Advanced tab).
Delimited and Fixed Width have always given me trouble. Give me a good ol' Ragged Right any day and it gets all the information, even on delimited files.
November 15, 2011 at 9:17 am
The hex character {1F} is the ASCII equivalent of the Unit Separator and is a non-printable character hence you see a small square box in the raw data. The files are from a third party source so I do not have control over the format of the file, I just consume it.
When I tested Ragged Right, things really got weird. It lost the fact that the there were columns at all and when I tried to put them back in, it saw the Unit Separator Character as valid data and not column delimiters. This also caused the numbers to not be recognized. Ragged Right made the first two columns a fixed width and then only the last one as delimited.
Am I missing something with Ragged Right?
November 15, 2011 at 12:02 pm
Try turning the Unit Separators into filler columns.
I knew it was going to lose the column data. I thought I'd mentioned it, but I apologize if I didn't.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply