Exporting data with dynamic fields

Hi,

I have one Query Builder application where user creates queries and generates output with required fields.Then user can export the same to excel (Using farPointSpread properties)
But if no. of records goes beyond 10K it goes too slow or even user machine hangs up. I want to give proper export facility. Can it be given using DTS?

I tried DTS (.vbs) file. but for random field selection it did not work.DTS package asks for new Transactions to be built for every change in Source table structure. It first creates the table in the destination databse and then exports the data.
If I try modify the VB code generated by the package to match with new Source table it does'nt work.

Can anyone tell me what should I do for export the data dynamically i.e. export the tables with differrent structres?

Thanx in advance.|||You should do a helluvalotta programming. DTS sucks, and I don't believe most people on this forum use it for much more than directly transfering defined recordsets.

Export window does not close

Report Server 2005; .NET framework 2.0; IE 6.0

I view a report in Report Manager, then I click the drop-down box and select an output type, select Save, and a new blank web page opens, then the small export window opens asking for the filename. The file is created, then I close the export window, but the blank web page window does not close. I can see that the web page showing my original view of the report is still open, but I have to close the blank web page myself. While not really an error, this is an annoyance for users. Is there a setting that will help prevent this problem? Is this a bug?

Thanks for any help.

Getting same problem as mentioned by DawnJ..

i want to rectify this problem as soon as possible..Reply back friends if u have relevant information for this thread...

Thanks in Advance

Bhoopathi...

Export window does not close

Report Server 2005; .NET framework 2.0; IE 6.0

I view a report in Report Manager, then I click the drop-down box and select an output type, select Save, and a new blank web page opens, then the small export window opens asking for the filename. The file is created, then I close the export window, but the blank web page window does not close. I can see that the web page showing my original view of the report is still open, but I have to close the blank web page myself. While not really an error, this is an annoyance for users. Is there a setting that will help prevent this problem? Is this a bug?

Thanks for any help.

Getting same problem as mentioned by DawnJ..

i want to rectify this problem as soon as possible..Reply back friends if u have relevant information for this thread...

Thanks in Advance

Bhoopathi...

Export to XML from Report - is this possible

Hello All,
I've got some data which is grouped using a table. The output of the report
looks Ok, however the output when exported to XML from the viewer needs to be
formatted specifically. The current output seems to add extra tags in which
is not required. The format of the XML should be smothing like this. Is it
possible to get something like this out of the box in SQL RS.
<Regions>
<RegionDetails>
<Region>Agricultural Region</Region>
<CandidateGroups>
<CandidateGroupTicketDetails>
<Group>Australian Labor Party</Group>
<Ticket>VOTING TICKET FOR: Australian Labor Party</Ticket>
<CandidateDetails>
<BallotPaperName>Ken Dones</BallotPaperName>
</CandidateDetails>
<CandidateDetails>
<BallotPaperName>Ken Dids</BallotPaperName>
</CandidateDetails>
</CandidateGroups>
</RegionDetails>
</Regions>
An example of extra tags is shown here:
<Regions> <-- This is extra an not required
<Regions>
<RegionDetails>
<Region>Agricultural</Region>
<CandidateGroups>
<CandidateGroupTicketDetails>
<Group>Liberal</Group>
<Ticket>VOTING TICKET FOR: Liberal</Ticket>
<CandidateDetails_Collection> <-- This is extra and not
required
<CandidateDetails>
<BallotPaperName>Ken Dones</BallotPaperName>
</CandidateDetails>
<CandidateDetails>
<BallotPaperName>ken Didd</BallotPaperName>
</CandidateDetails>Nick,
You can specify an XSLT to accompany your report and get the resultant
XML formatted anyway you like.
Andy Potter|||Currently just using standard settings in the report to get the tags I want
I haven't used XSLT - are there examples around for aplying this report
server output.
The users just use Save As to export as XML, how does XSLT get applied then.
Is there a setting in the reprt itself
"Potter" wrote:
> Nick,
> You can specify an XSLT to accompany your report and get the resultant
> XML formatted anyway you like.
> Andy Potter
>

Export to Tab Delimited TXT - SQL Reporting Services

I am new to Reporting tools. How can I export the output to Tab Delimited TXT. I found some solutions, however, I could not understand how to proceed with them... anybody can explain the solution step by step...

Hi Pradeep,

Try some of these links

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=924133&SiteID=1

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74882

|||

Thanks Sudhir for the reply, however, I need to export the SQL Reporting Services REPORT output to TAB Delimited TXT file.

|||

Hi,

From your description, it seems that you want to change the CSV extension to a Tab delimited output in your reporting service, right?

If so, you should add a new extension line to the RSReportServer.config file to enable the Tab delimited output.

In the config file, you should change the following settings:

1. For tab-delimited report, use
<FieldDelimiter> </FieldDelimiter>

2. For name override, you must specify language attribute:
<Name Language="en-US">TXT (Tab Delimited Text File)</Name>

For more information, see:http://msdn2.microsoft.com/en-us/library/ms156281.aspx

Thanks.

Export To MS Word

Hi folks,
Is there a way to export the report output to MS Word from reporting
services directly ?According to the docs, the only export formats are:
TIFF
Acrobat (PDF) File
Web archive
Excel
XML
CSV
HTML with Office Web Components
--
Adrian M.
MCP
"Rajan" <Rajan@.discussions.microsoft.com> wrote in message
news:B9827719-B3D4-4B82-AC97-CBD1886C8184@.microsoft.com...
> Hi folks,
> Is there a way to export the report output to MS Word from reporting
> services directly ?|||On Mon, 31 Jan 2005 02:57:02 -0800, "Rajan"
<Rajan@.discussions.microsoft.com> wrote:
>Hi folks,
> Is there a way to export the report output to MS Word from reporting
>services directly ?
No that I know of. There is a product by SoftArtisans, called
OfficeWriter (WordWriter/ExcelWriter), that provides advanced
exporters to Word and Excel, but it is not a straightforward solution:
you CANNOT use it with your existing reports, and must use their
designers to take advantage of these exporters. The opposite is a
problem as well: if you designed a report in their designer, you won't
be able to use any standard exporters with it, unless you maintain 2
separate designs in the same RDL file. Creates a maintenace hell, if
you have a lot of reports. :(~|||thanks for the update.
"Usenet User" wrote:
> On Mon, 31 Jan 2005 02:57:02 -0800, "Rajan"
> <Rajan@.discussions.microsoft.com> wrote:
> >Hi folks,
> >
> > Is there a way to export the report output to MS Word from reporting
> >services directly ?
> No that I know of. There is a product by SoftArtisans, called
> OfficeWriter (WordWriter/ExcelWriter), that provides advanced
> exporters to Word and Excel, but it is not a straightforward solution:
> you CANNOT use it with your existing reports, and must use their
> designers to take advantage of these exporters. The opposite is a
> problem as well: if you designed a report in their designer, you won't
> be able to use any standard exporters with it, unless you maintain 2
> separate designs in the same RDL file. Creates a maintenace hell, if
> you have a lot of reports. :(~
>

Export To Excel Error

When exporting to pdf format, everything is fine.
However, exporting to excel leads to some garbage.
Can anyone tell me what is going on?
Output similar to the following is displayed in the excel file
MIME-Version: 1.0
X-Document-Type: Workbook
Content-Type: multipart/related;boundary="--=_NextPart_01C35DB7.4B204430"
This is a multi-part message in MIME format.
--=_NextPart_01C35DB7.4B204430
Content-Type: text/html;
charset="utf-8"
Content-Transfer-Encoding: base64
Content-Location: file:///c:/Report.htm
77u/PGh0bWwgeG1sbnM6dj0idXJuOnNjaGVtYXMtbWljcm
--=_NextPart_01C35DB7.4B204430
Content-Type: text/html
Content-Transfer-Encoding: base64
Content-Location: file:///c:/Sheet1.htm
77u/PGh0bWwgeG1sbnM6dj0idXJuOnNjaGVtYXMtbW
9sc3Bhbj0iMyIgc3R5bGU9ImJvcmRlci1yaWdodDow
--=_NextPart_01C35DB7.4B204430
Content-Type: image/bmp
Content-Transfer-Encoding: base64
Content-Location: file:///c:/4.bmpAre you using MRS with service pack #1? If not, and you are exporting to an
older version of Excel you can see garbage. If you are on SP1, then I'm not
sure what could be going on.
"Sanjeev" wrote:
> When exporting to pdf format, everything is fine.
> However, exporting to excel leads to some garbage.
> Can anyone tell me what is going on?
> Output similar to the following is displayed in the excel file
> MIME-Version: 1.0
> X-Document-Type: Workbook
> Content-Type: multipart/related;boundary="--=_NextPart_01C35DB7.4B204430"
> This is a multi-part message in MIME format.
> --=_NextPart_01C35DB7.4B204430
> Content-Type: text/html;
> charset="utf-8"
> Content-Transfer-Encoding: base64
> Content-Location: file:///c:/Report.htm
> 77u/PGh0bWwgeG1sbnM6dj0idXJuOnNjaGVtYXMtbWljcm
> --=_NextPart_01C35DB7.4B204430
> Content-Type: text/html
> Content-Transfer-Encoding: base64
> Content-Location: file:///c:/Sheet1.htm
> 77u/PGh0bWwgeG1sbnM6dj0idXJuOnNjaGVtYXMtbW
> 9sc3Bhbj0iMyIgc3R5bGU9ImJvcmRlci1yaWdodDow
> --=_NextPart_01C35DB7.4B204430
> Content-Type: image/bmp
> Content-Transfer-Encoding: base64
> Content-Location: file:///c:/4.bmp
>
>|||I have the same problem.. And I talked with Microsoft Support Team..
Their answer is in the link below:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsmain/htm/rsc_architecture_v1_01df.asp
As a summary they say, use Excel 2002 or higher version !!!
I'm really disappointed with this..
Nothing to say more..
"Sanjeev" <sanjeev@.microsoft.com> wrote in message news:<eRin5rpuEHA.2016@.TK2MSFTNGP15.phx.gbl>...
> When exporting to pdf format, everything is fine.
> However, exporting to excel leads to some garbage.
> Can anyone tell me what is going on?
> Output similar to the following is displayed in the excel file
> MIME-Version: 1.0
> X-Document-Type: Workbook
> Content-Type: multipart/related;boundary="--=_NextPart_01C35DB7.4B204430"
> This is a multi-part message in MIME format.
> --=_NextPart_01C35DB7.4B204430
> Content-Type: text/html;
> charset="utf-8"
> Content-Transfer-Encoding: base64
> Content-Location: file:///c:/Report.htm
> 77u/PGh0bWwgeG1sbnM6dj0idXJuOnNjaGVtYXMtbWljcm
> --=_NextPart_01C35DB7.4B204430
> Content-Type: text/html
> Content-Transfer-Encoding: base64
> Content-Location: file:///c:/Sheet1.htm
> 77u/PGh0bWwgeG1sbnM6dj0idXJuOnNjaGVtYXMtbW
> 9sc3Bhbj0iMyIgc3R5bGU9ImJvcmRlci1yaWdodDow
> --=_NextPart_01C35DB7.4B204430
> Content-Type: image/bmp
> Content-Transfer-Encoding: base64
> Content-Location: file:///c:/4.bmp|||I am on SP1. Perhaps some setting is offset.
Thanks, Anyway.
"Shane Castle" <ShaneCastle@.discussions.microsoft.com> wrote in message
news:21C5DEAA-6CE5-41AF-865F-9FCD63B180C4@.microsoft.com...
> Are you using MRS with service pack #1? If not, and you are exporting to
> an
> older version of Excel you can see garbage. If you are on SP1, then I'm
> not
> sure what could be going on.
> "Sanjeev" wrote:
>> When exporting to pdf format, everything is fine.
>> However, exporting to excel leads to some garbage.
>> Can anyone tell me what is going on?
>> Output similar to the following is displayed in the excel file
>> MIME-Version: 1.0
>> X-Document-Type: Workbook
>> Content-Type:
>> multipart/related;boundary="--=_NextPart_01C35DB7.4B204430"
>> This is a multi-part message in MIME format.
>> --=_NextPart_01C35DB7.4B204430
>> Content-Type: text/html;
>> charset="utf-8"
>> Content-Transfer-Encoding: base64
>> Content-Location: file:///c:/Report.htm
>> 77u/PGh0bWwgeG1sbnM6dj0idXJuOnNjaGVtYXMtbWljcm
>> --=_NextPart_01C35DB7.4B204430
>> Content-Type: text/html
>> Content-Transfer-Encoding: base64
>> Content-Location: file:///c:/Sheet1.htm
>> 77u/PGh0bWwgeG1sbnM6dj0idXJuOnNjaGVtYXMtbW
>> 9sc3Bhbj0iMyIgc3R5bGU9ImJvcmRlci1yaWdodDow
>> --=_NextPart_01C35DB7.4B204430
>> Content-Type: image/bmp
>> Content-Transfer-Encoding: base64
>> Content-Location: file:///c:/4.bmp
>>|||Thanks.
"Sema Yuce" <sema.yuce@.eczacibasi.com.tr> wrote in message
news:9844673f.0410252312.e19650a@.posting.google.com...
>I have the same problem.. And I talked with Microsoft Support Team..
> Their answer is in the link below:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsmain/htm/rsc_architecture_v1_01df.asp
> As a summary they say, use Excel 2002 or higher version !!!
> I'm really disappointed with this..
> Nothing to say more..
> "Sanjeev" <sanjeev@.microsoft.com> wrote in message
> news:<eRin5rpuEHA.2016@.TK2MSFTNGP15.phx.gbl>...
>> When exporting to pdf format, everything is fine.
>> However, exporting to excel leads to some garbage.
>> Can anyone tell me what is going on?
>> Output similar to the following is displayed in the excel file
>> MIME-Version: 1.0
>> X-Document-Type: Workbook
>> Content-Type:
>> multipart/related;boundary="--=_NextPart_01C35DB7.4B204430"
>> This is a multi-part message in MIME format.
>> --=_NextPart_01C35DB7.4B204430
>> Content-Type: text/html;
>> charset="utf-8"
>> Content-Transfer-Encoding: base64
>> Content-Location: file:///c:/Report.htm
>> 77u/PGh0bWwgeG1sbnM6dj0idXJuOnNjaGVtYXMtbWljcm
>> --=_NextPart_01C35DB7.4B204430
>> Content-Type: text/html
>> Content-Transfer-Encoding: base64
>> Content-Location: file:///c:/Sheet1.htm
>> 77u/PGh0bWwgeG1sbnM6dj0idXJuOnNjaGVtYXMtbW
>> 9sc3Bhbj0iMyIgc3R5bGU9ImJvcmRlci1yaWdodDow
>> --=_NextPart_01C35DB7.4B204430
>> Content-Type: image/bmp
>> Content-Transfer-Encoding: base64
>> Content-Location: file:///c:/4.bmp

export to excel - formatting number output

Hi all,

Once again, SSIS is giving me a 'F.U.N.' time (ask for definition of the F.U.N. acronym another time ).

I

have a relatively simple task - create an excel spreadsheet with 3

columns of data - Id, Description and Sales. ID and Description are

text, sales is int.

So my SP aggregates and creates my resultset

in my OLE DB Source in the Data Flow. It proceeds to the Excel

destination, and that all seems fine. My issue is that the data is

being written as text. Looking at the excel destination in Advanced

editor:
the Excel Destination Input, Input columns are formatted as

I expected: DT_WSTR 8 for the ID, DT_WSTR 100 for the Description and

DT_I4 for the Sales.
Excel Destination Input, External columns refuse to fall in line, though. They are all listed as DT_WSTR 255.

The

target excel spreadsheet is being created from a template file. That

template file has header columns. The target column for the Sales has

the entire column formatted to NUMBER (0 decimals). Yet to now avail.

When

I check the spreadsheet, the column has retained the cell formatting,

and I have a 'I' pop-up to inform me that 'someone' has inserted text

data into the number column (even though the data IS number).

Since

the SP spits out INT, it isn't a case of receiving a text value, imho.

While trying to change the external column data type in the advanced

editor, SSIS is quite happy to let me change the value for the Sales

output to DT_I4, apply, and ok. Then, when I open it immedaitely

aftgerwards, it has reverted to the DT_WSTR's! AArrgh. If is can't

handle it, at least tell me when I try and change it. don't let me

change it, and then revert back without telling me! Grumble grumble...

So - anyone know a way around this?

There are no perfect solutions for many behaviors of Jet with the Excel driver. However the driver is always happiest when the destination "table" has been created by the driver itself, which could be done through the Excel Destination or the Import and Export Wizard or ADO code. Excel column formatting seems to be disregarded completely.

The most comprehensive list of Excel driver-related issues is in 257819 How To Use ADO with Excel Data from Visual Basic or VBA
http://support.microsoft.com/default.aspx?scid=kb;EN-US;257819. Many of these will surface in SSIS in one form or another.

-Doug

|||

Hi Douglas,

As I read

the links I’ve found so far, IMEX=1 specifies text? Is there a way to have the

IMEX setting per column? If so, what would the correct IMEX setting for numeric

be?

TIA

|||

IMEX makes all data one type. By default that's Text (based on a Registry entry), and I've never heard of anyone changing it.

Please trust me on this -- if you want more predictable behavior, create the destination table in Excel by using the Excel driver. There appear to be hidden Excel column or cell settings that the driver is aware of but that are not exposed in the Excel UI (and not through cell formatting options).

-Doug

|||Thanks Douglas. I'll try that approach. It would seem to imply something like this:

CREATE TABLE `Sales Rank` (

`CatalogueItemReference` NVARCHAR(8),

`DescriptionText` NVARCHAR(100),

`TotalSalesForPeriod` INTEGER

)

that seem about right?|||

I'm not sure whether Jet SQL recognizes NVARCHAR but always have trouble finding the list. Somewhere on your disk there's a JETSQL40.CHM or SQLJET40.CHM or similar. I'd be more inclined to let the Import and Export Wizard or the Excel Destination write the statement for me.

-Doug

|||Thanks Doug, the sample I wrote was a slightly modified version of the

table create that the ssis excel destination task generated. I'll post

if it works :-)

Export Stored procedure to Access Error

When trying to export the output of a stored procedure to an Access database
,
I get the error
"Syntax Error in field definition"
at the first step of the process (output table creation)
The procedure runs fine in QA, and has the following general structure:
...
select * into #MyTempTable
from ...
...
select * ] this is what I need
from ... ] to be the output
join #MyTempTable on ... ] of the procedure
... ]
Could the use of a temporary table be the cause of the problem?Try using SET NOCOUNT ON at the beginning of the procedure.
ML
http://milambda.blogspot.com/|||I did and still get the same error
"ML" wrote:

