Flat File connection configuration
-
I know I have done this in the past, but for the life of me I cannot remember how. We have address records that are coming from running a stored procedure whereas the fields are separated by double quotes. The issue is with any address that might contain a comma. If there is a comma, the field gets split into the next. This is an example
How do I stop that behavior? Is it in the General settings of the Flat file connection?
Thanks
Brad
-
Set the value of the Quote Identifier to
"in your connection manager; then refresh the columns.Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
If double quotes are field delimiters (which is a bit weird), why are you worried about commas?
I think maybe that you explained it slightly incorrectly and should set the text qualifier to " and keep the , as delimiter.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
You want the box highlighted in the below image (which says
<none>), and need to put in":Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
I believe the stored procedure implicitly returns the double quotes to capture the field as there are some addresses with commas. What is the best practice in SSIS to handle that situation? If we were to remove the CHAR(34) from the select statement and I am created a csv, how does SSIS know that comma in the actual data is not a field separator? For instance, this first record in ADDR2 is: 3M CTR, BLDG 275-5E-05 and has a comma.
-
It wouldn't.
No one suggested removing the double quotes from the file.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel - Brad Allison wrote:
What is the best practice in SSIS to handle that situation?
Tell your connection manager your text is Quote Identified (with the
"character); it's an simple as that. Then a string like:1,31/01/2019,"Steve, went to the shops"would be read as 3 different values,1,31/01/2019andSteve, went to the shops. The,in the last value won't be perceived as a delimiter, as it's inside the text qualifiers.It really is that simple.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Brad Allison wrote:
I believe the stored procedure implicitly returns the double quotes to capture the field as there are some addresses with commas. What is the best practice in SSIS to handle that situation? If we were to remove the CHAR(34) from the select statement and I am created a csv, how does SSIS know that comma in the actual data is not a field separator? For instance, this first record in ADDR2 is: 3M CTR, BLDG 275-5E-05 and has a comma.
For SSIS - you do not have to quote the columns in the stored procedure. Setting the connection manager to use a double-quote as the text qualifier will output all text columns quoted. If your procedure is already quoting the text columns - then you would not utilize the text qualifier on the connection manager and instead just identify the field delimiter.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply