analysis service package question

I'm working on a new analysis package using VS 2005, I am able to create the package and cube, etc, but when I go to the Calculation tab for the cube I'm getting the following error message "Unexpected Error occurred: 'Error in application' "

now I uninstalled and reinstalled VS .NET 05 twice, and even uninstalled and reinstalled SQL Server on my box and I'm still getting the error message.

What is causing this error and how can I fix it?

Have you got Office 2007 installed? See:

http://geekswithblogs.net/darrengosbell/archive/2006/11/17/97367.aspx

HTH,

Chris

|||No i don't, I have office 03 installed|||

Hmm, possibly something else has caused the same problem. I'd still go through the steps described to see if it stops the error if I were you...

Chris

|||

I'm getting ready to go through them now actually.

I'll post how it turns out.

An OLE DB error has occurred - Description: "Class not registered"

Hi

I have a package the connection of which is defined in a config file. When the source is on SQLserver, the package executes fine but when i change the connection to Oracle then i am getting this error.

TITLE: Microsoft Visual Studio

Error at DMND_PROBS_EXTRACT [Connection manager "DLWSDV18.PM"]: An OLE DB error has occurred. Error code: 0x80040154.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".

Error at DMND_PROBS_EXTRACT [DEMANDPROBLEMDETAILS [1]]: The AcquireConnection method call to the connection manager "DLWSDV18.PM" failed with error code 0xC0202009.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

OK

Please let me know if theres any solutions.

Thanks,

Vipul

Do you have the Oracle client tools installed?|||Yes Phil, there was some problem with my oracle install. I re-installed it and it worked.

an interesting bug for SSIS

I have a very simple SSIS package that imports data from ODBC source. There are two "Time" columns in source, but When this table is created in SQL2005, it give me "bigint"!!!

How can this happen?

I tried to import data to SQL2000, those two columns are created as "smalldatetime" properply.

Thanks.

Please provide more details about the package and the ODBCsource. When you say 'Time' does that mean the date is no part it?|||Thanks.
Right. "Time" means date is not part of it.

My package detail:
ODBC source: it is D3 data base. I am using .NET Providers\Odbc Data Provider to connect to the data source.

destination: SQL2005.

Actually, all "Time" field is imported as "bigint"

Cheers.|||I have no idea what a D3 database is; in my case I had to deal with an old PErvasive DB that had a similar case. It had separate columns/data type for the date and the time. I solved it by within the SQL query I use to extract the data. There was a native sql function that allowed me to 'concatenate' the date and time columns into a single column; then the data reader component could see it as a dateTime data type. Conclussion: try to resolve it right on the query.|||

Thanks.

I don't want to "concatenate" the date and time column and that table doesn't has date column at all!

The problem is table created by that package is with wrong data type. But DTS package in SQL 2000 created table properly. This is not about setting the query to select the data.

Can you show me how the query looks like, please?

|||

Qiuwei wrote:

Thanks.
Right. "Time" means date is not part of it.

My package detail:
ODBC source: it is D3 data base. I am using .NET Providers\Odbc Data Provider to connect to the data source.

destination: SQL2005.

Actually, all "Time" field is imported as "bigint"

Cheers.

This is most likely the provider stack. Are you using a different provider to what you used in DTS?

-Jamie

|||In my SQL2000 DTS package, I use "Other(ODBC Data Source)"

Thanks.|||

Which ODBC provider?

It could be that the dotnet provider for ODBC doesn't play very well with whatever ODBC provider you have. You may want to look into getting a different ODBC driver.

-Jamie

|||

Thanks.

Sorry for the late response (toob busy these days).

To get around this problem, we make the source data (time) as varchar type and imported as nvarchar.

Question is that why SQL server doesn't have "Time" data type which is quite useful?

Cheers.

|||

Qiuwei wrote:

Question is that why SQL server doesn't have "Time" data type which is quite useful?

many many people ask the same question. Which is why it (plus some other new things around dates and times) will be coming to a version of SQL Server near you very soon.

-Jamie

|||

That will be great!

Thanks.

Alternative to Identity Help.

I have a SSIS package that imports an Excel file using Visual Basic 2005 into my SQL 2005 database. All the fields are the same in the DB and xls. The package runs with no problem but I need one of the fields to be autoincrement. I tried to set up the properties of one of my fields "ID" to be an Identity. This didn't seem to work at all. There are about 1300 records in the DB so far with the last "ID" number being 10001415. Before now, the numbers were inputed manually. I want the "ID" to be assigned when the SSIS package imports the xls file.