> Try using SET NOCOUNT ON at the beginning of the procedure.
>

export report server contents/items to xml file

how can I output the contents or my report server to an xml file? I
assume I would use some variation of ListChildren and the WriteXml
method.I was able to accomplish this using the following code:
Dim impersonationContext As
System.Security.Principal.WindowsImpersonationContext
Dim currentWindowsIdentity As
System.Security.Principal.WindowsIdentity
currentWindowsIdentity = CType(User.Identity,
System.Security.Principal.WindowsIdentity)
impersonationContext = currentWindowsIdentity.Impersonate()
Dim rs As New ReportingService()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim items As CatalogItem() = Nothing
' Retrieve a list of all items from the report server database.
Try
items = rs.ListChildren("/", True)
Catch ex As Exception
response.write(ex.Message)
End Try
' Serialize the contents as an XML document and write the
contents to a file.
Try
Dim fs As New FileStream("C:\CatalogItems.xml",
FileMode.Create)
Dim writer As New XmlTextWriter(fs, Encoding.Unicode)
Dim serializer As New XmlSerializer(GetType(CatalogItem()))
serializer.Serialize(writer, items)
response.write("Contents successfully written to a file.")
Catch ex As Exception
response.write(ex.Message)
End Try
Stephen wrote:
> how can I output the contents or my report server to an xml file? I
> assume I would use some variation of ListChildren and the WriteXml
> method.