Exporting Column Names

Is it possible, when exporting data to Excel, to export the column names with the data?
Thanks!If you use DTS and export to an XLS, you can get the column names on the first run. For some reason, I can't get it to automate.|||Got it! Thanks! :-)

exporting blank report to excel gives no header info

I have a report that can sometimes be blank. When the report is exported to excel I dont get the header info - I need to get the col names even if there is no data. right now it comes up as blank spreadsheet and the first row first col is abnormally long. does anyone know how to fix this ?

I'd appreciate any help.still having this issue...

I wish there was atleast one person from the reporting team looking into these forums..Could this be a bug in RS ?|||Well Reporting services Automatically check the data of report if the data does not exist it automatically hide the subreport or in your senario.

What you can do is . There is noRows Properties in the reports to use if data does not exist in any case. to show the message .

Or if you reeally want to show the header in the report . you would have to change the query or procedure to pass atleast one balck row incase data doesn not exist. You can do this by union query.

There are 2 ways now . Choice is your|||hey dude

thanks for hint

(1) I have to type in the concatenated list of column names in the noRows property. which is okay but when the report is exported to excel the entire string is exported as one column rather than different column names.

(2) I cant use the UNION because we have one stored proc that is being used for 10-15 reports. and each report uses a diff set of column names from the stored proc. so i cant return all the column names in the stored proc when only some of them are used in the report.

I am trying to see if I can access the rdl file from custom code to see if i can parse the xml file and get the column names..

thanks again..|||I found a couple of hacks for this. Thought it could be useful for someone else...

(1) I can put the columns in the Page header ( Since I cant put table in the header I can throw in some textboxes) with the same width as the column below in the body section. This is a feasible solution.

(2) Modify the stored proc to something like :

SELECT
 col1, col2...
FROM
 <table
UNIONALL
SELECT
NULL,
NULL

This will return a blank row at the end of the report and RS treats this as a row. So when exported we do get a row with headers and a blank row.

(3) This is not very efficient but works...

Get the results of the stored proc into a temp table. do a UNION with nulls and the temp table IF there are no rows in the temp table and return the result set. This can affect the performance coz it uses temp tables..

Exporting a table with changing column names to an excel file

I'm trying to write an SSIS package that exports a table that has changing column names to an excel file. The column names change due to the fact that the table is created by a pivot daily. the only thing I'm missing is the ability to dynamically map the tables' columns to the excel destination. Is this possible?

I read in another thread that

"It is not possible to create packages or new objects within packages using SSIS."

I also read in the books online that "The input and the input columns of the Excel destination have no custom

properties." To me this means that I cannot programmatically create or remove columns in the excel destination. Please tell me I'm wrong.

So, to summarize my research so far. In writing an SSIS package, I cannot programmatically create a new excel destination object and I can't manipulate an existing one. I hope I'm wrong. Can anyone help me? (and please correct any wrong assumptions I may have stated)

I'm interested in finding this out as well. I need to basically take a dataset and cast it out to multiple pivot tables in Excel. If you find anything out about this, or find that you can't manage it; please let me know. Feel free to email!

thx

|||

It is not possible for a package to dynamically alter its pipeline(s) at execution-time. The metadata of the pipeline is set at design-time.

There are workarounds to this - but they're difficult. I have talked a little about this subject here:

But it used to work in DTS (1) - Modifing a Package in script
(http://blogs.conchango.com/jamiethomson/archive/2006/06/28/4156.aspx)

-Jamie

sql

Exporting a table with changing column names to an excel file

I'm trying to write an SSIS package that exports a table that has changing column names to an excel file. The column names change due to the fact that the table is created by a pivot daily. the only thing I'm missing is the ability to dynamically map the tables' columns to the excel destination. Is this possible?

I read in another thread that

"It is not possible to create packages or new objects within packages using SSIS."

I also read in the books online that "The input and the input columns of the Excel destination have no custom

properties." To me this means that I cannot programmatically create or remove columns in the excel destination. Please tell me I'm wrong.

So, to summarize my research so far. In writing an SSIS package, I cannot programmatically create a new excel destination object and I can't manipulate an existing one. I hope I'm wrong. Can anyone help me? (and please correct any wrong assumptions I may have stated)

I'm interested in finding this out as well. I need to basically take a dataset and cast it out to multiple pivot tables in Excel. If you find anything out about this, or find that you can't manage it; please let me know. Feel free to email!

thx

|||

It is not possible for a package to dynamically alter its pipeline(s) at execution-time. The metadata of the pipeline is set at design-time.

There are workarounds to this - but they're difficult. I have talked a little about this subject here:

But it used to work in DTS (1) - Modifing a Package in script
(http://blogs.conchango.com/jamiethomson/archive/2006/06/28/4156.aspx)

-Jamie

Export wizard trouble exporting columns names to ragged file.

I’m using the Import\Export wizard to export the top 5 lines from a MS Sql table into a fixed format (“ragged”) file.But I want the first record to contain the column names of the exported fields so I selected the “Column names in the first data row” option of the “Choose a Destination” box. When I run the Package I get:

>>>

· Information 0x402090dc: Data Flow Task: The processing of file "C:\barkingdog\ExportWithheader.txt" has started (SQL Server Import and Export Wizard)

· Error 0xc0202095: Data Flow Task: Failed to write out column name for column "CustomerID".
(SQL Server Import and Export Wizard)

Error 0xc004701a: Data Flow Task: component "Destination - ExportWithheader_txt" (49) failed the pre-execute phase and returned error code 0xC0202095.
(SQL Server Import and Export Wizard)

>>>

When I de-select the “Column names” option, the package works fine.Other than manually, how can I et the column names in output file?

TIA,

Barkingdog

That's very strange. I'm racking my brains as to what might be the problem but I can't think of anything!

Is there any other clue as to what is the problem?

-Jamie

|||

The problem here is that when you include column names in the first data row, then you might need to modify the column width (for a ragged right file). For example, if you have a column CustomerID which is of type DT_STR(5), it will fail trying to write "CustomerID" in that space, which takes 10 characters. Use the Edit Transform button on configure Flat File Destination page of the Import Export Wizard to adjust the length specified for each fixed width column.

hope that helps.

|||

Ranjetta,

I thought that that could be the case but I haven't had a chance to test it.

I also have to test if the "Import" wizard, with "column names in first row", has the same issue. My impression is no and perhaps the Export wizard is not as forgiving as the Import one. I will check.

Barkingdog