Showing posts with label create. Show all posts
Showing posts with label create. Show all posts

Thursday, March 29, 2012

Exporting report to text files

Hi guys,

I have this problem with srs... That is when i launched a report it should automatically create a text file on my local machine... is this possible?

Thanks

Not sure what you mean here..

Have you looked at subscriptions with delivery to the file system?

|||

what i mean is that whenever i open a report it should auomatically export the the report into .txt file format without going through the export button.

Does SRS has that kind of feature or i need to create a separate assembly for it?

thanks

|||

I think you would have to do some custom programming. Have you looked at the ssrs web service methods available?

sql

Tuesday, March 27, 2012

Exporting multiple tables as flat files

Hi.

I've tried to create a SSIS package to simply export a bunch of tables as flat files, and am having troubles because when the for each loop hits the second table the column mappings in the flat file destination are not synchronised with its schema.

I created a for each loop with an enumerator that returns the table names and sets a user variable.

I created a data flow task which dynamically connects to the table name variable.

In the Flat File Destination there is a column mapping property, but I don't know how to reset these mappings on each iteration.

Any ideas?

Are all the tables you are trying to export have exactly same column metadata? If not, you can't do this using a for loop. This is because SSIS cannot dynamically update the column mapping/schema. If the tables have different metadata, you need to create separate data flow tasks for them.|||Thanks.

Sunday, March 25, 2012

exporting desktop MSDE to remote server Sql

What is the best tool for doing this? I have been using MSDE to create a
portal app on my desktop and now want to move the data from the MSDE db
locally to the remote server db. I can export the MSDE data but need a way
to get it to the remote sever and import it. Enterprise Manager seems to
be the tool of choice for this but I don't have it so I need another way.
Thanks!
Try WinSQL at http://www.synametrics.com/winsql. I have found to be the
best solution if Enterprise Manager is not available. It can move the
data as well as serve as a query tool.
Regards,
PM
Bob K. wrote:

> What is the best tool for doing this? I have been using MSDE to create a
> portal app on my desktop and now want to move the data from the MSDE db
> locally to the remote server db. I can export the MSDE data but need a way
> to get it to the remote sever and import it. Enterprise Manager seems to
> be the tool of choice for this but I don't have it so I need another way.
> Thanks!
>
|||hi Bob,
"Bob K." <bob1030@.hotmail.com> ha scritto nel messaggio
news:A2YLc.6720$eM2.877@.attbi_s51...
> What is the best tool for doing this? I have been using MSDE to create a
> portal app on my desktop and now want to move the data from the MSDE db
> locally to the remote server db. I can export the MSDE data but need a
way
> to get it to the remote sever and import it. Enterprise Manager seems
to
> be the tool of choice for this but I don't have it so I need another way.
you can have a look at Web Data Administrator,
http://www.microsoft.com/downloads/d...displaylang=en
which implements a Web interface where you can execute Transact-SQL scripts
and/or (partially) manage your databases...
other tools, both free and commercial can be found at
http://www.microsoft.com/sql/msde/partners/default.asp and
http://www.aspfaq.com/show.asp?id=2442 ..
you can have a look at a free prj of mine too, available at the link
following my sign..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Thursday, March 22, 2012

Exporting Binary Data from MSSQL to MySQL

I have a MSSQL table with Primary Keys, Company_Id, set as a binary
value. I used the dts Transform Data Task and to create a new table in
MySQL.
The default create code comes up with:
CREATE TABLE `New Table` (
`Web_Edited` tinyint unsigned NULL,
`Company_Id` date NOT NULL,
`Rn_Descriptor` varchar (80) NULL, ...
Why is the Company_Id set to date and not varbinary?
Secondly, if I change the create statement
...`Company_Id` date NOT NULL, ...
to
... `Company_Id` varbinary(8) NOT NULL, ...
then on the Transform Data Task's Destination Tab, the Company_Id is
displayed as longtext. And then I specify that the Company Id column
is the only column I wanted in the transformation, and executed the
trasformation.
I get an error stating this:
The number of failing rows exceeds the maximum specified.
TransformCopy 'DTSTransformation__1' conversion error: General
conversion failure on column pair 1(source column 'Company_Id'
(DBTYPE_BYTES), destination column 'Company_Id' (DBTYPE_STR))
But I already specified the Company_Id column as a binary, and I even
checked the mysql table's strucutre and it was also set to varbinary(8)
Please help.Hi
I don't know much about MYSQL, but this may be a driver issue, are you using
ODBC? If mysql can import text files you may want to try using BCP and then
importing from MYSQL.
John
"datenisis@.gmail.com" wrote:

> I have a MSSQL table with Primary Keys, Company_Id, set as a binary
> value. I used the dts Transform Data Task and to create a new table in
> MySQL.
> The default create code comes up with:
> CREATE TABLE `New Table` (
> `Web_Edited` tinyint unsigned NULL,
> `Company_Id` date NOT NULL,
> `Rn_Descriptor` varchar (80) NULL, ...
> Why is the Company_Id set to date and not varbinary?
> Secondly, if I change the create statement
> ...`Company_Id` date NOT NULL, ...
> to
> ... `Company_Id` varbinary(8) NOT NULL, ...
> then on the Transform Data Task's Destination Tab, the Company_Id is
> displayed as longtext. And then I specify that the Company Id column
> is the only column I wanted in the transformation, and executed the
> trasformation.
> I get an error stating this:
> The number of failing rows exceeds the maximum specified.
> TransformCopy 'DTSTransformation__1' conversion error: General
> conversion failure on column pair 1(source column 'Company_Id'
> (DBTYPE_BYTES), destination column 'Company_Id' (DBTYPE_STR))
> But I already specified the Company_Id column as a binary, and I even
> checked the mysql table's strucutre and it was also set to varbinary(8)
> Please help.
>sql

Exporting Binary Data from MSSQL to MySQL

I have a MSSQL table with Primary Keys, Company_Id, set as a binary
value. I used the dts Transform Data Task and to create a new table in
MySQL.
The default create code comes up with:
CREATE TABLE `New Table` (
`Web_Edited` tinyint unsigned NULL,
`Company_Id` date NOT NULL,
`Rn_Descriptor` varchar (80) NULL, ...
Why is the Company_Id set to date and not varbinary?
Secondly, if I change the create statement
...`Company_Id` date NOT NULL, ...
to
... `Company_Id` varbinary(8) NOT NULL, ...
then on the Transform Data Task's Destination Tab, the Company_Id is
displayed as longtext. And then I specify that the Company Id column
is the only column I wanted in the transformation, and executed the
trasformation.
I get an error stating this:
The number of failing rows exceeds the maximum specified.
TransformCopy 'DTSTransformation__1' conversion error: General
conversion failure on column pair 1(source column 'Company_Id'
(DBTYPE_BYTES), destination column 'Company_Id' (DBTYPE_STR))
But I already specified the Company_Id column as a binary, and I even
checked the MySQL table's strucutre and it was also set to varbinary(8)
Please help.Hi
I don't know much about MYSQL, but this may be a driver issue, are you using
ODBC? If MYSQL can import text files you may want to try using BCP and then
importing from MYSQL.
John
"datenisis@.gmail.com" wrote:
> I have a MSSQL table with Primary Keys, Company_Id, set as a binary
> value. I used the dts Transform Data Task and to create a new table in
> MySQL.
> The default create code comes up with:
> CREATE TABLE `New Table` (
> `Web_Edited` tinyint unsigned NULL,
> `Company_Id` date NOT NULL,
> `Rn_Descriptor` varchar (80) NULL, ...
> Why is the Company_Id set to date and not varbinary?
> Secondly, if I change the create statement
> ...`Company_Id` date NOT NULL, ...
> to
> ... `Company_Id` varbinary(8) NOT NULL, ...
> then on the Transform Data Task's Destination Tab, the Company_Id is
> displayed as longtext. And then I specify that the Company Id column
> is the only column I wanted in the transformation, and executed the
> trasformation.
> I get an error stating this:
> The number of failing rows exceeds the maximum specified.
> TransformCopy 'DTSTransformation__1' conversion error: General
> conversion failure on column pair 1(source column 'Company_Id'
> (DBTYPE_BYTES), destination column 'Company_Id' (DBTYPE_STR))
> But I already specified the Company_Id column as a binary, and I even
> checked the MySQL table's strucutre and it was also set to varbinary(8)
> Please help.
>

exporting an analysis server db from one server to another


how do i export an analysis services from one server to another? i recently upgraded from analysis services 2000, so i didnt create the analysis services database and cubes in server 2005. Remember how you could save your db in analysis server 2000 as a cab file. is there any way to do that in analysis server 2005, so that i could simply then copy teh file over to a new server and add it?

In BI-Dev studio there is a project/template where you can import and generate scripts for that SSAS2005 cube to a project/script. If you successfully import this you will have scripts that you can deploy to a second server.

The other way is the Deployment Wizard, under start/programs/sql server 2005/Analysis services. This wizard requires a SSAS2005 project.

HTH

Thomas Ivarsson

|||my problem is trying to get the cubes as a SSAS2005 project. they are deployed using 2005, but just cant get them as a project.|||

Have you started BI-Dev Studio and tried the template I have described?

Regards

Thomas Ivarsson

|||

If you simply trying to create a project based on the existing cubes, you can simly go into BI Dev studio and create new project using "Import Analysis Services 9.0 database" project type.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Friday, March 9, 2012

Export to pdf fail

Hello,

I'm using SQL Reporting services 2000 SP2.
I create a report and when i select pdf format in the export dropdownlist and i click on export, a popup (open/save) appears but when i click ok, i've an error message : "IE can't download Format=PDF of IP_ADRESSE".

If i do the same thing with excel type in the dropdownlist it works!

Do you know why?Have you tried looking in the log files or Event viewer on the server?|||Yes I have an exception in the reporting services log but nothing in the event viewer.
The exception :
[code]
w3wp!library!d10!08/25/2006-14:18:52:: i INFO: Call to RenderNext( '/EPMDB_Report/EPM_Full_Dashboard' )
w3wp!chunks!d10!08/25/2006-14:18:52:: i INFO: ### GetReportChunk('RenderingInfo_PDF', 2), chunk was not found! this=3ffd3365-b936-477e-9108-c7ad88799346
w3wp!reportrendering!d10!08/25/2006-14:18:52:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown., ;
Info: Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. > Microsoft.ReportingServices.ReportRendering.ReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. > System.ArgumentOutOfRangeException: Index and length must refer to a location within the string.
Parameter name: length
at System.String.Substring(Int32 startIndex, Int32 length)
at Microsoft.ReportingServices.Rendering.ImageRenderer.CanvasPDF.ProcessString(TextBox context, String text, CanvasFontPDF font, CanvasPenPDF pen, Double left, Double top, Double paddingTop, Double width, Double height, Boolean wordTrim, String fontLanguage, Int32& charactersFitted, Int32& linesFilled, String& streamString)
at Microsoft.ReportingServices.Rendering.ImageRenderer.CanvasPDF.DrawString(TextBox context, OutlineObjects outlines, Boolean wordTrim, String text, CanvasFont font, CanvasPen pen, Double left, Double top, Double paddingTop, Double width, Double height, String fontLanguage, ReportUrl hyperLinkURL)
at Microsoft.ReportingServices.Rendering.ImageRenderer.TextBox.RenderToPage(Measurement parentClipBorder)
at Microsoft.ReportingServices.Rendering.ImageRenderer.RenderingItemContainer.RenderToPage(Measurement parentClipBorder)
at Microsoft.ReportingServices.Rendering.ImageRenderer.Rectangle.RenderToPage(Measurement parentClipBorder)
at Microsoft.ReportingServices.Rendering.ImageRenderer.RenderingItemContainer.RenderToPage(Measurement parentClipBorder)
at Microsoft.ReportingServices.Rendering.ImageRenderer.ImageRendererBase.ProcessPages(CompositionBase theComposition, Report theReport, Boolean needTotalPages, Int32 startPage, Int32 endPage, Boolean render)
at Microsoft.ReportingServices.Rendering.ImageRenderer.PdfReport.RenderReport(Report report, NameValueCollection deviceInfo, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStreamCallback)
at Microsoft.ReportingServices.Rendering.ImageRenderer.ImageRendererBase.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream)
End of inner exception stack trace
at Microsoft.ReportingServices.Rendering.ImageRenderer.ImageRendererBase.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream)
at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderSnapshot(CreateReportChunk createChunkCallback, RenderingContext rc, GetResource getResourceCallback)
End of inner exception stack trace
w3wp!webserver!d10!08/25/2006-14:18:52:: e ERROR: Reporting Services error Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. > Microsoft.ReportingServices.ReportRendering.ReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. > System.ArgumentOutOfRangeException: Index and length must refer to a location within the string.
Parameter name: length
[/code]

It seems to be a problem with the length parameter but i don't known what to do.

Any idea?|||Ok I find th problem.
In my rdl file I have the language define as 'en' and for exporting in pdf the format has to been syntaxed like this: xx-XX.
I put my rdl in en-US and it works.

Thanks @.+|||I found I had put some HTTP headers in at the website level on IIS to disable caching on some non-related ASP applications running in the same IIS instance.

I removed the HTTP headers:

Cache-Control: no-cache

Pragma: no-cache

Export to pdf fail

Hello,

I'm using SQL Reporting services 2000 SP2.
I create a report and when i select pdf format in the export dropdownlist and i click on export, a popup (open/save) appears but when i click ok, i've an error message : "IE can't download Format=PDF of IP_ADRESSE".

If i do the same thing with excel type in the dropdownlist it works!

Do you know why?
Have you tried looking in the log files or Event viewer on the server?|||Yes I have an exception in the reporting services log but nothing in the event viewer.
The exception :
[code]
w3wp!library!d10!08/25/2006-14:18:52:: i INFO: Call to RenderNext( '/EPMDB_Report/EPM_Full_Dashboard' )
w3wp!chunks!d10!08/25/2006-14:18:52:: i INFO: ### GetReportChunk('RenderingInfo_PDF', 2), chunk was not found! this=3ffd3365-b936-477e-9108-c7ad88799346
w3wp!reportrendering!d10!08/25/2006-14:18:52:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown., ;
Info: Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. > Microsoft.ReportingServices.ReportRendering.ReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. > System.ArgumentOutOfRangeException: Index and length must refer to a location within the string.
Parameter name: length
at System.String.Substring(Int32 startIndex, Int32 length)
at Microsoft.ReportingServices.Rendering.ImageRenderer.CanvasPDF.ProcessString(TextBox context, String text, CanvasFontPDF font, CanvasPenPDF pen, Double left, Double top, Double paddingTop, Double width, Double height, Boolean wordTrim, String fontLanguage, Int32& charactersFitted, Int32& linesFilled, String& streamString)
at Microsoft.ReportingServices.Rendering.ImageRenderer.CanvasPDF.DrawString(TextBox context, OutlineObjects outlines, Boolean wordTrim, String text, CanvasFont font, CanvasPen pen, Double left, Double top, Double paddingTop, Double width, Double height, String fontLanguage, ReportUrl hyperLinkURL)
at Microsoft.ReportingServices.Rendering.ImageRenderer.TextBox.RenderToPage(Measurement parentClipBorder)
at Microsoft.ReportingServices.Rendering.ImageRenderer.RenderingItemContainer.RenderToPage(Measurement parentClipBorder)
at Microsoft.ReportingServices.Rendering.ImageRenderer.Rectangle.RenderToPage(Measurement parentClipBorder)
at Microsoft.ReportingServices.Rendering.ImageRenderer.RenderingItemContainer.RenderToPage(Measurement parentClipBorder)
at Microsoft.ReportingServices.Rendering.ImageRenderer.ImageRendererBase.ProcessPages(CompositionBase theComposition, Report theReport, Boolean needTotalPages, Int32 startPage, Int32 endPage, Boolean render)
at Microsoft.ReportingServices.Rendering.ImageRenderer.PdfReport.RenderReport(Report report, NameValueCollection deviceInfo, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStreamCallback)
at Microsoft.ReportingServices.Rendering.ImageRenderer.ImageRendererBase.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream)
End of inner exception stack trace
at Microsoft.ReportingServices.Rendering.ImageRenderer.ImageRendererBase.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream)
at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderSnapshot(CreateReportChunk createChunkCallback, RenderingContext rc, GetResource getResourceCallback)
End of inner exception stack trace
w3wp!webserver!d10!08/25/2006-14:18:52:: e ERROR: Reporting Services error Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. > Microsoft.ReportingServices.ReportRendering.ReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. > System.ArgumentOutOfRangeException: Index and length must refer to a location within the string.
Parameter name: length
[/code]

It seems to be a problem with the length parameter but i don't known what to do.

Any idea?
|||Ok I find th problem.
In my rdl file I have the language define as 'en' and for exporting in pdf the format has to been syntaxed like this: xx-XX.
I put my rdl in en-US and it works.

Thanks @.+
|||I found I had put some HTTP headers in at the website level on IIS to disable caching on some non-related ASP applications running in the same IIS instance.

I removed the HTTP headers:
Cache-Control: no-cache Pragma: no-cache

Export to PDF Different!?

I am having problems with my matrix that is embedded in a list. I used a
workaround to create a horizontal table (matrix + list). Now, when i run my
report, the row sizes of the matrix always shrink, even if i put CanGrow and
CanShrink to false.
So now, i haev set CanGrow to true, and have aligned other fields
(textboxes) to line up with their corresponding rows. It looks "decent" when
i run the report on the report server, but when i click export to pdf, all
the textboxes are shifted up and are no longer lined up. Does this happen to
anyone else, and is this a known issue (differences in rendering between the
report server and exporting) ?
Any help is GREATLY appreciated. Thanks!
--whitOn Apr 25, 3:14 pm, Whitney Yiu <Whitney...@.discussions.microsoft.com>
wrote:
> I am having problems with my matrix that is embedded in a list. I used a
> workaround to create a horizontal table (matrix + list). Now, when i run my
> report, the row sizes of the matrix always shrink, even if i put CanGrow and
> CanShrink to false.
> So now, i haev set CanGrow to true, and have aligned other fields
> (textboxes) to line up with their corresponding rows. It looks "decent" when
> i run the report on the report server, but when i click export to pdf, all
> the textboxes are shifted up and are no longer lined up. Does this happen to
> anyone else, and is this a known issue (differences in rendering between the
> report server and exporting) ?
> Any help is GREATLY appreciated. Thanks!
> --whit
You might want to check the properties for each cell in the matrix
control. Check the Format tab and make sure that: 'Can decrease to
accommodate contents' is not selected. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Wednesday, March 7, 2012

Export to Exel

I am newbie in exporting data.

i am using visual studio.net 2003 and using vb.net to create a webapplication.

My problem is how do i query my data from MS-SQL and export to microsoft Exel??

Can some one gave me the sample code?

Thanks

I had success to export my data to exel,

but why must i save the file before i open it??

Can anyone help me solve this problem??

My coding is Below :

Dim

rAsNew Columnar_Landed

r.SetDatabaseLogon("username", "password", "192.xxx.xxx.xxx,xx", "DTZ-VMS")

r.RecordSelectionFormula = query

Dim oStreamAsNew MemoryStream

oStream = r.ExportToStream(CrystalDecisions.Shared.ExportFormatType.ExcelRecord)

HttpContext.Current.Response.Clear()

HttpContext.Current.Response.Buffer =

True

HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"

HttpContext.Current.Response.BinaryWrite(oStream.ToArray())

HttpContext.Current.Response.End()

|||

Please search the forum. You will find a lot of material on it. Search by "Gridview to excel". one important link is:

http://forums.asp.net/thread/1573815.aspx

Please mark this post as resolved if you r done

Export to excel problem

Hi,

WHen i export a report(which i dynamically create) to excel it spews the following :

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/PGh0bWwgeG1sbnM6dj0idXJuOnNjaGVtYXMtbWljcm9zb2Z0LWNvbTp2bWwiIHhtbG5zOm89InVybjpzY2hlbWFzLW1pY3Jvc29mdC1jb206b2ZmaWNlOm9mZmljZSIgeG1sbnM6eD0idXJuOnNjaGVtYXMtbWljcm9zb2Z0LWNvbTpvZmZpY2U6ZXhjZWwiIHhtbG5zPSJodHRwOi8vIHd3dy53My5vcmcvVFIvUkVDLWh0bWw0MCI+CjxoZWFkPgo8bWV0YSBuYW1lPSJFeGNlbCBXb3JrYm9vayBGcmFtZXNldCI+CjxtZXRhIGh0dHAtZXF1aXY9Q29udGVudC1UeXBlIGNvbnRlbnQ9InRleHQvaHRtbDsgY2hhcnNldD11dGYtOCI+Cjx4bWw+PG86RG9jdW1lbnRQcm9wZXJ0aWVzPjxvOkNyZWF0ZWQ+RnJpZGF5LCAxMiBNYXkgMjAwNiAxNTowOToxNDwvbzpDcmVhdGVkPgo8bzpMYXN0U2F2ZWQ+RnJpZGF5LCAxMiBNYXkgMjAwNiAxNTowOToxNDwvbzpMYXN0U2F2ZWQ+CjwvbzpEb2N1bWVudFByb3BlcnRpZXM+PC94bWw+PGxpbmsgaWQ9InNoTGluayIgaHJlZj0iU2hlZXQxLmh0bSI+PHhtbD48eDpFeGNlbFdvcmtib29rPjx4OkV4Y2VsV29ya3NoZWV0cz48eDpFeGNlbFdvcmtzaGVldD48eDpOYW1lPlJlcG9ydDwveDpOYW1lPjx4OldvcmtzaGVldFNvdXJjZSBIUmVmPSJTaGVldDEuaHRtIi8+PHg6V29ya3NoZWV0T3B0aW9ucz48eDpOb1N1bW1hcnlSb3dzQmVsb3dEZXRhaWwvPjx4Ok5vU3VtbWFyeUNvbHVtbnNSaWdodERldGFpbC8+PHg6RG9Ob3REaXNwbGF5R3JpZGxpbmVzLz48eDpQcmludD48eDpWYWxpZFByaW50ZXJJbmZvLz48eDpQYXBlclNp

=_NextPart_01C35DB7.4B204430

Content-Type: text/html

Content-Transfer-Encoding: base64

Content-Location: file:///c:/Sheet1.htm

Any ideas on how to resolve this?

I am experiencing the same exact problem on two machines. Did you ever find an answer?

|||

Can anyone help us? I am still looking for a solution.

Thanks. -Nittany92

|||

What version and service pack level of RS are you using? Can you please explain exactly what steps you are taking to export the report?

Thanks,
Chris

|||

I did finally find out that this issue was fixed in SQL Reporting Services SP1. Our reporting server was using SP1 but I was only given the original release to install on my PC. I have since applied SQL Reporting Services SP2 to my PC and I am now able to export to an Excel 2000 file successfully.

See the following info...

http://download.microsoft.com/download/5/1/3/513534ae-a0e7-44e6-9a04-ba3c549a5f5f/sp2Readme_EN.htm

4.2.8 Excel Rendering Extension

New in SP1. Significant improvements have been made to the Excel rendering extension. The output format produced by the rendering extension has changed. Improvements include the following: Rendered reports can now be opened in Excel 97 and later. Previously, the file format was MIME Encapsulation of Aggregate HTML (MHTML), which could only be opened in Excel 2002 and later. The format for rendered reports is now Binary Interchange File Format (BIFF), which can be opened in Excel 97 and later.

Export to excel problem

Hi,

WHen i export a report(which i dynamically create) to excel it spews the following :

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/PGh0bWwgeG1sbnM6dj0idXJuOnNjaGVtYXMtbWljcm9zb2Z0LWNvbTp2bWwiIHhtbG5zOm89InVybjpzY2hlbWFzLW1pY3Jvc29mdC1jb206b2ZmaWNlOm9mZmljZSIgeG1sbnM6eD0idXJuOnNjaGVtYXMtbWljcm9zb2Z0LWNvbTpvZmZpY2U6ZXhjZWwiIHhtbG5zPSJodHRwOi8vIHd3dy53My5vcmcvVFIvUkVDLWh0bWw0MCI+CjxoZWFkPgo8bWV0YSBuYW1lPSJFeGNlbCBXb3JrYm9vayBGcmFtZXNldCI+CjxtZXRhIGh0dHAtZXF1aXY9Q29udGVudC1UeXBlIGNvbnRlbnQ9InRleHQvaHRtbDsgY2hhcnNldD11dGYtOCI+Cjx4bWw+PG86RG9jdW1lbnRQcm9wZXJ0aWVzPjxvOkNyZWF0ZWQ+RnJpZGF5LCAxMiBNYXkgMjAwNiAxNTowOToxNDwvbzpDcmVhdGVkPgo8bzpMYXN0U2F2ZWQ+RnJpZGF5LCAxMiBNYXkgMjAwNiAxNTowOToxNDwvbzpMYXN0U2F2ZWQ+CjwvbzpEb2N1bWVudFByb3BlcnRpZXM+PC94bWw+PGxpbmsgaWQ9InNoTGluayIgaHJlZj0iU2hlZXQxLmh0bSI+PHhtbD48eDpFeGNlbFdvcmtib29rPjx4OkV4Y2VsV29ya3NoZWV0cz48eDpFeGNlbFdvcmtzaGVldD48eDpOYW1lPlJlcG9ydDwveDpOYW1lPjx4OldvcmtzaGVldFNvdXJjZSBIUmVmPSJTaGVldDEuaHRtIi8+PHg6V29ya3NoZWV0T3B0aW9ucz48eDpOb1N1bW1hcnlSb3dzQmVsb3dEZXRhaWwvPjx4Ok5vU3VtbWFyeUNvbHVtbnNSaWdodERldGFpbC8+PHg6RG9Ob3REaXNwbGF5R3JpZGxpbmVzLz48eDpQcmludD48eDpWYWxpZFByaW50ZXJJbmZvLz48eDpQYXBlclNp

=_NextPart_01C35DB7.4B204430

Content-Type: text/html

Content-Transfer-Encoding: base64

Content-Location: file:///c:/Sheet1.htm

Any ideas on how to resolve this?

I am experiencing the same exact problem on two machines. Did you ever find an answer?

|||

Can anyone help us? I am still looking for a solution.

Thanks. -Nittany92

|||

What version and service pack level of RS are you using? Can you please explain exactly what steps you are taking to export the report?

Thanks,
Chris

|||

I did finally find out that this issue was fixed in SQL Reporting Services SP1. Our reporting server was using SP1 but I was only given the original release to install on my PC. I have since applied SQL Reporting Services SP2 to my PC and I am now able to export to an Excel 2000 file successfully.

See the following info...

http://download.microsoft.com/download/5/1/3/513534ae-a0e7-44e6-9a04-ba3c549a5f5f/sp2Readme_EN.htm

4.2.8 Excel Rendering Extension

New in SP1. Significant improvements have been made to the Excel rendering extension. The output format produced by the rendering extension has changed. Improvements include the following:

Rendered reports can now be opened in Excel 97 and later. Previously, the file format was MIME Encapsulation of Aggregate HTML (MHTML), which could only be opened in Excel 2002 and later. The format for rendered reports is now Binary Interchange File Format (BIFF), which can be opened in Excel 97 and later.

Export to excel problem

Hi,

WHen i export a report(which i dynamically create) to excel it spews the following :

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/PGh0bWwgeG1sbnM6dj0idXJuOnNjaGVtYXMtbWljcm9zb2Z0LWNvbTp2bWwiIHhtbG5zOm89InVybjpzY2hlbWFzLW1pY3Jvc29mdC1jb206b2ZmaWNlOm9mZmljZSIgeG1sbnM6eD0idXJuOnNjaGVtYXMtbWljcm9zb2Z0LWNvbTpvZmZpY2U6ZXhjZWwiIHhtbG5zPSJodHRwOi8vIHd3dy53My5vcmcvVFIvUkVDLWh0bWw0MCI+CjxoZWFkPgo8bWV0YSBuYW1lPSJFeGNlbCBXb3JrYm9vayBGcmFtZXNldCI+CjxtZXRhIGh0dHAtZXF1aXY9Q29udGVudC1UeXBlIGNvbnRlbnQ9InRleHQvaHRtbDsgY2hhcnNldD11dGYtOCI+Cjx4bWw+PG86RG9jdW1lbnRQcm9wZXJ0aWVzPjxvOkNyZWF0ZWQ+RnJpZGF5LCAxMiBNYXkgMjAwNiAxNTowOToxNDwvbzpDcmVhdGVkPgo8bzpMYXN0U2F2ZWQ+RnJpZGF5LCAxMiBNYXkgMjAwNiAxNTowOToxNDwvbzpMYXN0U2F2ZWQ+CjwvbzpEb2N1bWVudFByb3BlcnRpZXM+PC94bWw+PGxpbmsgaWQ9InNoTGluayIgaHJlZj0iU2hlZXQxLmh0bSI+PHhtbD48eDpFeGNlbFdvcmtib29rPjx4OkV4Y2VsV29ya3NoZWV0cz48eDpFeGNlbFdvcmtzaGVldD48eDpOYW1lPlJlcG9ydDwveDpOYW1lPjx4OldvcmtzaGVldFNvdXJjZSBIUmVmPSJTaGVldDEuaHRtIi8+PHg6V29ya3NoZWV0T3B0aW9ucz48eDpOb1N1bW1hcnlSb3dzQmVsb3dEZXRhaWwvPjx4Ok5vU3VtbWFyeUNvbHVtbnNSaWdodERldGFpbC8+PHg6RG9Ob3REaXNwbGF5R3JpZGxpbmVzLz48eDpQcmludD48eDpWYWxpZFByaW50ZXJJbmZvLz48eDpQYXBlclNp

=_NextPart_01C35DB7.4B204430

Content-Type: text/html

Content-Transfer-Encoding: base64

Content-Location: file:///c:/Sheet1.htm

Any ideas on how to resolve this?

I am experiencing the same exact problem on two machines. Did you ever find an answer?

|||

Can anyone help us? I am still looking for a solution.

Thanks. -Nittany92

|||

What version and service pack level of RS are you using? Can you please explain exactly what steps you are taking to export the report?

Thanks,
Chris

|||

I did finally find out that this issue was fixed in SQL Reporting Services SP1. Our reporting server was using SP1 but I was only given the original release to install on my PC. I have since applied SQL Reporting Services SP2 to my PC and I am now able to export to an Excel 2000 file successfully.

See the following info...

http://download.microsoft.com/download/5/1/3/513534ae-a0e7-44e6-9a04-ba3c549a5f5f/sp2Readme_EN.htm

4.2.8 Excel Rendering Extension

New in SP1. Significant improvements have been made to the Excel rendering extension. The output format produced by the rendering extension has changed. Improvements include the following: Rendered reports can now be opened in Excel 97 and later. Previously, the file format was MIME Encapsulation of Aggregate HTML (MHTML), which could only be opened in Excel 2002 and later. The format for rendered reports is now Binary Interchange File Format (BIFF), which can be opened in Excel 97 and later.

Friday, February 24, 2012

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 :-)

Sunday, February 19, 2012

Export to CSV without header line

Hi,
Is it possible to export to CSV without header line?
I create report for my user where they can retrieve data in csv(txt) file
for their further use.
I would like to skip generation of header line in CVS rendering if it is
possible.
Thanks
DonThanks for the tip.
But where do you set the device info?
Sissel
"Gastón Pírez" wrote:
> You can do this setting a parameter in the device info
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_soapapi_dev_34fa.asp
> Gaston.-
> "Don" <Don@.discussions.microsoft.com> wrote in message
> news:ECC0D669-97A0-49D4-9914-50ABCD4C3223@.microsoft.com...
> > Hi,
> >
> > Is it possible to export to CSV without header line?
> >
> > I create report for my user where they can retrieve data in csv(txt) file
> > for their further use.
> > I would like to skip generation of header line in CVS rendering if it is
> > possible.
> >
> > Thanks
> > Don
> >
>
>|||Try www.sqlscripter.com to export data to text/csv.
It's free.
"Don" wrote:
> Hi,
> Is it possible to export to CSV without header line?
> I create report for my user where they can retrieve data in csv(txt) file
> for their further use.
> I would like to skip generation of header line in CVS rendering if it is
> possible.
> Thanks
> Don
>|||in sql server 2000 reporting services, can i export csv without header line?
"Thomas" <Thomas@.discussions.microsoft.com> wrote in message
news:CCE0FED9-1AA5-4C0A-8041-A5DE99D66C42@.microsoft.com...
> Try www.sqlscripter.com to export data to text/csv.
> It's free.
>
> "Don" wrote:
>> Hi,
>> Is it possible to export to CSV without header line?
>> I create report for my user where they can retrieve data in csv(txt) file
>> for their further use.
>> I would like to skip generation of header line in CVS rendering if it is
>> possible.
>> Thanks
>> Don|||Good question. I'd like to know how to export csv without header lines in
RS2005. AND can someone tell me how to customer csv render in a data-drive
subscription? Can we do that automatically so that users who retrieve the
report from a designed folder will be able to open the csv file automatically
w/o headers? thanks
"Peter Fuller" wrote:
> in sql server 2000 reporting services, can i export csv without header line?
> "Thomas" <Thomas@.discussions.microsoft.com> wrote in message
> news:CCE0FED9-1AA5-4C0A-8041-A5DE99D66C42@.microsoft.com...
> > Try www.sqlscripter.com to export data to text/csv.
> > It's free.
> >
> >
> > "Don" wrote:
> >
> >> Hi,
> >>
> >> Is it possible to export to CSV without header line?
> >>
> >> I create report for my user where they can retrieve data in csv(txt) file
> >> for their further use.
> >> I would like to skip generation of header line in CVS rendering if it is
> >> possible.
> >>
> >> Thanks
> >> Don
> >>
>
>

