Does anyone know if SP1 includes any fixes to the file size of Excel files. We are trying to replace an old reporting tool with RS; however, when exporting a relativly small report the Excel file in 5-15 MB. Has SP1 addressed this issue? There are similar problems with PDF files as well.SP1 includes a major change to the Excel renderer. Rather than generating
MHTML (which ends up being quite large), we now generate native Excel files
(which tend to be smaller unless you have a large images in your report).
This has the added benefit of working with older versions of Excel.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"sstevens" <sstevens@.discussions.microsoft.com> wrote in message
news:3CFB37E3-C8E4-4B06-8CBB-1B2CE31EE347@.microsoft.com...
> Does anyone know if SP1 includes any fixes to the file size of Excel
files. We are trying to replace an old reporting tool with RS; however,
when exporting a relativly small report the Excel file in 5-15 MB. Has SP1
addressed this issue? There are similar problems with PDF files as well.|||Thanks Chris! I just got SP1 up and running. With doing nothing but installing SP1 the render time and the file size have both been cut in half.
"Chris Hays [MSFT]" wrote:
> SP1 includes a major change to the Excel renderer. Rather than generating
> MHTML (which ends up being quite large), we now generate native Excel files
> (which tend to be smaller unless you have a large images in your report).
> This has the added benefit of working with older versions of Excel.
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "sstevens" <sstevens@.discussions.microsoft.com> wrote in message
> news:3CFB37E3-C8E4-4B06-8CBB-1B2CE31EE347@.microsoft.com...
> > Does anyone know if SP1 includes any fixes to the file size of Excel
> files. We are trying to replace an old reporting tool with RS; however,
> when exporting a relativly small report the Excel file in 5-15 MB. Has SP1
> addressed this issue? There are similar problems with PDF files as well.
>
>sql
Showing posts with label file. Show all posts
Showing posts with label file. Show all posts
Thursday, March 29, 2012
Excel File Size after upgrade to SRS 2005
We have upgraded our RS 2000 instance to SRS 2005. We have seen that
reports exported to excel have gotten huge since the upgrade. For
example, we had a report that when run in RS 2000, would export to an
excel file that was 900KB. The same report exported to excel in SRS
2005 is now 12 meg! Is this because the original report was designed
in VS 2003? Would it improve if the report was re-deployed from VS
2005?Hey just check in your (huge) excel file is there any spaces or no of sheets
have been added. If spaces are there on your sheets will also increase the
size of the file.
Amarnath
"dachrist28@.gmail.com" wrote:
> We have upgraded our RS 2000 instance to SRS 2005. We have seen that
> reports exported to excel have gotten huge since the upgrade. For
> example, we had a report that when run in RS 2000, would export to an
> excel file that was 900KB. The same report exported to excel in SRS
> 2005 is now 12 meg! Is this because the original report was designed
> in VS 2003? Would it improve if the report was re-deployed from VS
> 2005?
>sql
reports exported to excel have gotten huge since the upgrade. For
example, we had a report that when run in RS 2000, would export to an
excel file that was 900KB. The same report exported to excel in SRS
2005 is now 12 meg! Is this because the original report was designed
in VS 2003? Would it improve if the report was re-deployed from VS
2005?Hey just check in your (huge) excel file is there any spaces or no of sheets
have been added. If spaces are there on your sheets will also increase the
size of the file.
Amarnath
"dachrist28@.gmail.com" wrote:
> We have upgraded our RS 2000 instance to SRS 2005. We have seen that
> reports exported to excel have gotten huge since the upgrade. For
> example, we had a report that when run in RS 2000, would export to an
> excel file that was 900KB. The same report exported to excel in SRS
> 2005 is now 12 meg! Is this because the original report was designed
> in VS 2003? Would it improve if the report was re-deployed from VS
> 2005?
>sql
Tuesday, March 27, 2012
Excel file size - huge! - on Prod server
When I run a report in Reporting Services on the Report Server on my
local PC, I can save the report to Excel as reasonable file sizes eg.
700K. When I deploy the same report's rdl to the live server, run it
and then save the same report to my network share, the file size
explodes to several meg (anything from 5mb to 15 mb). Anyone know why
this happens and how I can prevent it?My guess is that your local one has SP1 and the live one does not. SP1
changed to native format instead of mhtml.
Check versions by going to //yourserver/reportserver
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Vivienne" <vivienne00@.yahoo.com> wrote in message
news:1107967800.202356.71560@.c13g2000cwb.googlegroups.com...
> When I run a report in Reporting Services on the Report Server on my
> local PC, I can save the report to Excel as reasonable file sizes eg.
> 700K. When I deploy the same report's rdl to the live server, run it
> and then save the same report to my network share, the file size
> explodes to several meg (anything from 5mb to 15 mb). Anyone know why
> this happens and how I can prevent it?
>
local PC, I can save the report to Excel as reasonable file sizes eg.
700K. When I deploy the same report's rdl to the live server, run it
and then save the same report to my network share, the file size
explodes to several meg (anything from 5mb to 15 mb). Anyone know why
this happens and how I can prevent it?My guess is that your local one has SP1 and the live one does not. SP1
changed to native format instead of mhtml.
Check versions by going to //yourserver/reportserver
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Vivienne" <vivienne00@.yahoo.com> wrote in message
news:1107967800.202356.71560@.c13g2000cwb.googlegroups.com...
> When I run a report in Reporting Services on the Report Server on my
> local PC, I can save the report to Excel as reasonable file sizes eg.
> 700K. When I deploy the same report's rdl to the live server, run it
> and then save the same report to my network share, the file size
> explodes to several meg (anything from 5mb to 15 mb). Anyone know why
> this happens and how I can prevent it?
>
Excel File Locked
I'm building a package that has a Data Flow which reads an Excel File using the Excel Source connection - during development I hit errors in steps after the excel read. This is causing the excel file to be locked up and the next run fails on excel connection problem. If I go to Excel I can't open the file either. I have to exit BIDS and then file gets released. Is there any setting / different approach that would prevent this from happening?
thx
I doubt there is a setting - its just unfortunate I guess.
This probably won't happen if you run the package from the command line - so try that!
-Jamie
Excel file loading in SSIS
Hi All,
We did the development of SSIS packges on 32 bit machine. We have few excel files which is loaded using SSIS.
Now the same was deployed to anothe rmachine(64 bit). This 64-bit machine does not have Microsoft office installed.
And all packages(which loads excel files) failed. Hence can someone answer my following questions:-
1) In order to load excel files using SSIS, is it necessary that Microsoft excel software should be installed on that machine?
2) If answer to above is yes, Can Microsoft excel viewer be used instead of Microsoft office(excel)?
Thanks
Sid
Hi,
I am not an expert in SSIS but i have tried importing excel source data to sql using ssis package in a machine that does not have MS Office installed, but it worked for me.
Thanks,
OM$.
|||
Thanks for reply: Here is my problem:
I run those SSIS packages (which loads excel file) by double clicking it or running it through SSIS editor. It works great.
However if I run it using the command line dtexec.exe then following error arises: -
Error: 2007-08-03 16:53:27.42
Code: 0xC0202009
Source: PkgExtract Connection manager "SRC_Connection"
Description: An OLE DB error has occurred. Error code: 0x80040154.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".
End Error
Error: 2007-08-03 16:53:27.42
Code: 0xC020801C
Source: Data Flow Task - Extract Data Excel Source [1860]
Description: The AcquireConnection method call to the connection manager "SRC_Connection" failed with error code 0xC0202009.
End Error
Error: 2007-08-03 16:53:27.43
Code: 0xC0047017
Source: Data Flow Task - Extract Data DTS.Pipeline
Description: component "Excel Source" (1860) failed validation and returned error code 0xC020801C.
End Error
See your other post on this.
There is no 64-bit driver for Excel. You need to run the 32-bit version of DTEXEC (located in C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn).
|||
Excel file import problem
Hello:
I need to import an Excel file to SQL Server.
The .xls file has the column names which contains
dot inside, like AAA.BBB. When I import this file
in SQL using DTS Import/Export tool, it creates a table
with column names like AAA#BBB.
So, during import process the dots substitutes with #.
Could you, please, give me a hint how to fix the problem?
Thanks,
GB"GB" wrote:
> Hello:
> I need to import an Excel file to SQL Server.
> The .xls file has the column names which contains
> dot inside, like AAA.BBB. When I import this file
> in SQL using DTS Import/Export tool, it creates a table
> with column names like AAA#BBB.
> So, during import process the dots substitutes with #.
> Could you, please, give me a hint how to fix the problem?
> Thanks,
> GB
>
> Do not use import/export in dts - nto very flexible.
Use Transform Data Task
1- sel. excel conn. (choose .xls doc)
2- sel. sql conn. (choose dbase and passwords if needed)
3-highlight excel conn and ctrl sql conn
4- sel. Transform Data task
5- open Trans data Task(double click arrow)
Source is excel - choose table/view (select sheet in excel) OR SQL Query
Destination is SQL - choose table name or create new (you can change created
name(usually default sheet1$ if you have no named tabs) to another name and
dts will load a new tab into excel)
Transformation shows links between cols from source to destin.
(open transform to make sure all is ok)
click OK for transformation.
arrow should be highlighted - just execute it !|||
"GB" wrote:
> Hello:
> I need to import an Excel file to SQL Server.
> The .xls file has the column names which contains
> dot inside, like AAA.BBB. When I import this file
> in SQL using DTS Import/Export tool, it creates a table
> with column names like AAA#BBB.
> So, during import process the dots substitutes with #.
> Could you, please, give me a hint how to fix the problem?
> Thanks,
> GB
>
> I forgot 1 piece.
In DataTransformation Task
When you select the destination - Create the table - change AAA#BBB to AAA.B
BB
OR if you have alot of columns - copy the whole create stmt to textpad -
replace all # with . and recopy into dts.
I need to import an Excel file to SQL Server.
The .xls file has the column names which contains
dot inside, like AAA.BBB. When I import this file
in SQL using DTS Import/Export tool, it creates a table
with column names like AAA#BBB.
So, during import process the dots substitutes with #.
Could you, please, give me a hint how to fix the problem?
Thanks,
GB"GB" wrote:
> Hello:
> I need to import an Excel file to SQL Server.
> The .xls file has the column names which contains
> dot inside, like AAA.BBB. When I import this file
> in SQL using DTS Import/Export tool, it creates a table
> with column names like AAA#BBB.
> So, during import process the dots substitutes with #.
> Could you, please, give me a hint how to fix the problem?
> Thanks,
> GB
>
> Do not use import/export in dts - nto very flexible.
Use Transform Data Task
1- sel. excel conn. (choose .xls doc)
2- sel. sql conn. (choose dbase and passwords if needed)
3-highlight excel conn and ctrl sql conn
4- sel. Transform Data task
5- open Trans data Task(double click arrow)
Source is excel - choose table/view (select sheet in excel) OR SQL Query
Destination is SQL - choose table name or create new (you can change created
name(usually default sheet1$ if you have no named tabs) to another name and
dts will load a new tab into excel)
Transformation shows links between cols from source to destin.
(open transform to make sure all is ok)
click OK for transformation.
arrow should be highlighted - just execute it !|||
"GB" wrote:
> Hello:
> I need to import an Excel file to SQL Server.
> The .xls file has the column names which contains
> dot inside, like AAA.BBB. When I import this file
> in SQL using DTS Import/Export tool, it creates a table
> with column names like AAA#BBB.
> So, during import process the dots substitutes with #.
> Could you, please, give me a hint how to fix the problem?
> Thanks,
> GB
>
> I forgot 1 piece.
In DataTransformation Task
When you select the destination - Create the table - change AAA#BBB to AAA.B
BB
OR if you have alot of columns - copy the whole create stmt to textpad -
replace all # with . and recopy into dts.
Excel file as data source
Hi everyone!
I am trying to import data into my sqlserver 2005 database from an Excel 2000 file. The database is empty. I am using the worksheets from the file to create the tables and copy the rows. I am getting follwing errors:
- Pre-execute (Error)
Messages
Error 0xc0202009: {674E15E4-102E-4935-90A2-8B1FFFEFB11D}: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error".
(SQL Server Import and Export Wizard)
Error 0xc020801c: Data Flow Task: The AcquireConnection method call to the connection manager "SourceConnectionExcel" failed with error code 0xC0202009.
(SQL Server Import and Export Wizard)
Error 0xc004701a: Data Flow Task: component "Source 64 - vw_TempOrderDetails" (5280) failed the pre-execute phase and returned error code 0xC020801C.
(SQL Server Import and Export Wizard)
Any suggestion is most welcome.
Regards
Have you read this:http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=160340&SiteID=1 ?|||Hi Mike,
Many thanks for the reply. I will check out this thread and come back.
Regards
|||Hi!
I went throught the thread but still am unable to figure it out. Will search elsewhere and revert back.
Regards
|||Though it looks the Import Export Wizard is not working properly ... you can use one of the alternative method .
1Use Distributed Queries2If you donot wantto configure a persistent connectionto the Excel workbookas a linked server, you can import datafor a specific purposeby using theOPENDATASOURCEor theOPENROWSET function. The following code samples also import the datafrom the Excel Customers worksheetinto new SQL Server tables:SELECT *INTO XLImport3FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',3'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]45SELECT *INTO XLImport4FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',6'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])78SELECT *INTO XLImport5FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',9'Excel 8.0;Database=C:\test\xltest.xls','SELECT * FROM [Customers$]')1011http://support.microsoft.com/kb/321686
Hope this will help you
This is excerpt from msdnlink...
|||Hi Satya,
Thanks for the reply. I hope this will work. But I will try to work around the alternative mentioned in the subject thread and present the solution.
BTW: I am from New Delhi, India.
Regards,
Venu
sql
Excel Exporting results in MIME message
I reviewed all post but have not seen the problem I have.
When exporting a report to Excel (regardless if I save or open the file) my
output is a multi-part MIME message. I get only one worksheet (there are 4
pages in the report) and it has the name of the exported report.
There is a section for each page in this message and a StyleSheet section.
Did I miss something in the report Services set-up?
Thanks
ShawnI took a better look at the rest of the posts and did find some with the same
problem. One of them suggested installation of SP1 in MS Office 2000. My
problem now is that I did install SP1 and nothing changed. If anyone has
another suggestion it will be greatly appreciated.
Thanks
Shawn
"Shawn Kralj" wrote:
> I reviewed all post but have not seen the problem I have.
> When exporting a report to Excel (regardless if I save or open the file) my
> output is a multi-part MIME message. I get only one worksheet (there are 4
> pages in the report) and it has the name of the exported report.
> There is a section for each page in this message and a StyleSheet section.
> Did I miss something in the report Services set-up?
> Thanks
> Shawn|||Close, it is not SP1. It is SP1 for Reporting Services that has the fix. SP1
no longer uses the MIME format.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Shawn Kralj" <ShawnKralj@.discussions.microsoft.com> wrote in message
news:FE6555D6-71F4-4C29-90F4-DA92604B4816@.microsoft.com...
> I took a better look at the rest of the posts and did find some with the
same
> problem. One of them suggested installation of SP1 in MS Office 2000. My
> problem now is that I did install SP1 and nothing changed. If anyone has
> another suggestion it will be greatly appreciated.
> Thanks
> Shawn
> "Shawn Kralj" wrote:
> > I reviewed all post but have not seen the problem I have.
> >
> > When exporting a report to Excel (regardless if I save or open the file)
my
> > output is a multi-part MIME message. I get only one worksheet (there are
4
> > pages in the report) and it has the name of the exported report.
> >
> > There is a section for each page in this message and a StyleSheet
section.
> >
> > Did I miss something in the report Services set-up?
> >
> > Thanks
> >
> > Shawn|||Thanks Bruce. That worked. Installing SP1 for Reporting Services fixed the
problem.
Looks great.
Shawn
"Bruce L-C [MVP]" wrote:
> Close, it is not SP1. It is SP1 for Reporting Services that has the fix. SP1
> no longer uses the MIME format.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Shawn Kralj" <ShawnKralj@.discussions.microsoft.com> wrote in message
> news:FE6555D6-71F4-4C29-90F4-DA92604B4816@.microsoft.com...
> > I took a better look at the rest of the posts and did find some with the
> same
> > problem. One of them suggested installation of SP1 in MS Office 2000. My
> > problem now is that I did install SP1 and nothing changed. If anyone has
> > another suggestion it will be greatly appreciated.
> >
> > Thanks
> >
> > Shawn
> >
> > "Shawn Kralj" wrote:
> >
> > > I reviewed all post but have not seen the problem I have.
> > >
> > > When exporting a report to Excel (regardless if I save or open the file)
> my
> > > output is a multi-part MIME message. I get only one worksheet (there are
> 4
> > > pages in the report) and it has the name of the exported report.
> > >
> > > There is a section for each page in this message and a StyleSheet
> section.
> > >
> > > Did I miss something in the report Services set-up?
> > >
> > > Thanks
> > >
> > > Shawn
>
>
When exporting a report to Excel (regardless if I save or open the file) my
output is a multi-part MIME message. I get only one worksheet (there are 4
pages in the report) and it has the name of the exported report.
There is a section for each page in this message and a StyleSheet section.
Did I miss something in the report Services set-up?
Thanks
ShawnI took a better look at the rest of the posts and did find some with the same
problem. One of them suggested installation of SP1 in MS Office 2000. My
problem now is that I did install SP1 and nothing changed. If anyone has
another suggestion it will be greatly appreciated.
Thanks
Shawn
"Shawn Kralj" wrote:
> I reviewed all post but have not seen the problem I have.
> When exporting a report to Excel (regardless if I save or open the file) my
> output is a multi-part MIME message. I get only one worksheet (there are 4
> pages in the report) and it has the name of the exported report.
> There is a section for each page in this message and a StyleSheet section.
> Did I miss something in the report Services set-up?
> Thanks
> Shawn|||Close, it is not SP1. It is SP1 for Reporting Services that has the fix. SP1
no longer uses the MIME format.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Shawn Kralj" <ShawnKralj@.discussions.microsoft.com> wrote in message
news:FE6555D6-71F4-4C29-90F4-DA92604B4816@.microsoft.com...
> I took a better look at the rest of the posts and did find some with the
same
> problem. One of them suggested installation of SP1 in MS Office 2000. My
> problem now is that I did install SP1 and nothing changed. If anyone has
> another suggestion it will be greatly appreciated.
> Thanks
> Shawn
> "Shawn Kralj" wrote:
> > I reviewed all post but have not seen the problem I have.
> >
> > When exporting a report to Excel (regardless if I save or open the file)
my
> > output is a multi-part MIME message. I get only one worksheet (there are
4
> > pages in the report) and it has the name of the exported report.
> >
> > There is a section for each page in this message and a StyleSheet
section.
> >
> > Did I miss something in the report Services set-up?
> >
> > Thanks
> >
> > Shawn|||Thanks Bruce. That worked. Installing SP1 for Reporting Services fixed the
problem.
Looks great.
Shawn
"Bruce L-C [MVP]" wrote:
> Close, it is not SP1. It is SP1 for Reporting Services that has the fix. SP1
> no longer uses the MIME format.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Shawn Kralj" <ShawnKralj@.discussions.microsoft.com> wrote in message
> news:FE6555D6-71F4-4C29-90F4-DA92604B4816@.microsoft.com...
> > I took a better look at the rest of the posts and did find some with the
> same
> > problem. One of them suggested installation of SP1 in MS Office 2000. My
> > problem now is that I did install SP1 and nothing changed. If anyone has
> > another suggestion it will be greatly appreciated.
> >
> > Thanks
> >
> > Shawn
> >
> > "Shawn Kralj" wrote:
> >
> > > I reviewed all post but have not seen the problem I have.
> > >
> > > When exporting a report to Excel (regardless if I save or open the file)
> my
> > > output is a multi-part MIME message. I get only one worksheet (there are
> 4
> > > pages in the report) and it has the name of the exported report.
> > >
> > > There is a section for each page in this message and a StyleSheet
> section.
> > >
> > > Did I miss something in the report Services set-up?
> > >
> > > Thanks
> > >
> > > Shawn
>
>
Excel Export Large File Size
When I export a matrix report to Excel (65 rows by 228 columns, about 15000
cells) the file size exceeds 15mb.
As the Excel file is sent out daily as an e-mail attachment in an SRSS
subscription I receive a lot of complaints about this.
Other export formats for the same report such as Web archive are tiny, but
the subscribers insist on Excel.
I have applied the latest service packs to SQL Server 2005.
Any tips/workarounds would be appreciated.
Regards
Ian OlsenOn Mar 22, 7:47 am, Wrighty <wrig...@.donotspam.com> wrote:
> When I export a matrix report to Excel (65 rows by 228 columns, about 15000
> cells) the file size exceeds 15mb.
> As the Excel file is sent out daily as an e-mail attachment in an SRSS
> subscription I receive a lot of complaints about this.
> Other export formats for the same report such as Web archive are tiny, but
> the subscribers insist on Excel.
> I have applied the latest service packs to SQL Server 2005.
> Any tips/workarounds would be appreciated.
> Regards
> Ian Olsen
The only other options are to use a compression tool to zip the file
before sending it (most likely, not a snapshot option, but a custom
one) or to export to CSV instead (which might save a little file size
but you loose formatting). The only other thing would be to have the
users save a web archive, etc as an excel file. Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer
cells) the file size exceeds 15mb.
As the Excel file is sent out daily as an e-mail attachment in an SRSS
subscription I receive a lot of complaints about this.
Other export formats for the same report such as Web archive are tiny, but
the subscribers insist on Excel.
I have applied the latest service packs to SQL Server 2005.
Any tips/workarounds would be appreciated.
Regards
Ian OlsenOn Mar 22, 7:47 am, Wrighty <wrig...@.donotspam.com> wrote:
> When I export a matrix report to Excel (65 rows by 228 columns, about 15000
> cells) the file size exceeds 15mb.
> As the Excel file is sent out daily as an e-mail attachment in an SRSS
> subscription I receive a lot of complaints about this.
> Other export formats for the same report such as Web archive are tiny, but
> the subscribers insist on Excel.
> I have applied the latest service packs to SQL Server 2005.
> Any tips/workarounds would be appreciated.
> Regards
> Ian Olsen
The only other options are to use a compression tool to zip the file
before sending it (most likely, not a snapshot option, but a custom
one) or to export to CSV instead (which might save a little file size
but you loose formatting). The only other thing would be to have the
users save a web archive, etc as an excel file. Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer
Excel Export Error - Excel Can't Open File, CPU 100% Utilization
I've got an issue I cannot seem to address. We've upgraded to SP1, and I
still am not able to open Excel files exported from Reporting Services if
they contain more than a few hundred rows.
For example, I have a report which contains 1453 records. Exporting to an
Excel takes a few minutes, then generates a 7 MB file. Attempting to open
that file in Excel 2003 pegs my CPU utlilzation at 100%, and Excel steadily
consumes 8K of RAM per second, yet nothing happens. Excel will either
display as "Not Responding" in Task Manager, or will display "Running" and I
cannot switch back to Excel.
I can export to CSV and Excel will open it with no issues. However,
exporting to Excel is critical to our business' use of Reporting Services.
Please advise if anyone has a workaround or is encountering this issue.
--
Aaron Berquist
Business Systems Analyst, Information Services
Open Text CorporationCould you send me the excel file generated for a smaller number rows,
please?
--
Nico Cristache [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aaron Berquist" <aberquis@.opentext.com> wrote in message
news:e0DFZ2BaEHA.3404@.TK2MSFTNGP11.phx.gbl...
> I've got an issue I cannot seem to address. We've upgraded to SP1, and I
> still am not able to open Excel files exported from Reporting Services if
> they contain more than a few hundred rows.
> For example, I have a report which contains 1453 records. Exporting to an
> Excel takes a few minutes, then generates a 7 MB file. Attempting to open
> that file in Excel 2003 pegs my CPU utlilzation at 100%, and Excel
steadily
> consumes 8K of RAM per second, yet nothing happens. Excel will either
> display as "Not Responding" in Task Manager, or will display "Running" and
I
> cannot switch back to Excel.
> I can export to CSV and Excel will open it with no issues. However,
> exporting to Excel is critical to our business' use of Reporting Services.
> Please advise if anyone has a workaround or is encountering this issue.
> --
> Aaron Berquist
> Business Systems Analyst, Information Services
> Open Text Corporation
>|||Any progress on this issue?
"Nico Cristache [MSFT]" <nipirvan@.microsoft.com> wrote in message
news:eU3AjxCaEHA.4052@.TK2MSFTNGP10.phx.gbl...
> Could you send me the excel file generated for a smaller number rows,
> please?
> --
> Nico Cristache [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Aaron Berquist" <aberquis@.opentext.com> wrote in message
> news:e0DFZ2BaEHA.3404@.TK2MSFTNGP11.phx.gbl...
> > I've got an issue I cannot seem to address. We've upgraded to SP1, and I
> > still am not able to open Excel files exported from Reporting Services
if
> > they contain more than a few hundred rows.
> >
> > For example, I have a report which contains 1453 records. Exporting to
an
> > Excel takes a few minutes, then generates a 7 MB file. Attempting to
open
> > that file in Excel 2003 pegs my CPU utlilzation at 100%, and Excel
> steadily
> > consumes 8K of RAM per second, yet nothing happens. Excel will either
> > display as "Not Responding" in Task Manager, or will display "Running"
and
> I
> > cannot switch back to Excel.
> >
> > I can export to CSV and Excel will open it with no issues. However,
> > exporting to Excel is critical to our business' use of Reporting
Services.
> >
> > Please advise if anyone has a workaround or is encountering this issue.
> > --
> > Aaron Berquist
> > Business Systems Analyst, Information Services
> > Open Text Corporation
> >
> >
>|||Well, I solved the problem!
The header on the report was causing the issue, as the fields were not lined
up with the data columns on my report, so I think Excel was goign nuts
trying to parse out all the merged columns. By making sure that the
descriptions in the header matched eneded at the same point as a column, the
file now takes about 50 secs to open on a Pentium 3 1Ghz machine.
So, when designing a header, make sure your test boxes line up with your
columns!
Thanks,
Aaron
"Aaron Berquist" <aberquis@.opentext.com> wrote in message
news:erwtoi$bEHA.2408@.tk2msftngp13.phx.gbl...
> Any progress on this issue?
> "Nico Cristache [MSFT]" <nipirvan@.microsoft.com> wrote in message
> news:eU3AjxCaEHA.4052@.TK2MSFTNGP10.phx.gbl...
> > Could you send me the excel file generated for a smaller number rows,
> > please?
> >
> > --
> > Nico Cristache [MSFT]
> > Microsoft SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "Aaron Berquist" <aberquis@.opentext.com> wrote in message
> > news:e0DFZ2BaEHA.3404@.TK2MSFTNGP11.phx.gbl...
> > > I've got an issue I cannot seem to address. We've upgraded to SP1, and
I
> > > still am not able to open Excel files exported from Reporting Services
> if
> > > they contain more than a few hundred rows.
> > >
> > > For example, I have a report which contains 1453 records. Exporting to
> an
> > > Excel takes a few minutes, then generates a 7 MB file. Attempting to
> open
> > > that file in Excel 2003 pegs my CPU utlilzation at 100%, and Excel
> > steadily
> > > consumes 8K of RAM per second, yet nothing happens. Excel will either
> > > display as "Not Responding" in Task Manager, or will display "Running"
> and
> > I
> > > cannot switch back to Excel.
> > >
> > > I can export to CSV and Excel will open it with no issues. However,
> > > exporting to Excel is critical to our business' use of Reporting
> Services.
> > >
> > > Please advise if anyone has a workaround or is encountering this
issue.
> > > --
> > > Aaron Berquist
> > > Business Systems Analyst, Information Services
> > > Open Text Corporation
> > >
> > >
> >
> >
>|||There is also a deviceinfo setting to put the page header into the Excel
header, rather than the sheets.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aaron Berquist" <aberquis@.opentext.com> wrote in message
news:%23TxUDQLcEHA.3012@.tk2msftngp13.phx.gbl...
> Well, I solved the problem!
> The header on the report was causing the issue, as the fields were not
> lined
> up with the data columns on my report, so I think Excel was goign nuts
> trying to parse out all the merged columns. By making sure that the
> descriptions in the header matched eneded at the same point as a column,
> the
> file now takes about 50 secs to open on a Pentium 3 1Ghz machine.
> So, when designing a header, make sure your test boxes line up with your
> columns!
> Thanks,
> Aaron
> "Aaron Berquist" <aberquis@.opentext.com> wrote in message
> news:erwtoi$bEHA.2408@.tk2msftngp13.phx.gbl...
>> Any progress on this issue?
>> "Nico Cristache [MSFT]" <nipirvan@.microsoft.com> wrote in message
>> news:eU3AjxCaEHA.4052@.TK2MSFTNGP10.phx.gbl...
>> > Could you send me the excel file generated for a smaller number rows,
>> > please?
>> >
>> > --
>> > Nico Cristache [MSFT]
>> > Microsoft SQL Server Reporting Services
>> >
>> > This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> >
>> >
>> > "Aaron Berquist" <aberquis@.opentext.com> wrote in message
>> > news:e0DFZ2BaEHA.3404@.TK2MSFTNGP11.phx.gbl...
>> > > I've got an issue I cannot seem to address. We've upgraded to SP1,
>> > > and
> I
>> > > still am not able to open Excel files exported from Reporting
>> > > Services
>> if
>> > > they contain more than a few hundred rows.
>> > >
>> > > For example, I have a report which contains 1453 records. Exporting
>> > > to
>> an
>> > > Excel takes a few minutes, then generates a 7 MB file. Attempting to
>> open
>> > > that file in Excel 2003 pegs my CPU utlilzation at 100%, and Excel
>> > steadily
>> > > consumes 8K of RAM per second, yet nothing happens. Excel will either
>> > > display as "Not Responding" in Task Manager, or will display
>> > > "Running"
>> and
>> > I
>> > > cannot switch back to Excel.
>> > >
>> > > I can export to CSV and Excel will open it with no issues. However,
>> > > exporting to Excel is critical to our business' use of Reporting
>> Services.
>> > >
>> > > Please advise if anyone has a workaround or is encountering this
> issue.
>> > > --
>> > > Aaron Berquist
>> > > Business Systems Analyst, Information Services
>> > > Open Text Corporation
>> > >
>> > >
>> >
>> >
>>
>
still am not able to open Excel files exported from Reporting Services if
they contain more than a few hundred rows.
For example, I have a report which contains 1453 records. Exporting to an
Excel takes a few minutes, then generates a 7 MB file. Attempting to open
that file in Excel 2003 pegs my CPU utlilzation at 100%, and Excel steadily
consumes 8K of RAM per second, yet nothing happens. Excel will either
display as "Not Responding" in Task Manager, or will display "Running" and I
cannot switch back to Excel.
I can export to CSV and Excel will open it with no issues. However,
exporting to Excel is critical to our business' use of Reporting Services.
Please advise if anyone has a workaround or is encountering this issue.
--
Aaron Berquist
Business Systems Analyst, Information Services
Open Text CorporationCould you send me the excel file generated for a smaller number rows,
please?
--
Nico Cristache [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aaron Berquist" <aberquis@.opentext.com> wrote in message
news:e0DFZ2BaEHA.3404@.TK2MSFTNGP11.phx.gbl...
> I've got an issue I cannot seem to address. We've upgraded to SP1, and I
> still am not able to open Excel files exported from Reporting Services if
> they contain more than a few hundred rows.
> For example, I have a report which contains 1453 records. Exporting to an
> Excel takes a few minutes, then generates a 7 MB file. Attempting to open
> that file in Excel 2003 pegs my CPU utlilzation at 100%, and Excel
steadily
> consumes 8K of RAM per second, yet nothing happens. Excel will either
> display as "Not Responding" in Task Manager, or will display "Running" and
I
> cannot switch back to Excel.
> I can export to CSV and Excel will open it with no issues. However,
> exporting to Excel is critical to our business' use of Reporting Services.
> Please advise if anyone has a workaround or is encountering this issue.
> --
> Aaron Berquist
> Business Systems Analyst, Information Services
> Open Text Corporation
>|||Any progress on this issue?
"Nico Cristache [MSFT]" <nipirvan@.microsoft.com> wrote in message
news:eU3AjxCaEHA.4052@.TK2MSFTNGP10.phx.gbl...
> Could you send me the excel file generated for a smaller number rows,
> please?
> --
> Nico Cristache [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Aaron Berquist" <aberquis@.opentext.com> wrote in message
> news:e0DFZ2BaEHA.3404@.TK2MSFTNGP11.phx.gbl...
> > I've got an issue I cannot seem to address. We've upgraded to SP1, and I
> > still am not able to open Excel files exported from Reporting Services
if
> > they contain more than a few hundred rows.
> >
> > For example, I have a report which contains 1453 records. Exporting to
an
> > Excel takes a few minutes, then generates a 7 MB file. Attempting to
open
> > that file in Excel 2003 pegs my CPU utlilzation at 100%, and Excel
> steadily
> > consumes 8K of RAM per second, yet nothing happens. Excel will either
> > display as "Not Responding" in Task Manager, or will display "Running"
and
> I
> > cannot switch back to Excel.
> >
> > I can export to CSV and Excel will open it with no issues. However,
> > exporting to Excel is critical to our business' use of Reporting
Services.
> >
> > Please advise if anyone has a workaround or is encountering this issue.
> > --
> > Aaron Berquist
> > Business Systems Analyst, Information Services
> > Open Text Corporation
> >
> >
>|||Well, I solved the problem!
The header on the report was causing the issue, as the fields were not lined
up with the data columns on my report, so I think Excel was goign nuts
trying to parse out all the merged columns. By making sure that the
descriptions in the header matched eneded at the same point as a column, the
file now takes about 50 secs to open on a Pentium 3 1Ghz machine.
So, when designing a header, make sure your test boxes line up with your
columns!
Thanks,
Aaron
"Aaron Berquist" <aberquis@.opentext.com> wrote in message
news:erwtoi$bEHA.2408@.tk2msftngp13.phx.gbl...
> Any progress on this issue?
> "Nico Cristache [MSFT]" <nipirvan@.microsoft.com> wrote in message
> news:eU3AjxCaEHA.4052@.TK2MSFTNGP10.phx.gbl...
> > Could you send me the excel file generated for a smaller number rows,
> > please?
> >
> > --
> > Nico Cristache [MSFT]
> > Microsoft SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "Aaron Berquist" <aberquis@.opentext.com> wrote in message
> > news:e0DFZ2BaEHA.3404@.TK2MSFTNGP11.phx.gbl...
> > > I've got an issue I cannot seem to address. We've upgraded to SP1, and
I
> > > still am not able to open Excel files exported from Reporting Services
> if
> > > they contain more than a few hundred rows.
> > >
> > > For example, I have a report which contains 1453 records. Exporting to
> an
> > > Excel takes a few minutes, then generates a 7 MB file. Attempting to
> open
> > > that file in Excel 2003 pegs my CPU utlilzation at 100%, and Excel
> > steadily
> > > consumes 8K of RAM per second, yet nothing happens. Excel will either
> > > display as "Not Responding" in Task Manager, or will display "Running"
> and
> > I
> > > cannot switch back to Excel.
> > >
> > > I can export to CSV and Excel will open it with no issues. However,
> > > exporting to Excel is critical to our business' use of Reporting
> Services.
> > >
> > > Please advise if anyone has a workaround or is encountering this
issue.
> > > --
> > > Aaron Berquist
> > > Business Systems Analyst, Information Services
> > > Open Text Corporation
> > >
> > >
> >
> >
>|||There is also a deviceinfo setting to put the page header into the Excel
header, rather than the sheets.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aaron Berquist" <aberquis@.opentext.com> wrote in message
news:%23TxUDQLcEHA.3012@.tk2msftngp13.phx.gbl...
> Well, I solved the problem!
> The header on the report was causing the issue, as the fields were not
> lined
> up with the data columns on my report, so I think Excel was goign nuts
> trying to parse out all the merged columns. By making sure that the
> descriptions in the header matched eneded at the same point as a column,
> the
> file now takes about 50 secs to open on a Pentium 3 1Ghz machine.
> So, when designing a header, make sure your test boxes line up with your
> columns!
> Thanks,
> Aaron
> "Aaron Berquist" <aberquis@.opentext.com> wrote in message
> news:erwtoi$bEHA.2408@.tk2msftngp13.phx.gbl...
>> Any progress on this issue?
>> "Nico Cristache [MSFT]" <nipirvan@.microsoft.com> wrote in message
>> news:eU3AjxCaEHA.4052@.TK2MSFTNGP10.phx.gbl...
>> > Could you send me the excel file generated for a smaller number rows,
>> > please?
>> >
>> > --
>> > Nico Cristache [MSFT]
>> > Microsoft SQL Server Reporting Services
>> >
>> > This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> >
>> >
>> > "Aaron Berquist" <aberquis@.opentext.com> wrote in message
>> > news:e0DFZ2BaEHA.3404@.TK2MSFTNGP11.phx.gbl...
>> > > I've got an issue I cannot seem to address. We've upgraded to SP1,
>> > > and
> I
>> > > still am not able to open Excel files exported from Reporting
>> > > Services
>> if
>> > > they contain more than a few hundred rows.
>> > >
>> > > For example, I have a report which contains 1453 records. Exporting
>> > > to
>> an
>> > > Excel takes a few minutes, then generates a 7 MB file. Attempting to
>> open
>> > > that file in Excel 2003 pegs my CPU utlilzation at 100%, and Excel
>> > steadily
>> > > consumes 8K of RAM per second, yet nothing happens. Excel will either
>> > > display as "Not Responding" in Task Manager, or will display
>> > > "Running"
>> and
>> > I
>> > > cannot switch back to Excel.
>> > >
>> > > I can export to CSV and Excel will open it with no issues. However,
>> > > exporting to Excel is critical to our business' use of Reporting
>> Services.
>> > >
>> > > Please advise if anyone has a workaround or is encountering this
> issue.
>> > > --
>> > > Aaron Berquist
>> > > Business Systems Analyst, Information Services
>> > > Open Text Corporation
>> > >
>> > >
>> >
>> >
>>
>
Excel Export Does not print Date
Hi Guys,
I have a date in the report Page header.
When i export this report to .pdf it shows the date in the adobe but when i
export it to excel file date does not appear
plz help me
Thanks
ChandraI've the same problem but the date-values in my report are shown as decimal
values. Do you've already a solution?
"Chandra" wrote:
> Hi Guys,
> I have a date in the report Page header.
> When i export this report to .pdf it shows the date in the adobe but when i
> export it to excel file date does not appear
> plz help me
> Thanks
> Chandra
>
I have a date in the report Page header.
When i export this report to .pdf it shows the date in the adobe but when i
export it to excel file date does not appear
plz help me
Thanks
ChandraI've the same problem but the date-values in my report are shown as decimal
values. Do you've already a solution?
"Chandra" wrote:
> Hi Guys,
> I have a date in the report Page header.
> When i export this report to .pdf it shows the date in the adobe but when i
> export it to excel file date does not appear
> plz help me
> Thanks
> Chandra
>
excel export /collapsed fails to render excel file
Trying to render a report that has three groups two expand and collapse and
have charts. The first attempts to render were successful but all later ones
fail. And excel produces the following message:
Microsoft Office Excel File Repair Log
Errors were detected in file 'C:\Documents and Settings\johnsond\Local
Settings\Temporary Internet
Files\Content.IE5\UT38LWFQ\CustomerSalesByPartType[2].xls'
The following is a list of repairs:
Damage to the file was so extensive that repairs were not possible. Excel
attempted to recover your formulas and values, but some data may have been
lost or corrupted.
Can anyone help with this?DJJIII,
I wish I were writing with a solution (as I also am getting the annonying
"damage too extensive" message in excel.) I have an MSDN subscription, so we
should get a MS reply. I will repost under the subject: "Damage to file
message when exporting to excel" for good measure.
The interesting thing in my case is that I have a fairly complex report with
many charts and tables. My "top level" report throws this excel error.
However, if I add parameters ("drill down report") the structure of the
report is exactly the same with charts and tables, just less (or different)
data and then it renders fine. I am not using groups at all, so I don't
think the issue is directly related to your use of groups.
My guess is there is some data-related issue as our same report (both
top-level and drilldown) with different data works fine.
Our report actually is a collection of 10 or so subreports, so I will be
doing a little debugging by removing subreports to see if I can identify the
subreport or data that is causing the issue.
-David
"DJJIII" wrote:
> Trying to render a report that has three groups two expand and collapse and
> have charts. The first attempts to render were successful but all later ones
> fail. And excel produces the following message:
> Microsoft Office Excel File Repair Log
> Errors were detected in file 'C:\Documents and Settings\johnsond\Local
> Settings\Temporary Internet
> Files\Content.IE5\UT38LWFQ\CustomerSalesByPartType[2].xls'
> The following is a list of repairs:
> Damage to the file was so extensive that repairs were not possible. Excel
> attempted to recover your formulas and values, but some data may have been
> lost or corrupted.
> Can anyone help with this?
>
have charts. The first attempts to render were successful but all later ones
fail. And excel produces the following message:
Microsoft Office Excel File Repair Log
Errors were detected in file 'C:\Documents and Settings\johnsond\Local
Settings\Temporary Internet
Files\Content.IE5\UT38LWFQ\CustomerSalesByPartType[2].xls'
The following is a list of repairs:
Damage to the file was so extensive that repairs were not possible. Excel
attempted to recover your formulas and values, but some data may have been
lost or corrupted.
Can anyone help with this?DJJIII,
I wish I were writing with a solution (as I also am getting the annonying
"damage too extensive" message in excel.) I have an MSDN subscription, so we
should get a MS reply. I will repost under the subject: "Damage to file
message when exporting to excel" for good measure.
The interesting thing in my case is that I have a fairly complex report with
many charts and tables. My "top level" report throws this excel error.
However, if I add parameters ("drill down report") the structure of the
report is exactly the same with charts and tables, just less (or different)
data and then it renders fine. I am not using groups at all, so I don't
think the issue is directly related to your use of groups.
My guess is there is some data-related issue as our same report (both
top-level and drilldown) with different data works fine.
Our report actually is a collection of 10 or so subreports, so I will be
doing a little debugging by removing subreports to see if I can identify the
subreport or data that is causing the issue.
-David
"DJJIII" wrote:
> Trying to render a report that has three groups two expand and collapse and
> have charts. The first attempts to render were successful but all later ones
> fail. And excel produces the following message:
> Microsoft Office Excel File Repair Log
> Errors were detected in file 'C:\Documents and Settings\johnsond\Local
> Settings\Temporary Internet
> Files\Content.IE5\UT38LWFQ\CustomerSalesByPartType[2].xls'
> The following is a list of repairs:
> Damage to the file was so extensive that repairs were not possible. Excel
> attempted to recover your formulas and values, but some data may have been
> lost or corrupted.
> Can anyone help with this?
>
Monday, March 26, 2012
Excel Destination: write over existing data
I have a daily package that extracts some data and writes it into an excel file. I want to write over the existing data, but the excel destination only appends the next free location in the worksheet. I tried using a SQL task to grab the file, set all the cells = NULL and then run the rest of the package, thinking it would see the null cells as empty and write in them, but somehow it knows where the previous data ended and keeps appending further down in the workbook.
Does anyone know of a workaround so I do not have to delete and re-create the file everytime?
TIA,
Sabrina
You could use the FileSystem task to delete the file prior to inserting into it. This will (I assume) create a new version of the file.
-Jamie
|||You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....
|||Thanks. I've tried deleting the file with a file system task, and that does fail because the destination no longer exists when it runs. The file system task does not have a create file option in it. I was thinking I would have to use a script task to do this?
Rafael, I did not see a link the example you mentioned.
Thanks,
Sabrina
|||Rafael Salas wrote:
You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....
Will setting "DelayValidation=True" work in this case?|||
Thanks. I've tried deleting the file with a file system task, and that does fail because the destination no longer exists when it runs. The file system task does not have a create file option in it. I was thinking I would have to use a script task to do this?
Rafael, I did not see a link the example you mentioned.
Thanks,
Sabrina
Sorry about the omission, Here is the link:
http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html
After deliting the file; just use the execute sql task to create it back as explained in the link...
|||
Rafael Salas wrote:
You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....
Will setting "DelayValidation=True" work in this case?
It should do. Perhaps the original poster could try it. Its a property of the data-flow by the way.
-Jamie
|||
Phil Brammer wrote:
Rafael Salas wrote:
You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....
Will setting "DelayValidation=True" work in this case?
It should do. Perhaps the original poster could try it. Its a property of the data-flow by the way.
-Jamie
If I recall correctly, the DelayValidation=true would woulddo just that; delay the validation; but it won't make the connection manager to create the file if it does not exists...but I am with Jamie...test it and see it.
|||
Jamie Thomson wrote:
Phil Brammer wrote:
Rafael Salas wrote:
You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....
Will setting "DelayValidation=True" work in this case?
It should do. Perhaps the original poster could try it. Its a property of the data-flow by the way.
-Jamie
If I recall correctly, the DelayValidation=true would woulddo just that; delay the validation; but it won't make the connection manager to create the file if it does not exists...but I am with Jamie...test it and see it.
Well, I tried DelayValidation=True and it behaved as expected - still failed. I was able to use the SQL task to create the excel file, thank you Rafael! It is just strange to me that SSIS does not have an option somewhere to overwrite vs append the data.
Thanks!
|||Its not really anything SSIS can do something about - its a lack of functionality in the Excel Driver. Hopefully there'll be a better one coming one day.
-Jamie
|||Ok, same project, now with a twist.
I'm using the file system task to delete the file, and the Execute SQL task to re-create the file. The only problem is we want to add more worksheets. I've tried the following with no results:
CREATE TABLE `Perfect Order` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`PCTPERFECT` DOUBLE PRECISION
);
CREATE TABLE `Days to Activate` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);
CREATE TABLE `Days Activate to Ship` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);
Is there a way to do it all in one statement? Or do I need to have multiple Execute SQL tasks with the same Excel connection?
|||Ok, same project, now with a twist.
I'm using the file system task to delete the file, and the Execute SQL task to re-create the file. The only problem is we want to add more worksheets. I've tried the following with no results:
CREATE TABLE `Perfect Order` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`PCTPERFECT` DOUBLE PRECISION
);
CREATE TABLE `Days to Activate` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);
CREATE TABLE `Days Activate to Ship` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);Is there a way to do it all in one statement? Or do I need to have multiple Execute SQL tasks with the same Excel connection?
I would expected putting all the create statements in a single Execute Sql Task to work. Perhaps having multiple Execute sql tasks is the solution.
sqlExcel Destination: write over existing data
I have a daily package that extracts some data and writes it into an excel file. I want to write over the existing data, but the excel destination only appends the next free location in the worksheet. I tried using a SQL task to grab the file, set all the cells = NULL and then run the rest of the package, thinking it would see the null cells as empty and write in them, but somehow it knows where the previous data ended and keeps appending further down in the workbook.
Does anyone know of a workaround so I do not have to delete and re-create the file everytime?
TIA,
Sabrina
You could use the FileSystem task to delete the file prior to inserting into it. This will (I assume) create a new version of the file.
-Jamie
|||
You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....
|||
Thanks. I've tried deleting the file with a file system task, and that does fail because the destination no longer exists when it runs. The file system task does not have a create file option in it. I was thinking I would have to use a script task to do this?
Rafael, I did not see a link the example you mentioned.
Thanks,
Sabrina
|||Rafael Salas wrote:
You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....
Will setting "DelayValidation=True" work in this case?|||
Thanks. I've tried deleting the file with a file system task, and that does fail because the destination no longer exists when it runs. The file system task does not have a create file option in it. I was thinking I would have to use a script task to do this?
Rafael, I did not see a link the example you mentioned.
Thanks,
Sabrina
Sorry about the omission, Here is the link:
http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html
After deliting the file; just use the execute sql task to create it back as explained in the link...
|||Rafael Salas wrote: You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....
Will setting "DelayValidation=True" work in this case?
It should do. Perhaps the original poster could try it. Its a property of the data-flow by the way.
-Jamie
|||
Phil Brammer wrote: Rafael Salas wrote: You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....
Will setting "DelayValidation=True" work in this case?
It should do. Perhaps the original poster could try it. Its a property of the data-flow by the way.
-Jamie
If I recall correctly, the DelayValidation=true would woulddo just that; delay the validation; but it won't make the connection manager to create the file if it does not exists...but I am with Jamie...test it and see it.
|||Jamie Thomson wrote: Phil Brammer wrote: Rafael Salas wrote: You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....
Will setting "DelayValidation=True" work in this case?
It should do. Perhaps the original poster could try it. Its a property of the data-flow by the way.
-Jamie
If I recall correctly, the DelayValidation=true would woulddo just that; delay the validation; but it won't make the connection manager to create the file if it does not exists...but I am with Jamie...test it and see it.
Well, I tried DelayValidation=True and it behaved as expected - still failed. I was able to use the SQL task to create the excel file, thank you Rafael! It is just strange to me that SSIS does not have an option somewhere to overwrite vs append the data.
Thanks!
|||Its not really anything SSIS can do something about - its a lack of functionality in the Excel Driver. Hopefully there'll be a better one coming one day.
-Jamie
|||
Ok, same project, now with a twist.
I'm using the file system task to delete the file, and the Execute SQL task to re-create the file. The only problem is we want to add more worksheets. I've tried the following with no results:
CREATE TABLE `Perfect Order` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`PCTPERFECT` DOUBLE PRECISION
);
CREATE TABLE `Days to Activate` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);
CREATE TABLE `Days Activate to Ship` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);
Is there a way to do it all in one statement? Or do I need to have multiple Execute SQL tasks with the same Excel connection?
|||Ok, same project, now with a twist.
I'm using the file system task to delete the file, and the Execute SQL task to re-create the file. The only problem is we want to add more worksheets. I've tried the following with no results:
CREATE TABLE `Perfect Order` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`PCTPERFECT` DOUBLE PRECISION
);
CREATE TABLE `Days to Activate` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);
CREATE TABLE `Days Activate to Ship` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);Is there a way to do it all in one statement? Or do I need to have multiple Execute SQL tasks with the same Excel connection?
I would expected putting all the create statements in a single Execute Sql Task to work. Perhaps having multiple Execute sql tasks is the solution.
Excel Destination: write over existing data
I have a daily package that extracts some data and writes it into an excel file. I want to write over the existing data, but the excel destination only appends the next free location in the worksheet. I tried using a SQL task to grab the file, set all the cells = NULL and then run the rest of the package, thinking it would see the null cells as empty and write in them, but somehow it knows where the previous data ended and keeps appending further down in the workbook.
Does anyone know of a workaround so I do not have to delete and re-create the file everytime?
TIA,
Sabrina
You could use the FileSystem task to delete the file prior to inserting into it. This will (I assume) create a new version of the file.
-Jamie
|||
You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....
|||
Thanks. I've tried deleting the file with a file system task, and that does fail because the destination no longer exists when it runs. The file system task does not have a create file option in it. I was thinking I would have to use a script task to do this?
Rafael, I did not see a link the example you mentioned.
Thanks,
Sabrina
|||You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....
Will setting "DelayValidation=True" work in this case?|||
Thanks. I've tried deleting the file with a file system task, and that does fail because the destination no longer exists when it runs. The file system task does not have a create file option in it. I was thinking I would have to use a script task to do this?
Rafael, I did not see a link the example you mentioned.
Thanks,
Sabrina
Sorry about the omission, Here is the link:
http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html
After deliting the file; just use the execute sql task to create it back as explained in the link...
|||Rafael Salas wrote: You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....
Will setting "DelayValidation=True" work in this case?
It should do. Perhaps the original poster could try it. Its a property of the data-flow by the way.
-Jamie
|||
Phil Brammer wrote: Rafael Salas wrote: You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....
Will setting "DelayValidation=True" work in this case?
It should do. Perhaps the original poster could try it. Its a property of the data-flow by the way.
-Jamie
If I recall correctly, the DelayValidation=true would woulddo just that; delay the validation; but it won't make the connection manager to create the file if it does not exists...but I am with Jamie...test it and see it.
|||Jamie Thomson wrote: Phil Brammer wrote: Rafael Salas wrote: You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....
Will setting "DelayValidation=True" work in this case?
It should do. Perhaps the original poster could try it. Its a property of the data-flow by the way.
-Jamie
If I recall correctly, the DelayValidation=true would woulddo just that; delay the validation; but it won't make the connection manager to create the file if it does not exists...but I am with Jamie...test it and see it.
Well, I tried DelayValidation=True and it behaved as expected - still failed. I was able to use the SQL task to create the excel file, thank you Rafael! It is just strange to me that SSIS does not have an option somewhere to overwrite vs append the data.
Thanks!
|||Its not really anything SSIS can do something about - its a lack of functionality in the Excel Driver. Hopefully there'll be a better one coming one day.
-Jamie
|||
Ok, same project, now with a twist.
I'm using the file system task to delete the file, and the Execute SQL task to re-create the file. The only problem is we want to add more worksheets. I've tried the following with no results:
CREATE TABLE `Perfect Order` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`PCTPERFECT` DOUBLE PRECISION
);
CREATE TABLE `Days to Activate` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);
CREATE TABLE `Days Activate to Ship` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);
Is there a way to do it all in one statement? Or do I need to have multiple Execute SQL tasks with the same Excel connection?
|||Ok, same project, now with a twist.
I'm using the file system task to delete the file, and the Execute SQL task to re-create the file. The only problem is we want to add more worksheets. I've tried the following with no results:
CREATE TABLE `Perfect Order` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`PCTPERFECT` DOUBLE PRECISION
);
CREATE TABLE `Days to Activate` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);
CREATE TABLE `Days Activate to Ship` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);Is there a way to do it all in one statement? Or do I need to have multiple Execute SQL tasks with the same Excel connection?
I would expected putting all the create statements in a single Execute Sql Task to work. Perhaps having multiple Execute sql tasks is the solution.
Excel Destination Multiple Worksheets!
Hi ,
I am retrieving some data that contains three or four hundred thousand rows. These rows are supposed to go into an excel file with multiple worksheets, since one Excel worksheet cannot handle rows that are more than 65536. Below is what I need to achieve:
- Dynamically create mutiple worksheets. Re-direct data like this i.e. first 64K in first worksheet and next 64K in next worksheet and so on. Dynamically name the work sheet with the start value in that work sheet e.g. OrderNumber or OrderDate.
Can we achieve this directly/indirectly?
Your help will be appreciated.
Thanks,
Paraclete
Search is your friend.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1490423&SiteID=1
|||Hi,
Thanks for your response. I already gone through this post. It does not explain how can we accomplish this. It just says it can be done in two steps. But the 2nd step which needs the elaboration it does not touch it.
Regards,
Paraclete
|||I'm just adding more information: Microsoft has a KB article on importing text files larger than 65,536 rows into Excel.http://support.microsoft.com/default.aspx?scid=kb;EN-US;q120596|||
Hi All,
I tried to implement as described in the post below.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1490423&SiteID=1
What happened first I wrote the results to a comma separated Flat file Destination. Then created a For Loop Container in which defined a Data Flow Task. It started with a Flat File Source to the access the data that was written to it earlier. Then added a Data Conversion Transformation. And finally the Excel Destination. There is a variable that is a result of an expression. Based on that expression I will create the name of the work sheet. When I try to do that, the following error apprears in the execution results tab
[Excel Destination [9]] Error: An OLE DB error has occurred. Error code: 0x80040E37.
[Excel Destination [9]] Error: Opening a rowset for "MySheet2" failed. Check that the object exists in the database.
This may be of interest to note that MySheet1 is already created before execution. And the variable value changed during execution i.e. 2nd iteration. I also tried to add a table using a Script Task but was not successful either. The question boils down to: Whether or not we can create a table whose name comes from a variable if yes then what is the syntax for that.
Below are the settings in Excel Destination Editor Connection Managers Tab
OLE DB Connection Manager = Excel Connection Manager (path and name of the excel file)
Data Access Mode = Table name or View name variable
Variable Name = DestSheetName (this is of string type constructed from expression)
Below is the table definition of "MySheet1". Can any one tell how to create this table using a variable name dynamically, since the value of 'DestSheetName' will change with every iteration of the Foor Loop Container.
CREATE TABLE `MySheet1` (
`EmpID_ID` INTEGER,
`LocID` INTEGER,
`Dept` NVARCHAR(6),
`Date_Time_Hired` NVARCHAR(20),
`RowGroup` INTEGER
)
Shema will remain same of every Table i.e. MySheet2, MySheet3 etc.
Thanks,
Paraclete
|||You have to explicitly create each worksheet before trying to load it. The way of doing that is using a Execute sql task in the control flow with an create table statement that uses the excel connection manager. I know there are other threads in this forum that explains that. I have a post that shows it; it contains other logic, but hopefully you will get the idea:
http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html
|||
Yes, you are right we need to explicity create these work sheets before we can access them. Yes, we can try by putting this in a Execute SQL Task. But I doubt if the SQL Task can support CREATE TABLE Statement. Also what is the syntax for using a variable in CREATE TABLE Statement
Thanks,
Paraclete
|||Did you check the link I provided? from that link:
Add an Execute SQL Task inside of the ForEach Loop Container and create a precedence constraint (yes, a green arrow!) from the Execute SQL Task to the Dataflow task.
Open the Execute SQL task and change the ConnectionType to ‘Excel’, Choose the Excel Connection Manager in Connection property and write a Create table statement in the SQLStatement as follows:
CREATE TABLE `Excel Destination` (
`HeaderID` INTEGER,
`HeaderName` NVARCHAR(50),
`LineID` INTEGER,
`LineName` NVARCHAR(50),
`LineDetails` NVARCHAR(50)
)
As you can see, the objective of this EST is to create the excel file to make it available to the data flow task on every iteration; and it is here where the power of the expression come handy.
Create a new variable, to hold the file name to be creates. I created a variable called 'ExcelFileName' of String Type (give an intial name in the variable window to avoid validation errors).
Now, use an expression to change the value of the variable at run time; to do so, go to the preperties windows of the Variable and change the porperty EvaluateAsExpression to true and click on the expression property to bring the expression editor. That expression will change the value of the variable as the package iterates through the recordset. I used an expression like:
"C:\\Temp\\Report-"+ (DT_STR, 25,1252)@.[User::HeaderID] +".xls"
In the Excel file connection manager, let's create another expression that uses the 'ExcelFileName' variable to change the ExcelFilePath. To do so, click in the connection manager, go to the properties window and click in the expression property. Once the Property Expression Editor is available; choose ExcelFilePath and bring the expression builder. Write an expression like:
@.[User::ExcelFileName]
|||
Yes, you are right we need to explicity create these work sheets before we can access them. Yes, we can try by putting this in a Execute SQL Task. But I doubt if the SQL Task can support CREATE TABLE Statement. Also what is the syntax for using a variable in CREATE TABLE Statement
Thanks,
Paraclete
Sure it can. You would create the statement as an expression-based variable and set the SQLSourceType of the Execute SQL Task to Variable.
|||
Same approach, different words:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=949540&SiteID=1
Excel Destination errors for fields > 255 chars
Hi:
Am trying to write sql2005 data to Excel. I have problems when data length exceeds 255 chars. I used a sample destination file with cells containing >255 chars where required, so that the Excel Destination external column was recognized as DT_Text.
My oledb source columns(external and output) are both varchar (1000).
Works fine , but fails when values>255 chars.
Error 0xC0202009 on ProcessInput.
I use SQL2005 enterprise SP2.
I tried changing the Oledb source output to Textstream, but that does not work at all.
How do I export values >255 chars to excel?
TIA
Kar
Hello,
could you try using Import/Export wizard and saving the package it produces? It will likely use DT_STR on the source, DT_TEXT on the destination and insert a data conversion (DT_STR->DT_TEXT).
HTH.|||Hi:
Using Varchar(Max) in the SQL table works both ways(excel source and excel destination) without any need to convert. I used the Import wizard as suggested, and alllowed it to create a new SQL table. Thanks for the tip.
Kar
|||Hmmm, I rejoiced too soon.
Using varchaar(max) in SQL Destination works in the normal case, when the column contains >255 chars in the reference rows (as set by the GuessTypeRows registry value).
However, if the column contains <255 rows, then I get error 0xC0208265 - Failed to retrieve long data for Column.
My problem is that I have multiple xls files from which I import data to SQL. Now most files would have a given column with data >255 chars, but maybe one of the files has that column with all values <255 chars.
I thought I would just convert everything to text, and then use a Text File Source. Problem is that "Excel Automation - Save as text" gives me a series of hashes for cells with >255 chars.
So now I am stuck, and the only inelegant solution is to add a dummy first row to each excel and set guessTypeRows to 1 and force excel to get at least one cell>255 chars for all files..
UGH.
How do I make sure the package works for all cases? Somebody please help me!
TIA
Kar
Well, you can either go with your solution or try to build two packages if you know which Excel files will have <255 char columns.
Thanks.
Excel Destination errors for fields > 255 chars
Hi:
Am trying to write sql2005 data to Excel. I have problems when data length exceeds 255 chars. I used a sample destination file with cells containing >255 chars where required, so that the Excel Destination external column was recognized as DT_Text.
My oledb source columns(external and output) are both varchar (1000).
Works fine , but fails when values>255 chars.
Error 0xC0202009 on ProcessInput.
I use SQL2005 enterprise SP2.
I tried changing the Oledb source output to Textstream, but that does not work at all.
How do I export values >255 chars to excel?
TIA
Kar
Hello,
could you try using Import/Export wizard and saving the package it produces? It will likely use DT_STR on the source, DT_TEXT on the destination and insert a data conversion (DT_STR->DT_TEXT).
HTH.|||Hi:
Using Varchar(Max) in the SQL table works both ways(excel source and excel destination) without any need to convert. I used the Import wizard as suggested, and alllowed it to create a new SQL table. Thanks for the tip.
Kar
|||Hmmm, I rejoiced too soon.
Using varchaar(max) in SQL Destination works in the normal case, when the column contains >255 chars in the reference rows (as set by the GuessTypeRows registry value).
However, if the column contains <255 rows, then I get error 0xC0208265 - Failed to retrieve long data for Column.
My problem is that I have multiple xls files from which I import data to SQL. Now most files would have a given column with data >255 chars, but maybe one of the files has that column with all values <255 chars.
I thought I would just convert everything to text, and then use a Text File Source. Problem is that "Excel Automation - Save as text" gives me a series of hashes for cells with >255 chars.
So now I am stuck, and the only inelegant solution is to add a dummy first row to each excel and set guessTypeRows to 1 and force excel to get at least one cell>255 chars for all files..
UGH.
How do I make sure the package works for all cases? Somebody please help me!
TIA
Kar
Well, you can either go with your solution or try to build two packages if you know which Excel files will have <255 char columns.
Thanks.
Excel Destination appends the excel file everytime a package is executed
i have an SSIS package that exports to an excel file. This works fine. the problem is that it appends the data instead of overwriting the file. Is there any way to overwrite the file like you can with a flat file? I have to email the file everyweek and don't want to have to clear it out manually. Any help would be appreciated
Perhaps you can use an Execute SQL statement with a delete statement before the data flow task. That way you delete existing rows before loading the new one.
Update: I just tried that and did not work:
[Execute SQL Task] Error: Executing the query "delete FROM [Sheet1$]" failed with the following error: "Deleting data in a linked table is not supported by this ISAM.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
So, another option may be to have an empty copy of the file that you copy/rename evry time the package run. You can use File system task in control flow for that.
|||Pamela,
You could also use a Execute SQL task to create a new excel file on every execution of the package. I have posted an step by step guide on that:
http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html
I hope you find it helpful
Excel Destination
Does an empty file (text/ Excel) needs to be created manually for the connection manager for (text/excel)destinations. Can I create the file at run time?A file will be created for you.sql
Subscribe to:
Posts (Atom)