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 to unicode textfile with tsql

Hello,
is it possible to write the he result of query to a unicode textfile instead
of an ansi textfile. i need this because i want to generate udl files on the
fly.
any help appriciated.
GerbenHi
You don't say how the file is created but...
osql has a -u parameter that creates a unicode file.
John
"gerben" wrote:

> Hello,
> is it possible to write the he result of query to a unicode textfile inste
ad
> of an ansi textfile. i need this because i want to generate udl files on t
he
> fly.
> any help appriciated.
> Gerben

Export to flat file

We have a need to export a couple of reports to a flat file (not csv). I am thinking that the easiest way to do this is to write a custom extension. Should I do it this way and if so, can somebody point me to some resources or is there an easier way to do this?

Thanks for the information.

You need to write your own rendering extension for any output format not supported out of the box.

http://msdn2.microsoft.com/en-us/library/ms154606.aspx

|||Yeah, I was afraid that you would say that.

Export to flat file

We have a need to export a couple of reports to a flat file (not csv). I am thinking that the easiest way to do this is to write a custom extension. Should I do it this way and if so, can somebody point me to some resources or is there an easier way to do this?

Thanks for the information.

You need to write your own rendering extension for any output format not supported out of the box.

http://msdn2.microsoft.com/en-us/library/ms154606.aspx

|||Yeah, I was afraid that you would say that.

export to excel

is it possible to write a query that will export my reuslts to an excel
spreadsheet with tsql?Try DTS packages
--
Thanks & Rate the Postings.
-Ravi-
"mcnewsxp" wrote:

> is it possible to write a query that will export my reuslts to an excel
> spreadsheet with tsql?
>
>|||>
> Try DTS packages
> --
not what i was looking for, but thanks.|||If you click in your Results window in Query Analyser (QA) and do 'Save As'
you can save the results as a file, and the open it in Excel.
Alternately you can use bcp and the 'queryout' option if you need a special
format. This is a big topic so google bcp and queryout.
Finally, again in QA, you can customise your results in the 'Tools' and
'Options' menu:
General Tab
Set a default Result file directory
Results Tab
Change Results output format from 'Results to Text' to 'Results to File'
Change the Result file extension from .rpt to .xls
Let me know how you get on.
Damien
"mcnewsxp" wrote:

> is it possible to write a query that will export my reuslts to an excel
> spreadsheet with tsql?
>
>|||Look into makewebtask. It is real easy to write a query that sends directly
to Excel as long as you only have 1 tab.
"mcnewsxp" wrote:

> is it possible to write a query that will export my reuslts to an excel
> spreadsheet with tsql?
>
>

export to csv file places all detail line on 1 line of CSV?

