June 4, 2012 at 8:08 am
Jeff Moden (6/4/2012)
opc.three (6/3/2012)
BTW... didn't need to do anything additional in the T-SQL for handling the ragged right nature of the file. 😉
You ought to put that one in the brochure in the section that talks about file formats that are not ragged right, but under cons. No negative feedback when a column that was supplied yesterday is not supplied today. I am not so sure that is a good thing in all circumstances :ermm:
Even SSIS has to load it to make that determination, Orlando. 😉 Since it was an expected condition, I could ignore the supposed fault. If it were not an expected condition, I'd do like any good programmer would do... test for expected conditions. Yep... a little extra code that SSIS already tests for.
Fair point. You can always add code to account for it. In fact, if the file were expected to be flat, truly flat, then you could simply have BULK INSERT parse the file into columns for you. Speaking of BULK INSERT, to answer your earlier question, I do not use BULK INSERT where possible because it relies on the database engine to have file system access. Backups and restores, and FILESTREAM I suppose although I have not implemented it in production, might be the only places where I willfully access the file system from the database engine.
Will SSIS allow you to NOT test for it if it IS an expected condition? I believe the answer is "yes" but you have to select "Ragged Right".
No, it will not allow you to bypass a column. SSIS is quite strict when it comes to the defined data sources matching reality at runtime. This is both a source of frustration for folks new to SSIS and a source of comfort for those that rely on the behavior. In SSIS terms "Ragged Right" is actually a form of a fixed-width flat file. It's a quite poorly named "file type" in my opinion. For those coming from DTS it was a true shock to the system as DTS was quite tolerant of variances. It would have had no trouble parsing the data lines in this example, it would have said "oh, missing delimiter, not a problem, fill those columns with NULL and move on parsing the file...not SSIS. As an aside, the strictness of SSIS' parsing methods play directly into performance. The less corner-cases the parsing routines need to check for the leaner they are and therefore the faster they perform. The old Flexibility vs. Performance argument again.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing post 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply