Exporting Data From SSIS Package to Excel

I currently have a export that takes data from my SQL Server 2005 DB and exports it into Excel. This process works correctly. My excel template has the first row headers and the data is dumped in the row after the header. I would like to know if it is possible for me to add borders around my data without doing it within the template? I don't know how much data is going to be exported so I can't put borders within the template. I put borders around the headers to see if it will copy the formatting down to the data and it didn't. Thank You for any help.

I do not think you can do this using SSIS.

Thanks.

|||

I hope that isn't true I really have to find a way around that. Why does Microsoft limit the access to the Office Components being automated?

|||

Jerry8989 wrote:

Why does Microsoft limit the access to the Office Components being automated?

It doesn't, you just have to work a little harder.

http://sqljunkies.com/WebLog/knight_reign/archive/2005/07/07/16018.aspx
http://msdn2.microsoft.com/en-us/library/ms136007.aspx

I think automation is the only way you'll get this accomplished.
|||

Jerry8989 wrote:

I hope that isn't true I really have to find a way around that. Why does Microsoft limit the access to the Office Components being automated?

Well, for one SSIS is an ETL tool and as such isn't designed to be making fancy looking excel sheets, powerpoint presentations, etc... It's designed to read data, transform it, and load it somewhere. So the fact that you need pretty looking visual effects in your excel sheet, doesn't really warrant the need for that feature in SSIS.

Export to MS Excel carriage returns

I have a report which has multiple fields in the same column of a table. So that it formats correctly, on the stored procedure side I inserted char(10) + char(13). This allows the next field to correctly carriage return on the report. The report looks great.

But once the report is exported to MS Excel, it appears to add in extra carriage returns. This can be cleaned up by going record by record and back-spacing over the carriage returns.

Is there any other work around for this?

Just curious. Thank you for your help.

T.J.

I haven't found one as yet. The other minor annoyance is that when you open the exported report in excel the cells with carriage returns all have the same row height and hence you can't actually tell if a cell has multiple lines unless you manually autosize the rows.|||

It really appears that the report export to MS Excel is just a file for the users to work with once the data gets exported. As the data does not come over clean (too many carriage returns), or the data does not completely display (all the data arrives, but it is hidden in the column, even if wrap text is checked), or the excel spreadsheet over extends the page to the left and prints 2 pages for 1 report page.

Has anyone had any success with exporting a report to MS Excel, or is it just a way for the users to edit the data?

If anyone can offer advice, I would appreciate it.

Thank you,

T.J.

|||The other thing it can't do is export sureports.|||

Thank you for telling me that. I had no idea.

Strange. I have a sub-report that exports to Excel just fine, but a sub-report on the sub-report does not export (the original sub-report is not linked, just a straight listing of data, seperate from the rest of the report).

Oh my.

|||Reading your post I just noticed that you have the order switched for those characters. You are using a line feed (chr 10) and then a carriage return (chr 13). Micro$ likes to see them in the other order: vbcrlf - or char(13) + char(10)... and some other OS's (that shall not be named) just want to see the line feed... which is probably why Excel is adding in the extra characters (it's picking up on it as a non-ms format).

Export to MS Excel carriage returns

I have a report which has multiple fields in the same column of a table. So that it formats correctly, on the stored procedure side I inserted char(10) + char(13). This allows the next field to correctly carriage return on the report. The report looks great.

But once the report is exported to MS Excel, it appears to add in extra carriage returns. This can be cleaned up by going record by record and back-spacing over the carriage returns.

Is there any other work around for this?

Just curious. Thank you for your help.

T.J.

I haven't found one as yet. The other minor annoyance is that when you open the exported report in excel the cells with carriage returns all have the same row height and hence you can't actually tell if a cell has multiple lines unless you manually autosize the rows.|||

It really appears that the report export to MS Excel is just a file for the users to work with once the data gets exported. As the data does not come over clean (too many carriage returns), or the data does not completely display (all the data arrives, but it is hidden in the column, even if wrap text is checked), or the excel spreadsheet over extends the page to the left and prints 2 pages for 1 report page.

Has anyone had any success with exporting a report to MS Excel, or is it just a way for the users to edit the data?

If anyone can offer advice, I would appreciate it.

Thank you,

T.J.

|||The other thing it can't do is export sureports.|||

Thank you for telling me that. I had no idea.

