Exporting data to a csv file and include column names
I am creating a SSIS package which creates a .csv file. The package works fine except it is not putting the column names as the first row. How do I get the column names to also export? Thanks, ~John
July 12th, 2011 12:30pm

While setting up the destination connection in the File connection manager select the "Column Names in the first Row". This should do the job.My Blog | Ask Me | Test your SSIS skills
Free Windows Admin Tool Kit Click here and download it now
July 12th, 2011 12:37pm

When I do that it replaces the column headings with the data from the first row of my table I am trying to export.
July 12th, 2011 12:50pm

Which one is it? Your first post said you weren't seeing column names in the CSV, and your last post says they're now being replaced with data. Checking the "Column Names in First Row" setting will work for you. The only reason it won't might be if you have other things going on in that package we're not aware of. Please describe your package, connection managers, and data flow in more detail. Talk to me now on
Free Windows Admin Tool Kit Click here and download it now
July 12th, 2011 1:36pm

Okay, let me try to explain this better. I am not seeing any column names in my outputed file. In my connection manager without checking the box for "Column Names in First Row" and doing a preview of the data I see my column names and first 200 rows of data. When I check that box the column headers go away and I only see the first fow of data in place of the column headings. Looking at my source connection manager I can see the column names and data as well. The package was designed to do the following: 1. Creates or truncates the table which holds the data in the database. 2. Loads a text file into the databsae table just created or emptied. 3. Does some data clean up on the data to remove extra quotes in the file. 4. Finally it pulls the data from the database and creates a .csv file. This is where I am having an issue with it not pulling in the column names. The file generates okay, but there is no header row. Hope that helps better explain my problem. ~John
July 12th, 2011 1:45pm

Rather than checking the header row in the data viewer, execute the package with the settings I mentioned and see the text file. May be put a row count transform to check the count of rows.My Blog | Ask Me | Test your SSIS skills
Free Windows Admin Tool Kit Click here and download it now
July 12th, 2011 2:50pm

I made a few changes to my connection manager and it is now working. One thing I did notice however, is that for my first field which is a listing of three digit numbers that I am passing in as text it is dropping the leading zero's from all fields. Do you know how I can make it now do that? The field needs to contain the leading zerors so that the data of 001 comes over as 001 and not 1. Thanks for all your help, it is much appreciated. ~John
July 12th, 2011 3:29pm

does the source and destination different? does any value or column name exists in destination file? or that is clean file?http://www.rad.pasfu.com My Submitted sessions at sqlbits.com
Free Windows Admin Tool Kit Click here and download it now
July 12th, 2011 3:31pm

The source is a SQL Server 2005 database table and the destination is a .csv file. It creates a new file everytime it is run. Looking at the data in the source it does have the leading zeros for that columns data.
July 12th, 2011 3:37pm

could you post an screenshot of your package control flow and data flow schema?http://www.rad.pasfu.com My Submitted sessions at sqlbits.com
Free Windows Admin Tool Kit Click here and download it now
July 12th, 2011 3:46pm

Are you doing any transforms in the flow where you are converting the column to numeric? or any other such transform.My Blog | Ask Me | Test your SSIS skills
July 12th, 2011 3:48pm

No, the data is coming in as text and going out as text. It is being changed by the spreadsheet when I open it. If I do the ouput to a .txt file the leading zeros are there, but if I open it in Excel it is formatting that column as General instead of text thus removing the leading zeros.
Free Windows Admin Tool Kit Click here and download it now
July 12th, 2011 4:43pm

Ok, What is happening in Excel is that it shows the column value as general where it displays numeral values as numeric while the actual data is stored as text with leading zeroes. Do not worry about this the data in the file is as you want it to be. If in excel you want it as text select the entire column and format the column type as text. My Blog | Ask Me | Test your SSIS skills
July 12th, 2011 5:16pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics