Error loading multiple CSV files in SSIS

Question

Post reply

Error loading multiple CSV files in SSIS

Viewing 5 posts - 16 through 19 (of 19 total)

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

frederico_fonseca SSCoach Points: 16154 More actions · Answer 1

why bother sometimes. basic sql stuff that should not need our help.

straight replace -

SET @FILEPATH = replace(@FILENAME, 'chartobereplaced_escapedifneeded', 'replacewith_escapedifneeded')

you can just try out until you get the replace correct - and that's it.

if required to replace others just do replace(replace(replace as many times as required

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 2

Bumping the post so that the post above shows up on the next page. This has been a long standing fault on this forum. I sure hope the get around to fixing this soon.

--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)

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 3

frederico_fonseca wrote:

Jeff Moden wrote:
If you look at the column headers of the file and compare them to the table, they don't match. We can't see your package to know what the mapping is. That would explain why it "works for some files but not for others". What to do about it in SSIS is beyond me because I don't use SSIS. There has to be a way for SSIS to read that column header line in the file and match it up with either the correct mapping or the correct table.
not quite that easy in SSIS - a flat file is defined with logical names associated to a column on the file - the HEADER of the file when its read is irrelevant - for SSIS the first column gets the name that was defined for THAT file definition. if number of columns on file is changed or if the position of a column changes on the file itself, SSIS does not know about it - so sometimes it works (e.g. does not give an error) and happily loads the data from the file onto the destination (but puts data on wrong columns)) or it fails completely (e.g. different number of columns on input file, or invalid datatype for the column definition within SSIS)
only way to process different layouts within SSIS is to either treat them as a single column and then process them in SQL (tricky) or use C# script to read file as CSV, and then do column name mapping to destination (which is easy to do), and address potential datatype issues on this load.

You've just given me additional reasons to be glad that I don't use SSIS. 😀

--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)

Zidar SSC Enthusiast Points: 126 More actions · Answer 4

There are other ways of importing text files. You can try MS Access and import CSV into a new table, then examine the data. It is much simpler than directly in the file. As for SSIS, I really do not understand why some smart people believe it does better job than other methods, good old bulk insert for example. MS SQL never had a good tool for import-export 9soem people get offended when I say import-export, that is supposed to be Extraction Transformation and Loading, you know.

Zidar's Theorem: The best code is no code at all...

Phil Parkin SSC Guru Points: 246959 More actions · Answer 5

Zidar wrote:

As for SSIS, I really do not understand why some smart people believe it does better job than other methods, good old bulk insert for example. MS SQL never had a good tool for import-export 9soem people get offended when I say import-export, that is supposed to be Extraction Transformation and Loading, you know.

Perhaps you'd explain how to consume the JSON output from a web service requiring OAUTH2 authentication using bulk insert?

Yes, SSIS can do that.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin