Flat File connection configuration

  • Brad Allison

    Hall of Fame

    Points: 3529

    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

    Annotation 2019-12-03 064724

    How do I stop that behavior?  Is it in the General settings of the Flat file connection?

    Thanks

    Brad

  • Thom A

    SSC Guru

    Points: 98458

    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.

  • Phil Parkin

    SSC Guru

    Points: 243849

    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Thom A

    SSC Guru

    Points: 98458

    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.

  • Brad Allison

    Hall of Fame

    Points: 3529

    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.

    • This reply was modified 1 week, 1 day ago by  Brad Allison.
  • Phil Parkin

    SSC Guru

    Points: 243849

    It wouldn't.

    No one suggested removing the double quotes from the file.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Thom A

    SSC Guru

    Points: 98458

    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/2019 and Steve, 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.

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88173

    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.

     

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

Viewing 8 posts - 1 through 8 (of 8 total)

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