Any help is very appreciated.

See if this helps:
http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/|||

Not really. I opened the SQL Server Management Studio and tried to edit the DTSX package. I couldn't find any of those options you mention in the article. I am not that familiar with SSIS. I just used the wizard to create it.

|||

I think all you have to do is to set a column in the target table to be an Identity (Identity Specification = Yes).

BTW, to edit a ssis package you have to use Business Intelligence Studio.

|||

I previously set my column to Identity = Yes. When I run my DTSX package I get an error. It will not let me change a current column to identity and then import new data into the column. I tried creating a new column whith the Is Identity set to Yes with the appropriate seed and increment, however, I have records that have been deleted and now the numbering is off from the original format. What I mean is I have records numbered 1, 2, 3, 5, 6, 9, 12, etc in the DB in that order. When I create the new column and set the ID, it is unaware that I have records missing and what I end up with is 1, 2, 3, 4, 5, 6, etc.

I also tried creating a new SSIS package in order to use surrogate ID's and cannot figure out how to implement that within my import from Excel.

Please help!

|||

ISSOA wrote:

I previously set my column to Identity = Yes. When I run my DTSX package I get an error. It will not let me change a current column to identity and then import new data into the column.

If you want to insert explicit values in a identity colum you have to alter the table:

SET IDENTITY_INSERT <tableName> ON

|||

I set all that up and I still get an error that I have violated the constraints for the column. The column that I am importing from EXCEL is blank. I want it to get a number when it is imported by the DTSX job. When I put a number in that column within the EXCEL sheet the number stays the same. Any ideas?

|||

ISSOA wrote:

I set all that up and I still get an error that I have violated the constraints for the column. The column that I am importing from EXCEL is blank. I want it to get a number when it is imported by the DTSX job. When I put a number in that column within the EXCEL sheet the number stays the same. Any ideas?

Don't map that column to the destination column. Leave it out of the Import/Export Wizard and you should get the results you desire.

Alternative to Identity Help.

I have a SSIS package that imports an Excel file using Visual Basic 2005 into my SQL 2005 database. All the fields are the same in the DB and xls. The package runs with no problem but I need one of the fields to be autoincrement. I tried to set up the properties of one of my fields "ID" to be an Identity. This didn't seem to work at all. There are about 1300 records in the DB so far with the last "ID" number being 10001415. Before now, the numbers were inputed manually. I want the "ID" to be assigned when the SSIS package imports the xls file.

Any help is very appreciated.

See if this helps:
http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/|||

Not really. I opened the SQL Server Management Studio and tried to edit the DTSX package. I couldn't find any of those options you mention in the article. I am not that familiar with SSIS. I just used the wizard to create it.

|||

I think all you have to do is to set a column in the target table to be an Identity (Identity Specification = Yes).

BTW, to edit a ssis package you have to use Business Intelligence Studio.

|||

I previously set my column to Identity = Yes. When I run my DTSX package I get an error. It will not let me change a current column to identity and then import new data into the column. I tried creating a new column whith the Is Identity set to Yes with the appropriate seed and increment, however, I have records that have been deleted and now the numbering is off from the original format. What I mean is I have records numbered 1, 2, 3, 5, 6, 9, 12, etc in the DB in that order. When I create the new column and set the ID, it is unaware that I have records missing and what I end up with is 1, 2, 3, 4, 5, 6, etc.

I also tried creating a new SSIS package in order to use surrogate ID's and cannot figure out how to implement that within my import from Excel.

Please help!

|||

ISSOA wrote:

I previously set my column to Identity = Yes. When I run my DTSX package I get an error. It will not let me change a current column to identity and then import new data into the column.

If you want to insert explicit values in a identity colum you have to alter the table:

SET IDENTITY_INSERT <tableName> ON

|||

I set all that up and I still get an error that I have violated the constraints for the column. The column that I am importing from EXCEL is blank. I want it to get a number when it is imported by the DTSX job. When I put a number in that column within the EXCEL sheet the number stays the same. Any ideas?

|||

ISSOA wrote:

I set all that up and I still get an error that I have violated the constraints for the column. The column that I am importing from EXCEL is blank. I want it to get a number when it is imported by the DTSX job. When I put a number in that column within the EXCEL sheet the number stays the same. Any ideas?

Don't map that column to the destination column. Leave it out of the Import/Export Wizard and you should get the results you desire.