Excel Destination Number Conversion Error

In my SSIS project I am populating an Excel spreadsheet with several worksheets. When I define a worksheet in Excel Destination Editor, I use Create Table SQL statement to create a worksheet. Most of the fields are defined as numbers, however when job runs my numeric fields appear as text in Excel with yellow warning sign - 'The number in this cell is formatted as text or preceeded by an apostrophe'. I need my numbers to appear as numbers. Is there a workaround for this?

Are you specifying Import Mode by using IMEX=1 in your connection string? This forces all data to text.

The Excel driver does store what it believes to be text data preceded by the apostrophe, to force the Excel application in turn to recognize it without question as text values. This implies to me that the columns in question contain at least some values that the driver is interpreting as text.

The BOL topic on the Excel Source contains some information that explains some of the behaviors of the Jet Excel driver.

-Doug

|||

Doug,

Thanks a lot for advice. I tried to put IMEX=0 (2) into a connection string, but it did not solve the problem. I wonder what the default is if IMEX parameter is not specified?

Sergey

|||

Sergey,

I was not encouraging the use of Import Mode...on the contrary, this forces everything to text and would presumably add the opening apostrophe everywhere.

You may want to try allowing the Import and Export Wizard to build your "base" package and add necessary Data Conversion tasks etc. for you. Then you can customize the package afterward.

Be careful to check that the driver is recognizing your numeric columns as numeric, by examining the data types of columns in the various components.

-Doug

Excel as a data mining client

Can I use MS Excel to generate the diagrams and charts that are being generated in your data mining project when you use VS.NET 2005? If so, how? I am trying to bring this technology to marketing people the simplest way possible without having to create my custom application. They just love the graphs, diagrams, and charts generated by VS.NET 2005 when you create data mining projects but I don't want to install it as client tools to my end users. Besides, these are not tech people but marketing staff.There is no direct way. You would have to write your own code to get the results into Excel. We will be releasing samples, but we don't have any plans to have any custom charting in Excel.

The controls we ship with VS will have a redist package at RTM. These are Winform controls and could be wrapped as ActiveX controls to be put in Excel, but we personally haven't tried it as of yet.

If you like writing code, I would recommend looking at the thin client viewer samples to see how they are put together. Then you can replicate the same behavior in Excel however is convenient for you.|||

Using VS 2005 and the Visual Studio Tools for Office you can very easily create an XL workbook or Word doc and in Visual studio drag .Net controls from the toolbox out onto the document. It is very simple since they do all the work of wrapping up the control in an ActiveX wrapper, and you can write managed code behind the events using VB.Net or C# etc. It’s really slick. I haven’t yet tried with our viewer controls yet though. It should work if you add the necessary references to your project (File | New | Project | Visual C# | Office | Excel Workbook) for example.

Hope that helps.

Scott Oveson
Microsoft SQL Server Analysis Services
Data Mining Tools

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

sql

Excel 2003 and nonemptycrossjoin

I am trying to utilize an Excel 2003 pivot table as a client tool to front end a project I am developing. I have created the cube in Analysis Services 2005 and it works fine when I run MDX against it directly. The problem that I am having is that Excel is not using nonemptycrossjoin in the pivot table. I have Teo Lachev's book "Applied Microsoft Analysis Services 2005' and I was looking for tips and found a note on p. 633 referring to a workaround (http://blogs.msdn.com/bi_systems/articles/162852.aspx).

To simplify testing, I reduced the query down to the most essential dimensions on rows and a single non-calculated measure on columns. The below query takes 1 second from SQL Server Management Studio.

select
nonemptycrossjoin([Product Family].[Product Families].[Product Brand].&[Family1],
[Shift Calendar].[Shift Fiscal].[Fiscal Year].&[2.006E3].&[2006-3].&[2006-08].&[2006-33],
[Failure Root Cause].[FA Failure Root Causes].[FA Pass Fail Indicator].&[Fail].&[Failure1],
[Comp1 Supplier].[Comp1 Suppliers].[Comp1 Supplier Description],
[Comp2 Supplier].[Comp2 Suppliers].[Comp2 Supplier Description]) on rows
{[Measures].[Sum Failed Qty]} on columns
from
[MyCube]

If I remove the nonempycrossjoin, keeping the () on rows to get the implicit crossjoin, the query runs but takes 114s. Now, if I start creating a similar query in Excel, the first few dimensions are pretty fast, but adding the last dimension on rows takes so long that my machine consumes all of its memory (2 GB) and CPU. I have to kill Excel to get my machine back.

So, Excel 2003 does not provide a facility to alter the Data Source, so after a bit of looking around I found the data source in "C:\Documents and Settings\username\Application Data\Microsoft\Queries". Opening my file test.oqy:

QueryType=OLEDB
Version=1
CommandType=Cube
Connection=Provider=MSOLAP.3;Cache Authentication=False;User ID="";Initial Catalog=Analysis Services Test Project;Data Source=localhost;Impersonation Level=Impersonate;Location=localhost;Mode=ReadWrite;Protection Level=Pkt Privacy;Auto Synch Period=20000;Default Isolation Mode=0;Default MDX Visual Mode=0;MDX Compatibility=0;MDX Unique Name Style=0;Non Empty Threshold=0;SQLQueryMode=Calculated;Safety Options=1;Secured Cell Value=0;SQL Compatibility=0;Compression Level=0;Real Time Olap=False;Packet Size=4096
CommandText=AS Test Cube

According to the workaround above, this query should use the "new" method to eliminate empty rows since: 1) Non Empty Threshold is set lower than the number of tuples returned. and 2) there are no calculated measures in the query. Meeting both these criteria should clear the way for the "new" method to be used. Does anyone have any other ideas that I could employ to make this pivot table any faster? Any other tools that would do this with nonemptycrossjoin? I need slice/dice capabilities which is preventing my from using Reporting Services.

