What are the differenet approaches I can use for uploading data to SQL Server destination from a text file in SSIS? Please explain. Thanks in advance.
The only approach I know is to create a fiat file connection manger and a some kind of.destnstion connection manager to SOL Server, and a data flow to load the text file.
1 Like
They are ususally "fragile" IME
They are only fragile when someone on the sending side decides to change the format...adding new columns/fields or increasing data lengths, etc...
These same issues would cause the same problems if you are getting data directly from another database - except adding new columns. Adding new columns in the source system can and will cause problems if your code is using SELECT * due to binding issues...
I think the OP's question is probably more related to the process and not the mechanics...in other words should the OP load the file to a staging table then load to final...do you keep a copy in the staging tables and identify a new file by some unique identifier or truncate the staging table each load?
If that is actually what is being asked...then my answer is the highly technical term: It Depends...
Yes, agree that applies in all instances (although if using Native format in BCP it does at least fail with a half-useful message).
What was in my mind was CSV with embedded commas, or quoted-data that has embedded commas, or a line-break (regardless of whether quoting is correct) which tends to fail.
Even in systems with tight specs and "straightforward" requirements I've known them run fine for years and then all-of-a-sudden some rogue characters creeps into the data ... and mucks up the TEXT transfer file.
Pity the person that invented CSV didn't choose a binary or escaped format instead. heck .. or even a markup-format