Strange. I have a sub-report that exports to Excel just fine, but a sub-report on the sub-report does not export (the original sub-report is not linked, just a straight listing of data, seperate from the rest of the report).

Oh my.

|||Reading your post I just noticed that you have the order switched for those characters. You are using a line feed (chr 10) and then a carriage return (chr 13). Micro$ likes to see them in the other order: vbcrlf - orchar(13) + char(10)... and some other OS's (that shall not be named) just want to see the line feed... which is probably why Excel is adding in the extra characters (it's picking up on it as a non-ms format).

Export to Excel. Version Problem

when I try to eport a report in EXCEL format it only works on client with
EXCEL XP installed. With EXCEL 2000 it dosn't work correctly.
Anyone knows if there are EXCEL version Requirements'
ThenksYes, it only works with Office XP or above and will not work in Office 2000.
Cheers
"Ale Dori" wrote:
> when I try to eport a report in EXCEL format it only works on client with
> EXCEL XP installed. With EXCEL 2000 it dosn't work correctly.
> Anyone knows if there are EXCEL version Requirements'
> Thenks|||Excel 2000 works after installing RS SP1.
"Ale Dori" <AleDori@.discussions.microsoft.com> wrote in message
news:BBCBE66F-6F04-42EB-B461-B74F0C537BB3@.microsoft.com...
> when I try to eport a report in EXCEL format it only works on client with
> EXCEL XP installed. With EXCEL 2000 it dosn't work correctly.
> Anyone knows if there are EXCEL version Requirements'
> Thenks

Export to CSV w/ grouping problem after 2005 upgrade

Hi,

After upgrading to reporting services 2005 from 2000, reports that used to export to CSV correctly don't now. It appears the CSV export suddenly stops at a table with grouping. Any ideas would be appreciated...

Thanks.I am having the same problem although I'm not sure it is the grouping that breaks it. I have many reports, and after converting to 2005 some of them export data in the table to csv and others do not. I am unable to find any differences between the reports that work and those that don't - all of the having groups in the table. I am disappointed that the original post was back in February and there have been no responses?|||I fixed this by changing the Output property of the table from Auto to Yes. Although I'm still not sure why it was working on some reports and not others. Both had the same conditional expressions for visibility in them and the Output property of both were set to Auto.

Export to CSV w/ grouping problem after 2005 upgrade

Hi,

After upgrading to reporting services 2005 from 2000, reports that used to export to CSV correctly don't now. It appears the CSV export suddenly stops at a table with grouping. Any ideas would be appreciated...

Thanks.
I am having the same problem although I'm not sure it is the grouping that breaks it. I have many reports, and after converting to 2005 some of them export data in the table to csv and others do not. I am unable to find any differences between the reports that work and those that don't - all of the having groups in the table. I am disappointed that the original post was back in February and there have been no responses?|||I fixed this by changing the Output property of the table from Auto to Yes. Although I'm still not sure why it was working on some reports and not others. Both had the same conditional expressions for visibility in them and the Output property of both were set to Auto.

Export to CSV and oepn in Excel

