January 30, 2009 at 10:02 am
Hi All,
This is probably an easy question but I am new to SSIS so...
I have a simple select query being run by a data flow task and when the results are pushed out to the destination file it shows only the results and not the headings. I did try a search on the forums first and all I got was someone mentioning to tick the 'Column Names in the first data row' - This however only assumes the first row is the header and uses that as the column headings (not what I was after). I can not get the results to display the headers except in the preview in the flat file destination
Thanks in advance
Elliot
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
January 30, 2009 at 10:31 am
You could setup a second data source that would issue an SQL statement such as:
select 'ColumnName1' as Col1,'ColumnName2' as col2,'ColumnName3' as col3,... (no from clause is needed)
Just set it up as an OLE source pick a connection, doesn't matter which since you will not be referencing a table.
This would give you a single record that looks like
ColumnName1 | ColumnName2 | ColumnName3
Format the select to output exactly what you need.
Then use a union all task and setup your header as input 1 and your data as input 2 - I think that will get them in the right order (not sure I have not done this but I think it will get you started)
data types may be an issue although unless you can format all columns as character.
good luck.
February 2, 2009 at 5:16 am
Many thanks for your prompt response and workaround to the issue.
I am slightly amazed that SSIS does not have a capability built into this. I used to run DTS packages that would, when run as resultset to text, would export the column headings as well.
If you or anyone knows of an inbuilt function please let me know as the above will be quite resource intensive as there are a lot of existing reports to update.
Thanks in advance
Elliot
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
February 2, 2009 at 4:15 pm
blow away your destination file (on the file system as well as within your data flow) and destination connection manager. create a new destination file within your data flow and a new destination connection manager. tick the box you mentioned in your first post. your col headers will appear in the first row of your destination file when you run the package the first time.
if you have already run the package and do not blow away the file, you are correct, it expects the first line of the file to contain the col headers. if the file is blank, it will write col headers to the first row in the file.
hope this helps.
February 3, 2009 at 10:23 am
Many thanks - I'll give it a try
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply