locked
CSV destination with no headers RRS feed

  • Question

  • Hello:

    It is possible to export to CSV with headers or with no headers, but my problem is a bit different..

    My customer wants a CSV file where most of the headers are blank, but some aren't. The data, coming from a SQL Server query source, will have many zero values, and the corresponding headers for the zero values will be blank. There are many columns with zero values, so I wanted to ask before I went to trial and error.  Problem is you can't use a source query will blank headers or with headers that are the same. So since the source query won't let me do this I imagine if it is possible at all I would have to use some transform to change the header values to blank and then send to CSV. The CSV result should look like this

    1stHeader,2ndHeader,,,5thHeader

    3221,322,0,0,2

    Thanks for any help,

    Blair

    Monday, July 16, 2012 12:15 PM

Answers

  • To aviod dupe headers I mentioned in my reply to unckeck the property "Column Names in the first row" in the destination connection.

    My Blog    |      Ask Me     |      SSIS Basics     

    • Marked as answer by Eileen Zhao Thursday, July 26, 2012 7:30 AM
    Wednesday, July 18, 2012 8:56 AM

All replies

  • you can create your own query, (add the column's title row with a union to data rows)

    and add derived column for fixed column values (like zero values),

    and then load them into a flat file destination, (Uncheck the column names are in the first data row) and set other properties of destination flat file


    http://www.rad.pasfu.com

    • Proposed as answer by Sudeep Raj Wednesday, July 18, 2012 4:52 AM
    Monday, July 16, 2012 12:19 PM
  • Added issue--I tried to create the query using an expression but it is now more than 4000 characters, which isn't allowed in an expression.

    I have tried creating the query in a script task using VB (setting the query variable's "Evaluate as Expression" property to "False"), but when I then go to the source query object I can't see the structure of the query to select columns etc. because the script task has not run yet.

    How best to do this?

    Tuesday, July 17, 2012 3:51 PM
  • So I have a flat file outputting. I have, as suggested, made all the values strings so I can include the header names (many of which are identical) in the data of the csv. Problem is, the actual headers are still going to the csv. I tried the "header rows to skip option" but that doesn't seem to be working. The csv is something like:

    H1,H2,H3,H4,H5

    H1,H2,,,

    33,44,33,0,0,0

    123,25,1,0,0,0

    I don't want the top row, the actual headers from the query, to be in the csv, I want the first data row to act as the header in the csv.

    How to do?

    Thanks.

    Wednesday, July 18, 2012 2:37 AM
  • Wednesday, July 18, 2012 4:44 AM
  • Another simpler approach would be in the Control flow put a Script Task before the Data Flow Task. In the Script task write to the destination file just the header deatils which could be saved in some variables. Now use this file in the DFT.

    My Blog    |      Ask Me     |      SSIS Basics     

    Wednesday, July 18, 2012 4:54 AM
  • Not sure I understand your approach Sudeep. I make a bit of a longwinded description, but essentially all I want to do is output only the data to a csv, not the headers. It seems in the flat file connection manager that should be possible, but it doesn't seem to be working.

    If you think your approach is still the way to go could you give some more detail? Why do I want to save the headers in a file if I want to discard them eventually?


    Wednesday, July 18, 2012 5:23 AM
  • Not sure I understand your approach Sudeep. I make a bit of a longwinded description, but essentially all I want to do is output only the data to a csv, not the headers. It seems in the flat file connection manager that should be possible, but it doesn't seem to be working.

    If you think your approach is still the way to go could you give some more detail? Why do I want to save the headers in a file if I want to discard them eventually?

    You want your output as including header row:

    1stHeader,2ndHeader,,,5thHeader
    3221,322,0,0,2

    So if you know in your output you want the headers like the one above, write to the file using script task. and in the data flow task flat file destination connection uncheck "Column Names in the first row"

    Let me know if My understanding is wrong.


    My Blog    |      Ask Me     |      SSIS Basics     

    Wednesday, July 18, 2012 5:31 AM
  • Not sure if we are talking about the same thing--in this case the column names ARE in the first row. The column names that came from the SQL Server Source are what I do not want.

    If I send the headers to the file first and then the rest of the data via the data flow, won't it still send the actual column names from the SQL Server source so I'll still end up with two header rows?

    Wednesday, July 18, 2012 8:52 AM
  • To aviod dupe headers I mentioned in my reply to unckeck the property "Column Names in the first row" in the destination connection.

    My Blog    |      Ask Me     |      SSIS Basics     

    • Marked as answer by Eileen Zhao Thursday, July 26, 2012 7:30 AM
    Wednesday, July 18, 2012 8:56 AM