Is there a way to export to CSV and open the file directly in Excel without
saving it first? And, to have Excel parse the columns correctly when opening?
I can save the file and then import it in another step into Excel without
problems. But, when I Open in Excel instead of saving it first it has all the
exported csv columns in a single Excel column, i.e. Excel did not parse it
into a spreadsheet format.
Thanks,
SteveOn Jan 15, 4:46 pm, Steve <MyNoS...@.NoSpam.org> wrote:
> Is there a way to export to CSV and open the file directly in Excel without
> saving it first? And, to have Excel parse the columns correctly when opening?
> I can save the file and then import it in another step into Excel without
> problems. But, when I Open in Excel instead of saving it first it has all the
> exported csv columns in a single Excel column, i.e. Excel did not parse it
> into a spreadsheet format.
> Thanks,
> Steve
You might try setting the delimiter programmatically and use Microsoft
Automation to open the file using Excel. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||The problem here is that the default for CSV export is Unicode which Excel
puts all into one column (I don't know about the latest version of excel but
Excel 2003 and earlier does).
RS 2005 you can set this in config file to default to ASCII instead of
Unicode. Then it will do exactly as you want.
RS 2000 I added a link to my report and used Jump To URL to open up in ASCII
CSV format.
RS 2005 solution:
RSReportServer.Config file make a backup before changing.
<!--
<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>
Note how I commented out the original and then added the new configuration.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Steve" <MyNoSpam@.NoSpam.org> wrote in message
news:65404A6E-931E-482F-B1B5-A4C7374E9FA8@.microsoft.com...
> Is there a way to export to CSV and open the file directly in Excel
> without
> saving it first? And, to have Excel parse the columns correctly when
> opening?
> I can save the file and then import it in another step into Excel without
> problems. But, when I Open in Excel instead of saving it first it has all
> the
> exported csv columns in a single Excel column, i.e. Excel did not parse it
> into a spreadsheet format.
> Thanks,
> Steve
>|||Bruce,
We are still using RS 2000 and Excel 2003, so I tried your Jump To URL
suggestion and it works great.
I also tried modifying the config as suggested and restarting the report
server, but that didn't work for us. So, I guess unless I typed something
incorrectly that is strictly a RS 2005 solution?
Thanks much!
Regards,
Steve
"Bruce L-C [MVP]" wrote:
> The problem here is that the default for CSV export is Unicode which Excel
> puts all into one column (I don't know about the latest version of excel but
> Excel 2003 and earlier does).
> RS 2005 you can set this in config file to default to ASCII instead of
> Unicode. Then it will do exactly as you want.
> RS 2000 I added a link to my report and used Jump To URL to open up in ASCII
> CSV format.
> RS 2005 solution:
> RSReportServer.Config file make a backup before changing.
> <!--
> <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>
> Note how I commented out the original and then added the new configuration.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Steve" <MyNoSpam@.NoSpam.org> wrote in message
> news:65404A6E-931E-482F-B1B5-A4C7374E9FA8@.microsoft.com...
> > Is there a way to export to CSV and open the file directly in Excel
> > without
> > saving it first? And, to have Excel parse the columns correctly when
> > opening?
> >
> > I can save the file and then import it in another step into Excel without
> > problems. But, when I Open in Excel instead of saving it first it has all
> > the
> > exported csv columns in a single Excel column, i.e. Excel did not parse it
> > into a spreadsheet format.
> >
> > Thanks,
> > Steve
> >
>
>|||Yes, it is a RS 2005 only solution.
As you have seen in RS 2000, exporting to CSV to get data into Exel is much
faster. Excel rendering is a good bit better in RS 2005 and I expect it will
be even better in RS 2008. Although I have CSV export configured as ASCII in
RS 2005 my users very seldom need to do that anymore. Pretty much they can
export to Excel with good enough performance.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Steve" <MyNoSpam@.NoSpam.org> wrote in message
news:F9F9DFB8-52CF-49C0-8E88-023B45C7CBDD@.microsoft.com...
> Bruce,
> We are still using RS 2000 and Excel 2003, so I tried your Jump To URL
> suggestion and it works great.
> I also tried modifying the config as suggested and restarting the report
> server, but that didn't work for us. So, I guess unless I typed something
> incorrectly that is strictly a RS 2005 solution?
> Thanks much!
> Regards,
> Steve
> "Bruce L-C [MVP]" wrote:
>> The problem here is that the default for CSV export is Unicode which
>> Excel
>> puts all into one column (I don't know about the latest version of excel
>> but
>> Excel 2003 and earlier does).
>> RS 2005 you can set this in config file to default to ASCII instead of
>> Unicode. Then it will do exactly as you want.
>> RS 2000 I added a link to my report and used Jump To URL to open up in
>> ASCII
>> CSV format.
>> RS 2005 solution:
>> RSReportServer.Config file make a backup before changing.
>> <!--
>> <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>
>> Note how I commented out the original and then added the new
>> configuration.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Steve" <MyNoSpam@.NoSpam.org> wrote in message
>> news:65404A6E-931E-482F-B1B5-A4C7374E9FA8@.microsoft.com...
>> > Is there a way to export to CSV and open the file directly in Excel
>> > without
>> > saving it first? And, to have Excel parse the columns correctly when
>> > opening?
>> >
>> > I can save the file and then import it in another step into Excel
>> > without
>> > problems. But, when I Open in Excel instead of saving it first it has
>> > all
>> > the
>> > exported csv columns in a single Excel column, i.e. Excel did not parse
>> > it
>> > into a spreadsheet format.
>> >
>> > Thanks,
>> > Steve
>> >
>>