Export to Access

Would like to know anyone knows how to create the ability to export to Access
from RS web. Maybe making it available in the dropdown. We are reaching the
limitations of excel and would like to automate it saving as table in access.
ThanksAndrew,
I'm not sure if you can get to access from RS directly but a
possible way round it would be to set up a subscription in RS to save the
output as a csv file on a fileshare at regular intervals. You could then
import the csv file into access as a table.
Hope this helps.
Nick Colebourn
"Andrew" wrote:
> Would like to know anyone knows how to create the ability to export to Access
> from RS web. Maybe making it available in the dropdown. We are reaching the
> limitations of excel and would like to automate it saving as table in access.
> Thanks|||RS doesn't export to MS Access. As Nick said you can export to CSV or since
you are already exporting to Excel then import those files into Access.
"Nick Colebourn" <NickColebourn@.discussions.microsoft.com> wrote in message
news:2A6D94F2-099F-426E-9335-49C779F4747B@.microsoft.com...
> Andrew,
> I'm not sure if you can get to access from RS directly but a
> possible way round it would be to set up a subscription in RS to save the
> output as a csv file on a fileshare at regular intervals. You could then
> import the csv file into access as a table.
> Hope this helps.
> Nick Colebourn
> "Andrew" wrote:
> > Would like to know anyone knows how to create the ability to export to
Access
> > from RS web. Maybe making it available in the dropdown. We are
reaching the
> > limitations of excel and would like to automate it saving as table in
access.
> > Thanks|||I have a similar problem. I want to automatically have an excel file imported
into access without user intervention. Is this possible?
"Brian Bischof" wrote:
> RS doesn't export to MS Access. As Nick said you can export to CSV or since
> you are already exporting to Excel then import those files into Access.
>
> "Nick Colebourn" <NickColebourn@.discussions.microsoft.com> wrote in message
> news:2A6D94F2-099F-426E-9335-49C779F4747B@.microsoft.com...
> > Andrew,
> > I'm not sure if you can get to access from RS directly but a
> > possible way round it would be to set up a subscription in RS to save the
> > output as a csv file on a fileshare at regular intervals. You could then
> > import the csv file into access as a table.
> >
> > Hope this helps.
> >
> > Nick Colebourn
> >
> > "Andrew" wrote:
> >
> > > Would like to know anyone knows how to create the ability to export to
> Access
> > > from RS web. Maybe making it available in the dropdown. We are
> reaching the
> > > limitations of excel and would like to automate it saving as table in
> access.
> > > Thanks
>
>

Export Template

