none
Help - how to get column headers to flat file RRS feed

  • Question

  • Hi,

     

    I have a flat file destination but have no idea how to capture the column headers to the output file.

     

    Is this a setting in SSMS or in SSIS?

     

    The problem is, when I'm setting up the flat file connection manager, it forces me to browse for a file. Well, that file doesn't have headers in it, so it's not going to pick them up. Furthermore, I am using an expression to generate the file name, so the file will be different every time. So seems like the headers have to be coming from the query.

     

    How do I do this?

     

    Thanks

    Tuesday, November 25, 2008 3:41 PM

Answers

  • Can you try going back to my original instructions. When you create the flat file connection manager, do not pick an existing file. Use a new name, one that does not already exist. Make sure you check the column names in the first data row option.

     

    In my testing, that should deliver exactly what you need.

     

    "Column names in the first data row" does not mean that the destination expects the column headers in the first data row, it means that it will put column headers in the first data row in the output file.

     

    Wednesday, November 26, 2008 4:34 PM
    Moderator

All replies

  • Hello - does anybody know HOW to get the column names from a table (query) to output to the Flat File Destination?

     

    The rows are coming from an OLEDB Source in the Data Flow, which calls a stored procedure. I can see the rows returned in the Flat File Manager preview, even the column names... so WHY is there no option to add the column names to the output????

     

    Sorry folks, this is idiotic. Unless I'm really missing something here, which is possible.

     

    I found another thread that describes one way to do this. I just CAN'T believe you have to go through this kind of effort to do this.

     

    --------------------------------------------------------------------------------------------------------------------

    We use the dtsx designer in Microsoft Visual Studio to do our
    development.

    We create two 'flat file connection managers' - one holds the column
    definition for the header, one holds the column definition for the
    detail.

    We create a data flow task to populate the header information. Within
    the data flow, we create a data reader source that pulls the summary
    data and connect the summary data reader to a flat file destination
    (attached to the 'header' flat file connection manager) with 'overwrite'
    set to true.

    We then create a second data flow task connected to the 'successful'
    branch of the 'populate header' task. Within the second data flow task,
    we create a data reader source that pulls the detail data and we connect
    the detail data reader to flat file destination (attached to the
    'detail' flat file connection manager) with 'overwrite' set to false.

    I hope this is helpful for you!
    ~Rose

     

    Tuesday, November 25, 2008 5:23 PM
  • Create the flat file connection manager by adding a flat file destination to the data flow, connecting it to the upstream component, and then choosing New in the connection manager drop down. Then, in the connection manager's property dialog, check the "Column names in first data row" checkbox. Works like a charm for me.

    Tuesday, November 25, 2008 7:46 PM
    Moderator
  • Believe me, I've already tried that. Don't work... is there some kind of bug here??

     

    It puts the first row of DATA into the column header, not the column names...

     

    Wednesday, November 26, 2008 5:20 AM
  • BTW, I found this link which seems to indicate this might be a bug. Any thoughts?

     

    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=373232&wa=wsignin1.0

    Wednesday, November 26, 2008 5:28 AM
  •  sadie519590 wrote:

    BTW, I found this link which seems to indicate this might be a bug. Any thoughts?

     

    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=373232&wa=wsignin1.0

     

    the ssis team said that they couldn't repro.  can you repro consistently?

    Wednesday, November 26, 2008 5:56 AM
    Moderator
  • Yes

     

    Wednesday, November 26, 2008 6:18 AM
  • Let me explain what I am doing, so that there is no confusion:

     

    1.) Created an OLE DB Source in the Data Flow

    2.) Calling a stored proc from the OLE DB Source, i.e. EXEC myProc (which is a simple "SELECT * WHERE...")

    3.) Created and connected the OLE DB Source to a Flat File Destination

    4.) Created the Flat File Connection Manager

     

    Flat File Connection Manager settings:

     

    Format: Delimited

    Text Qualifier: "

    Header Row Delimiter: {CR}{LF}

    Header Rows to Skip: 0

     

    Column Names in First Data Row: NOT checked

    When NOT checked, the Preview tab shows my data rows AND the column names. The column names are up top, in the gray label area, not in the data section. 

     

    Column Names in First Data Row: Checked

    When checked, the Preview tab shows the FIRST ROW OF DATA in the gray label area, instead of the column names. It misses the column names completely. They're nowhere to be seen.

     

    That is the problem description in a nutshell.

     

     

    Wednesday, November 26, 2008 6:31 AM
  •  sadie519590 wrote:
    Yes

     

     

    then, i suggest that you submit a new bug report to connect.  if you do so, please make sure that you give them the same instructions that you posted here.

    Wednesday, November 26, 2008 8:13 AM
    Moderator
  • Ok thanks

     

    Just to confirm then, these step should work - yes?

     

    Wednesday, November 26, 2008 8:18 AM
  • Hmm, actually if you think about it, the "Column names in the first data row" seems to be doing the correct thing.

     

    That is, if the "Column names in first data row" box, is checked, should select the first row. It IS doing that.

     

    The problem is, column names are NOT in the first data row. Data is in the first data row, because the results are coming from a SELECT statement in the OLE DB source.

     

    So how does one get the column names to appear as the first data row from a query? It can't be hardcoded either.

     

    That's why I was saying, I think it's ridiculous that there isn't an option to just say "include column names in destination file" because obviously SSIS can see them! They're in the grey label area. But there seems to be no way to "push" them down into the data area.

     

    That's the problem.

     

     

    Wednesday, November 26, 2008 8:28 AM
  •  sadie519590 wrote:

    Hmm, actually if you think about it, the "Column names in the first data row" seems to be doing the correct thing.

     

    That is, if the "Column names in first data row" box, is checked, should select the first row. It IS doing that.

     

    The problem is, column names are NOT in the first data row. Data is in the first data row, because the results are coming from a SELECT statement in the OLE DB source.

     

    So how does one get the column names to appear as the first data row from a query? It can't be hardcoded either.

     

    That's why I was saying, I think it's ridiculous that there isn't an option to just say "include column names in destination file" because obviously SSIS can see them! They're in the grey label area. But there seems to be no way to "push" them down into the data area.

     

    That's the problem.

     

     

     

    have you tried using an alias like the following:

     

    Code Snippet

    SELECT TableColumn AS ColumnHeader

     

     

    hth

    Wednesday, November 26, 2008 9:10 AM
    Moderator
  • I'm not sure what you mean?

    Wednesday, November 26, 2008 11:57 AM
  •  sadie519590 wrote:

    Let me explain what I am doing, so that there is no confusion:

     

    1.) Created an OLE DB Source in the Data Flow

    2.) Calling a stored proc from the OLE DB Source, i.e. EXEC myProc (which is a simple "SELECT * WHERE...")

    3.) Created and connected the OLE DB Source to a Flat File Destination

    4.) Created the Flat File Connection Manager

     

    Flat File Connection Manager settings:

     

    Format: Delimited

    Text Qualifier: "

    Header Row Delimiter: {CR}{LF}

    Header Rows to Skip: 0

     

    Column Names in First Data Row: NOT checked

    When NOT checked, the Preview tab shows my data rows AND the column names. The column names are up top, in the gray label area, not in the data section. 

     

    Column Names in First Data Row: Checked

    When checked, the Preview tab shows the FIRST ROW OF DATA in the gray label area, instead of the column names. It misses the column names completely. They're nowhere to be seen.

     

    That is the problem description in a nutshell.

     

     

     

    Are you pointing the flat file connection manager to an existing file, or a new one?

    Wednesday, November 26, 2008 3:33 PM
    Moderator
  • I am pointing to an existing file, with no column headers. However, when the package runs, it generates a new file each time with new data in it from the OLEDB Source query. The query isn't outputting column names in the file. So the file will never have column names in it.

     

    Which I think is the problem... so how can I get the column names to appear in the file itself from the OLEDB Source query?? e.g. SELECT * FROM...

     

    My flow need to go like this:

     

    DataFlow: Run query from OLEDB Source > Output to Flat File Destination with column headers in it

     

    ================

     

    Getting back to my original post, if the Flat File Destination Connection Manager can SEE the column names, as show in the "Preview" how come there's no option to output these to the file? That's what I don't understand!

    Wednesday, November 26, 2008 4:20 PM
  • Can you try going back to my original instructions. When you create the flat file connection manager, do not pick an existing file. Use a new name, one that does not already exist. Make sure you check the column names in the first data row option.

     

    In my testing, that should deliver exactly what you need.

     

    "Column names in the first data row" does not mean that the destination expects the column headers in the first data row, it means that it will put column headers in the first data row in the output file.

     

    Wednesday, November 26, 2008 4:34 PM
    Moderator
  • That worked - thanks a bunch

     

     

     

    Wednesday, November 26, 2008 6:06 PM

  • "Column names in the first data row" does not mean that the destination expects the column headers in the first data row, it means that it will put column headers in the first data row in the output file

    Thanks, this solved my issue. They should really make this clearer - seems like a lot of people are misinterpreting the meaning.
    Tuesday, April 17, 2018 2:50 PM