Thanks,

Keehan

Hi Keehan,

A question I would have, though, is whether the "Non Empty Threshold" connection property works in the same way in AS 2005 (or is just included for backward compatibility). Unfortunately, I can't locate this info in BOL; but Appendix B of the "MDX Solutions" 2nd Edition covers connection parameters. Hopefully, someone from [MS] will be able to authoritatively answer this?

In any case, I assume that the number of rows returned in your scenario easily exceeds the default threshold. If so, is there any possibility of restructuring the dimensions to exploit the AS 2005 Auto Exists feature - such as folding some separate dimensions into hierarchies of a single dimension?

http://www.wiley.com/WileyCDA/WileyTitle/productCd-0471748080.html

>>

Appendix B: Connection Parameters That Affect MDX.

>>

|||

Deepak,

I kind of see where you are going with auto-exists but I'm not sure how it would be implemented. So, basically, for auto-exists to kick in, the attributes would have to be in the same dimension. So, how would I have say product and calendar in the same dimension. Is this the kind of work around you are thinking about?

Keehan

|||

I think I may know what you are talking about now. Let me dig into this a little more and I'll let you know how it goes.

Cheers

Excel

I seriously doubt this is possible but would love to know for sure before starting a new project in VB in order to accomplish. After i fire off a SP(or even written into the SP), is there anyway withing SQL Server to send the results of a specif query into a prenamed Excel file?http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74858&SearchTerms=Excel

Example Project/Package for SQl Server 2005 "Text Extraction"

Does any kind person have a simple example Package for undertaking 'Text Extraction' on one or two columns of text data in a SQL Server table (the data is not in Unicode)?

Check out the sample @. http://aspalliance.com/889
With this example - users will be able to import sample data from a flat file to SQL Server 2005 database.

Let us know if you are looking for any other specific extraction examples.

Thanks,
Loonysan

|||The example provides you provided a useful example of how to set up a package to extract data from an existing flat file. Thank you. Would you per chance have an example on how to set up a package to run on an existing SQL Server table to do a "Text Extraction" on a column of data, including doing an initial conversion to Unicode, as ths appears required?|||

What do you mean by "test extraction"? Are you talking about the TERM EXTRACTION component?

-Jamie

|||

Yes, sorry, "Term Extraction"!

|||

Some Good Pointers for 'Term extraction'

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/datasol.asp (Check out the Example on Extracting Attributes)

http://www.sqlserverdatamining.com/DMCommunity/_Tutorials/688.aspx (Need to register for accessing this site - Registration is Free) - Tutorial on Text Classification using SQL Server 2005.

Thanks,
Loonysan

Event Properties

Hi All,
before you understand my problem see the http://www.eventid.net
the above URL is similar to my problem.
According to my project requirement I will give the EventID as input of
any Event. [Ex : MSSQLSERVER EventID is 17055].
So I have to retrieve all the standard properties of Events as per the
EventID. [Ex : you restart the MSSQLSERVER Service and see the Event
Viewer log file so you will have the Log Entry for MSSQLSERVER double
click on it. so u will find some of the standard properties
of the events like [Date, Time, Source, EventID, Description...etc.].
Sincerely,
RasheedThere is not enough information here to understand what you are trying to
accomplish.
It sounds like you are trying to get help doing some kind of programming
class assignment. I suggest that you read and/or re-read the class
textbooks, paying especial attention to the sections on What is a database
server?, and How to query a database server?
Or if I'm wrong, please supply more detailed information about what is the
problem that you are trying to solve. Include the table DDL, a few rows of
sample data in the form of insert statements, and your attempted DML.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
<sk.rasheedfarhan@.gmail.com> wrote in message
news:1150896677.594310.217000@.m73g2000cwd.googlegroups.com...
> Hi All,
> before you understand my problem see the http://www.eventid.net
> the above URL is similar to my problem.
>
> According to my project requirement I will give the EventID as input of
>
> any Event. [Ex : MSSQLSERVER EventID is 17055].
>
> So I have to retrieve all the standard properties of Events as per the
> EventID. [Ex : you restart the MSSQLSERVER Service and see the Event
> Viewer log file so you will have the Log Entry for MSSQLSERVER double
> click on it. so u will find some of the standard properties
> of the events like [Date, Time, Source, EventID, Description...etc.].
>
> Sincerely,
> Rasheed
>