Is it true that we cannot create a template for a Report Server project?
--
Harry RiddleIt is false, we can create a template, it works like this, we can create a
standard template and copied on a specific directory, so whenever you go for
creating reports this can be selected, so that all your reports will be of
same look and feel.
Amarnath
"Harry" wrote:
> Is it true that we cannot create a template for a Report Server project?
> --
> Harry Riddle|||How would I accomplish that? The export template button is grayed out when I
am in a Report Server Project.
--
Harry Riddle
"Amarnath" wrote:
> It is false, we can create a template, it works like this, we can create a
> standard template and copied on a specific directory, so whenever you go for
> creating reports this can be selected, so that all your reports will be of
> same look and feel.
> Amarnath
> "Harry" wrote:
> > Is it true that we cannot create a template for a Report Server project?
> > --
> > Harry Riddle|||Are we in sync ? I think I am talking about diff. template, I doubt. ok,
where is this
"The export template button is grayed out " where exactly is this.
Amarnath
"Harry" wrote:
> How would I accomplish that? The export template button is grayed out when I
> am in a Report Server Project.
> --
> Harry Riddle
>
> "Amarnath" wrote:
> > It is false, we can create a template, it works like this, we can create a
> > standard template and copied on a specific directory, so whenever you go for
> > creating reports this can be selected, so that all your reports will be of
> > same look and feel.
> >
> > Amarnath
> >
> > "Harry" wrote:
> >
> > > Is it true that we cannot create a template for a Report Server project?
> > > --
> > > Harry Riddle|||Project templates are created using this procedure:
http://msdn2.microsoft.com/en-us/library/xkh1wxd8(VS.80).aspx
The export template option is grayed out when you select a business
intelligence project from which to create your template.
--
Harry Riddle
"Amarnath" wrote:
> Are we in sync ? I think I am talking about diff. template, I doubt. ok,
> where is this
> "The export template button is grayed out " where exactly is this.
> Amarnath
>
> "Harry" wrote:
> > How would I accomplish that? The export template button is grayed out when I
> > am in a Report Server Project.
> > --
> > Harry Riddle
> >
> >
> > "Amarnath" wrote:
> >
> > > It is false, we can create a template, it works like this, we can create a
> > > standard template and copied on a specific directory, so whenever you go for
> > > creating reports this can be selected, so that all your reports will be of
> > > same look and feel.
> > >
> > > Amarnath
> > >
> > > "Harry" wrote:
> > >
> > > > Is it true that we cannot create a template for a Report Server project?
> > > > --
> > > > Harry Riddle|||oh I am sorry, I was explaining about report template.
Amarnath
"Harry" wrote:
> Project templates are created using this procedure:
> http://msdn2.microsoft.com/en-us/library/xkh1wxd8(VS.80).aspx
> The export template option is grayed out when you select a business
> intelligence project from which to create your template.
> --
> Harry Riddle
>
> "Amarnath" wrote:
> > Are we in sync ? I think I am talking about diff. template, I doubt. ok,
> > where is this
> > "The export template button is grayed out " where exactly is this.
> >
> > Amarnath
> >
> >
> >
> > "Harry" wrote:
> >
> > > How would I accomplish that? The export template button is grayed out when I
> > > am in a Report Server Project.
> > > --
> > > Harry Riddle
> > >
> > >
> > > "Amarnath" wrote:
> > >
> > > > It is false, we can create a template, it works like this, we can create a
> > > > standard template and copied on a specific directory, so whenever you go for
> > > > creating reports this can be selected, so that all your reports will be of
> > > > same look and feel.
> > > >
> > > > Amarnath
> > > >
> > > > "Harry" wrote:
> > > >
> > > > > Is it true that we cannot create a template for a Report Server project?
> > > > > --
> > > > > Harry Riddle|||Hello Harry,
This is by design.
The export template function only works for the .NET project, not for the
Report Project now.
Please send your feedback to the product team:
http://connect.microsoft.com/visutalstudio
Thanks!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Is there a best practice to instill a report layout standard without the use
of templates for the many users creating a report in our company?
--
Harry Riddle
"Wei Lu [MSFT]" wrote:
> Hello Harry,
> This is by design.
> The export template function only works for the .NET project, not for the
> Report Project now.
> Please send your feedback to the product team:
> http://connect.microsoft.com/visutalstudio
> Thanks!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Harry,
Currently, we did not have any best practice on this.
You may follow this article to have a try:
http://www.databasejournal.com/features/mssql/article.php/10894_3422801_9
This response contains a reference to a third party World Wide Web site.
Microsoft is providing this information as a convenience to you. Microsoft
does not control these sites and has not tested any software or information
found on these sites; therefore, Microsoft cannot make any representations
regarding the quality, safety, or suitability of any software or
information found there. There are inherent dangers in the use of any
software found on the Internet, and Microsoft cautions you to make sure
that you completely understand the risk before retrieving any software from
the Internet.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Visual Studio 2005 handles templates in a completely different manner. I
have created two shared network locations for the data sources and a report.
I have instructed them to import both into their newly created project.
Another item in the very long list of short-sighted ideas from Microsoft.
IOW, vision that becomes near-sighted past the end of their nose.
--
Harry Riddle
"Wei Lu [MSFT]" wrote:
> Hello Harry,
> Currently, we did not have any best practice on this.
> You may follow this article to have a try:
> http://www.databasejournal.com/features/mssql/article.php/10894_3422801_9
> This response contains a reference to a third party World Wide Web site.
> Microsoft is providing this information as a convenience to you. Microsoft
> does not control these sites and has not tested any software or information
> found on these sites; therefore, Microsoft cannot make any representations
> regarding the quality, safety, or suitability of any software or
> information found there. There are inherent dangers in the use of any
> software found on the Internet, and Microsoft cautions you to make sure
> that you completely understand the risk before retrieving any software from
> the Internet.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Harry,
Yes, Visual Studio has used somewhat different model for management project
templates and also some other management and extension features. Also, as
Wei has suggested, you're welcome to send your request and comment about
the reusable template for SQL Server database/report projects to our
feedback center.
Here are some other reference on VS 2005 template management:
http://msdn.microsoft.com/msdnmag/issues/06/01/CodeTemplates/
http://msdn2.microsoft.com/en-us/library/6db0hwky(VS.80).aspx
http://www.developer.com/net/vb/article.php/3497836
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Harry,
Still any further questions on this? If there is anything else we can help,
please feel free to post here.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||No questions, Microsoft abruptly ended this conversation. They are starting
quite a track record with removing capabilities with each successive version
like losing edit and continue with VB.Net 1.0, losing multi-value parameter
capabilities in deployed reports with SQL Server 2005 SP1 and now this which
was available in VS.Net 2003. Will they ever learn? Each previous removal
has returned. Do you think that templated reports are not valuable to a
corporation's standard look and feel? Have the people running the show never
been in a corporate environment? Of course not. Microsoft policy has
required them to leave their acquired knowledge at the door. Open source is
looking better and better...
--
Harry Riddle
"Steven Cheng[MSFT]" wrote:
> Hello Harry,
> Still any further questions on this? If there is anything else we can help,
> please feel free to post here.
> Sincerely,
> Steven Cheng
> Microsoft MSDN Online Support Lead
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Thanks for your followup Harry,
I can understand your concern here and I agree that sometimes the
breakchange in sequential product versions may add the pain to users' work.
Actually, for most of the changes, they're also added according to user
community's feedback and the dev team is continously collecting feedback
from customers and arrange them depend on the vote rate and priority.
Anyway, I'm sorry that the drawback here make your poor experience,
however, I would still recommend you submit your feedback and comments so
that the dev team can hear more about this:
https://connect.microsoft.com/feedback/default.aspx?wa=wsignin1.0&siteid=68
Thanks for your understanding.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.

Export tables to csv file

Hi...

I have 4 connected table in sql, and i have view that query this 4 and create 1 table, can i export that to csv file?

thank you...

Once you have the SELECT written, export is quit easy. See http://www.codeproject.com/aspnet/Excel_CSV_export_button.asp An extended ASP.NET button control for exporting/downloading Excel or CSV file.

|||

Yes, you can!

How? By using DTS in SQL Server 2000 or SSIS in SQL Server 2005.

Justright click on the database and select "Tasks" then "Export".

Decide yoursource (your sql server andyour databasethat you are working on) then decide about yourdestination selectFlat File Destination from the drop down menu.

Click onBrowse, select your destination .cvs file.

Tip: You maycheck the box tomake table's columns in the first line of the .cvs file.

Select theFormat: (for example: Delimited)

