SSIS - CSV Row being dropped

Question

SSIS - CSV Row being dropped

Meatloaf

SSCrazy

Points: 2564
More actions
November 17, 2015 at 3:54 pm

#322675

Hello,
I am using a Data Flow uploading a CSV into a table. Rows import as expected, unless the last row is incomplete, in this case the row is being dropped! Is there a known way to solve this problem? I have tried adding a text qualifier but this did not work. Any advice would be greatly appreciated.
My data looks like this when the row gets dropped:
114071776412
214031776421
314031776422
414072776423
514121(No Data)(No Data)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Disclaimer: I am NOT an SSIS user. I do this type of stuff using T-SQL and I'm not even sure that I know how to even spell "SSIS". 😀

Disclaimer notwithstanding, I believe there's a "ragged right" option for the file type that will do the trick for you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Meatloaf SSCrazy Points: 2564 More actions · Answer 2

Hi Jeff, thank you for the tip! However, this did not solve the problem. Looking at my CSV file in notepad, the last row looks like this:

7,1412,1

Instead of:

7,1412,1,,

Of course when I manually add the commas it works. IS there a way to insert the data that is available?

Jack Corbett SSC Guru Points: 184388 More actions · Answer 3

I can't seem to duplicate the problem.

Can you attach the SSIS package?

I have attached the file I used to try to duplicate the issue. I had to upload it as .txt because the site doesn't allow you to upload .csv.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Brandie Tarvin SSC Guru Points: 172918 More actions · Answer 4

My best suggestion would be to use a Data Flow task to insert various items to evaluate the final row, then used Derived Column tasks to add the final blank columns and delimiters if the last row is cut off.

The issue, I believe, is that SSIS isn't seeing a row / file terminator on that last row, so doesn't know how to handle it.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.