trying to write a report that when exported (csv) each detail line in my
report would be a seperate line in the csv file currently the csv places all
detail line on the same line in the csv and delimits the row by comma. is
there a way in the csv rendering to allow the RecordDelimiter to be a
<cr><lf>?thanks you are correct but maybe I'm not being clear on my end the columns
are not the issue. I have 3 detail line in the report each line has 3 columns
report table
detail line1
detail line2
detail line3
but when i export to csv i get
(record1) line1,line2,line3
next(record) line1,line2,line3
etc...
what i'm looking for is
(record1)
line 1
line 2
line 3
(nextrecord)
line1
line2
line3
make any more sense?
thanks in advance for all the help
"Bruce L-C [MVP]" wrote:
> I assume you want Excel to open this up. RS defaults to unicode which Excel
> does not recognize. If RS 2005 you can configure RS to export it as Ascii
> which Excel will then properly split into cells.Even if what you want is a
> file for some other reason, I have RS configured for ASCII csv and my rows
> are NOT separated by a comma. So I think regardless of what you want to do
> follow the below and see if it resolves it for you:
> You only need to change in one place, rsreportserver.config. Reboot after
> the change. The below shows commenting out the existing entry and putting in
> the needed change to have CSV export as ASCII.
> .. <!--
> <Extension Name="CSV"
> Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering"/>
> -->
> <Extension Name="CSV"
> Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
> <Configuration>
> <DeviceInfo>
> <Encoding>ASCII</Encoding>
> </DeviceInfo>
> </Configuration>
> </Extension>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "gackinclose" <gackinclose@.discussions.microsoft.com> wrote in message
> news:CFE91818-CC7B-4297-8802-0C15BBB52998@.microsoft.com...
> > trying to write a report that when exported (csv) each detail line in my
> > report would be a seperate line in the csv file currently the csv places
> > all
> > detail line on the same line in the csv and delimits the row by comma. is
> > there a way in the csv rendering to allow the RecordDelimiter to be a
> > <cr><lf>?
> >
>
>|||I assume you want Excel to open this up. RS defaults to unicode which Excel
does not recognize. If RS 2005 you can configure RS to export it as Ascii
which Excel will then properly split into cells.Even if what you want is a
file for some other reason, I have RS configured for ASCII csv and my rows
are NOT separated by a comma. So I think regardless of what you want to do
follow the below and see if it resolves it for you:
You only need to change in one place, rsreportserver.config. Reboot after
the change. The below shows commenting out the existing entry and putting in
the needed change to have CSV export as ASCII.
. <!--
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering"/>
-->
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<Configuration>
<DeviceInfo>
<Encoding>ASCII</Encoding>
</DeviceInfo>
</Configuration>
</Extension>
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"gackinclose" <gackinclose@.discussions.microsoft.com> wrote in message
news:CFE91818-CC7B-4297-8802-0C15BBB52998@.microsoft.com...
> trying to write a report that when exported (csv) each detail line in my
> report would be a seperate line in the csv file currently the csv places
> all
> detail line on the same line in the csv and delimits the row by comma. is
> there a way in the csv rendering to allow the RecordDelimiter to be a
> <cr><lf>?
>|||Ahh, multiple detail lines. No, they are all considered one line of data for
export. I am not aware of a way to do what you want.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"gackinclose" <gackinclose@.discussions.microsoft.com> wrote in message
news:7DA88C6E-4683-483D-9554-E576566EED48@.microsoft.com...
> thanks you are correct but maybe I'm not being clear on my end the columns
> are not the issue. I have 3 detail line in the report each line has 3
> columns
> report table
> detail line1
> detail line2
> detail line3
> but when i export to csv i get
> (record1) line1,line2,line3
> next(record) line1,line2,line3
> etc...
> what i'm looking for is
> (record1)
> line 1
> line 2
> line 3
> (nextrecord)
> line1
> line2
> line3
>
> make any more sense?
> thanks in advance for all the help
>
> "Bruce L-C [MVP]" wrote:
>> I assume you want Excel to open this up. RS defaults to unicode which
>> Excel
>> does not recognize. If RS 2005 you can configure RS to export it as Ascii
>> which Excel will then properly split into cells.Even if what you want is
>> a
>> file for some other reason, I have RS configured for ASCII csv and my
>> rows
>> are NOT separated by a comma. So I think regardless of what you want to
>> do
>> follow the below and see if it resolves it for you:
>> You only need to change in one place, rsreportserver.config. Reboot after
>> the change. The below shows commenting out the existing entry and putting
>> in
>> the needed change to have CSV export as ASCII.
>> .. <!--
>> <Extension Name="CSV"
>> Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering"/>
>> -->
>> <Extension Name="CSV"
>> Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
>> <Configuration>
>> <DeviceInfo>
>> <Encoding>ASCII</Encoding>
>> </DeviceInfo>
>> </Configuration>
>> </Extension>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "gackinclose" <gackinclose@.discussions.microsoft.com> wrote in message
>> news:CFE91818-CC7B-4297-8802-0C15BBB52998@.microsoft.com...
>> > trying to write a report that when exported (csv) each detail line in
>> > my
>> > report would be a seperate line in the csv file currently the csv
>> > places
>> > all
>> > detail line on the same line in the csv and delimits the row by comma.
>> > is
>> > there a way in the csv rendering to allow the RecordDelimiter to be a
>> > <cr><lf>?
>> >
>>