Select theText Qualifier: (for example " or ' depends on your case).

Click onNext.

Now you can either to get dump thefull data in the table into the .cvs fileby selecting thefirst option,

OR

you may getpart of the data and dumpt it into the .cvs file by selecting thesecond option.

Click onNext.

Specify thesource Table OR View from the drop down menu. (usually you don't need to change the values of the others two drop down menues).

Click onNext, thenFinished.

We areDONE! Cool

I hope this steps will solve the issue.

Regards,
CS4Ever

-------------

If you find this post useful & helpful, please click on "Make as Answer" button.

Wednesday, February 15, 2012

Export SQL database Tables into HTML page

Hello,

I just want to know how can I create a SSIS package to export a few distinct tables into distinct HTML pages.

If anyone can help.

Thanks in advance.

Best regards...

Hi, are you looking for how to get distinct tables or the acutal HTML ouput? Is the idea that each table ends up as an HTML file with the data formatted with the proper tags of an HTML table?

For walking through the table list you may want to explore using a ForEach loop with a SMO enumerator to cylce through the tables. There is not built in feature/object for generating HTML though you may want to consider a script component, perhaps several, one as a transform and one as a custom destination.

|||

Thanks Craig for your reply,

Well, seams to me that export to HTML from Integration Services isn't a easy task... In my first aproach, I try using a query "Select * from table FOR XML AUTO" with output to a Flat destination file... I have some problems with types conversion... What I really wants is put each table content in a HTML file (or XML), and send all the files (ftp) to a specific site...

Best regards

Export sql data to text file

Hi all,

I am new to ssis. I try to create a package completely by vb.net to export a table in sql server to text file. i got the following error while i run the package,

An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for ODBC Drivers"
Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver Manager]
Data source name not found and no default driver specified".
The AcquireConnection method call to the connection manager "OLEDBSrc"
failed with error code 0xC0202009.
component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.
One or more component failed validation.
There were errors during task validation.

i have posted my code below,

Dim pkg As New Package

Dim OLEDBConMgr As ConnectionManager

Dim FileConMgr As ConnectionManager

Dim SrcComponent As IDTSComponentMetaData90

Dim SrcInstance As CManagedComponentWrapper

Dim DesComponent As IDTSComponentMetaData90

Dim DesInstance As CManagedComponentWrapper

pkg.PackageType = DTSPackageType.DTSDesigner90

Dim e As Executable = pkg.Executables.Add("DTS.Pipeline.1")

Dim thMainPipe As TaskHost = e 'as Task Host

Dim DataFlowTask As MainPipe = thMainPipe.InnerObject 'as MainPipe

'OLEDB Connection Manager

OLEDBConMgr = pkg.Connections.Add("OLEDB")

OLEDBConMgr.ConnectionString = "Data Source=srcServerName;Initial Catalog=srcDBName;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Auto Translate=False;"

OLEDBConMgr.Name = "OLEDBSrc"

OLEDBConMgr.Description = "OLEDB Connection to flightinfo database"

'FlatFile Connection Manager

FileConMgr = pkg.Connections.Add("FLATFILE")

FileConMgr.ConnectionString = "//FilePath"

FileConMgr.Name = "FLATFILE"

FileConMgr.Description = "Flat File Connection to the file"

FileConMgr.Properties("DataRowsToSkip").SetValue(FileConMgr, 0)

FileConMgr.Properties("Format").SetValue(FileConMgr, "Delimited")

FileConMgr.Properties("ColumnNamesInFirstDataRow").SetValue(FileConMgr, False)

FileConMgr.Properties("Unicode").SetValue(FileConMgr, False)

FileConMgr.Properties("RowDelimiter").SetValue(FileConMgr, vbCrLf)

FileConMgr.Properties("TextQualifier").SetValue(FileConMgr, "<none>")

FileConMgr.Properties("HeaderRowsToSkip").SetValue(FileConMgr, 0)

FileConMgr.Properties("HeaderRowDelimiter").SetValue(FileConMgr, vbCrLf)

FileConMgr.Properties("CodePage").SetValue(FileConMgr, 1252)

'Create Source Component

SrcComponent = DataFlowTask.ComponentMetaDataCollection.[New]

SrcComponent.ComponentClassID = "DTSAdapter.OLEDBSource"

SrcComponent.Name = "OLEDB"

'Get the Design time instance of the component

SrcInstance = SrcComponent.Instantiate

'Initialize the component

SrcInstance.ProvideComponentProperties()

'Specify the Connection Manager

If SrcComponent.RuntimeConnectionCollection.Count > 0 Then

SrcComponent.RuntimeConnectionCollection(0).ConnectionManagerID = OLEDBConMgr.ID

SrcComponent.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(OLEDBConMgr)

End If

'Set the Custom Properties

SrcInstance.SetComponentProperty("AccessMode", 0)

SrcInstance.SetComponentProperty("OpenRowset", "[dbo].[srcTableName]")

'ReInitialize the metadata

'SrcInstance.AcquireConnections(Nothing)

'SrcInstance.ReinitializeMetaData()

'SrcInstance.ReleaseConnections()

'Create Destination Component

DesComponent = DataFlowTask.ComponentMetaDataCollection.[New]

DesComponent.ComponentClassID = "DTSAdapter.FlatFileDestination"

DesComponent.Name = "FLATFILE"

'Get the Design time instance of the component

DesInstance = DesComponent.Instantiate

'Initialize the component

DesInstance.ProvideComponentProperties()

'Specify the Connection Manager

If DesComponent.RuntimeConnectionCollection.Count > 0 Then

DesComponent.RuntimeConnectionCollection(0).ConnectionManagerID = FileConMgr.ID

DesComponent.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(FileConMgr)

End If

'ReInitialize the metadata

'DesInstance.AcquireConnections(Nothing)

'DesInstance.ReinitializeMetaData()

'DesInstance.ReleaseConnections()

Dim path As IDTSPath90 = DataFlowTask.PathCollection.[New]

path.AttachPathAndPropagateNotifications(SrcComponent.OutputCollection(0), DesComponent.InputCollection(0))

' Get the destination's default input and virtual input.

Dim input As IDTSInput90 = DesComponent.InputCollection(0)

Dim vInput As IDTSVirtualInput90

vInput = input.GetVirtualInput()

'Iterate through the virtual column collection.

Dim vColumn As IDTSVirtualInputColumn90

' Iterate through the virtual input column collection.

For Each vColumn In vInput.VirtualInputColumnCollection

' Call the SetUsageType method of the destination

' to add each available virtual input column as an input column.

DesInstance.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)

Next

' Verify that the columns have been added to the input.

For Each inputColumn As IDTSInputColumn90 In DesComponent.InputCollection(0).InputColumnCollection

MsgBox(inputColumn.Name)

Next

Dim pkgResult As DTSExecResult

pkgResult = pkg.Execute

Is there anybody know it? plz help me.

regards,

sivani

OLEDBConMgr.ConnectionString = "Data

Source=srcServerName;Initial

Catalog=srcDBName;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist

Security Info=False;Auto Translate=False;"


Might be something with your connection string, Try:

"Data Source=srcServerName;Initial Catalog=srcDBName;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"|||

I changed the connection string but still i got the same error and in path creation,

Dim path As IDTSPath90 = DataFlowTask.PathCollection.[New]

path.AttachPathAndPropagateNotifications(SrcComponent.OutputCollection(0), DesComponent.InputCollection(0))

the following error occured at the endpoint while debugging

ErrorCode = -2147467259
Error HRESULT E_FAIL has been returned from a call to a COM component.

|||

Thanks a lot.

The connection string is the problem. i corrected the error but in flat file validation i got the following error, i think its related with column mapping but i dont know abt it. plz help me. the error is,

"component "FLATFILE" (31)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

One or more component failed validation.

There were errors during task validation.

|||

Hi,

Anybody has any idea to solve the "VS_NEEDNEWMETADATA" problem.

thanks

sivani.

|||What is the VS_NEEDNEWMETADATA problem?|||

Hi,

I already posted the problem in,

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

plz find it.

thanks

Sivani

|||

You are correct, the problem is related to the column mappings.

There are no external columns created in the above post for the flat file destination, nor are there any mapping to those columns. Hence, there is nothing to map the oledb sourced input columns into.

See the example (if you've downloaded the 2005 Samples) located by default at "%ProgramFiles%\Microsoft SQL Server\90\Samples\Integration Services\Programming Samples\Control Flow\CreatePackage Sample\VB" which does exactly what you're attempting here. The sample is a programmatic package builder which instantiates an OLEDB source that ends up in a flat file destination.

|||

Hi,

Thanks for ur suggestion. i solved the problem.

Thanks & Regards,

Sivani