cannot use datasource column twice in ssas - IBMDB2

We develop ontop of IBMDB2 and cannot use a column twice in the model.

To work arround we have to add for every usage of the column and additional column with new name to an DB2 view and use the new column in the olap model - thats really annoying.

Does someone know a solution to solve this? because ontop of sql server you could use a column N times (for example for: name, key and some other attributes - in with DB2 we have to add 3 columns.) The Error Message we got is "Error in Backend Relational Model - OLEDB Reported unknown Status ' ' for column.".

Our System:

Analysis Services 2005 SP1 ENTERPRISE 64 Bit on Windows 2003 64 Bit connection with the IBMDADB2.1 64Bit OLEDB Provider from the IBM Client Version 8.2.4 to an IBM DB2 Database Version 8.2.4 running on LINUX 64 Bit.

Thanks for your help

HANNES

When do you get the error? Is it at processing time? You can see the query that Analysis Services sends to the Relational system and determine if the query is not formed correctly. This may give you clue to what is wrong.

Are you working around it in the DSV or in an actual DB2 view? An easier workaround would be to use the DSV and add the columns there.

David Botzenhart

|||

we get the error at processing time. see below for the full processing response. If we run the query at db2 directly its correct and working - so it must have something todo with anaylsis services or the oledb provider.

Mostly we work arround with the DB2 view..

This is the server processing state information

Thanks HANNES

====================

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Object>
<DatabaseID>DWH_AT</DatabaseID>
<DimensionID>LU AGMT REQUEST SPEC</DimensionID>
</Object>
<Type>ProcessUpdate</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parallel>
</Batch>
Processing Dimension 'Agreement request profile' failed.
Start time: 30.11.2006 09:44:25; End time: 30.11.2006 09:45:21; Duration: 0:00:56
Processing Dimension Attribute '(All)' completed successfully.
Start time: 30.11.2006 09:44:25; End time: 30.11.2006 09:44:25; Duration: 0:00:00
Processing Dimension Attribute 'Longname' completed successfully. 573 rows have been read.
Start time: 30.11.2006 09:44:25; End time: 30.11.2006 09:45:21; Duration: 0:00:56
SQL queries 1
SELECT
DISTINCT
"PIPE01_LU_AGMT_REQUEST_SPEC"."NAME" AS "IPE01_LU_AGMT_REQUEST_SPEC0_0"
FROM "PIPE01"."LU_AGMT_REQUEST_PROFILE" AS "PIPE01_LU_AGMT_REQUEST_SPEC"
Processing Dimension Attribute 'Product group' completed successfully. 19 rows have been read.
Start time: 30.11.2006 09:44:25; End time: 30.11.2006 09:45:21; Duration: 0:00:56
SQL queries 1
SELECT
DISTINCT
"PIPE01_LU_AGMT_REQUEST_SPEC"."SPARTE" AS "IPE01_LU_AGMT_REQUEST_SPEC0_0"
FROM "PIPE01"."LU_AGMT_REQUEST_PROFILE" AS "PIPE01_LU_AGMT_REQUEST_SPEC"
Processing Dimension Attribute 'Asset category' completed successfully. 6 rows have been read.
Start time: 30.11.2006 09:44:25; End time: 30.11.2006 09:45:21; Duration: 0:00:56
SQL queries 1
SELECT
DISTINCT
"PIPE01_LU_AGMT_REQUEST_SPEC"."CODE_GROUP_VALUE" AS "IPE01_LU_AGMT_REQUEST_SPEC0_0"
FROM "PIPE01"."LU_AGMT_REQUEST_PROFILE" AS "PIPE01_LU_AGMT_REQUEST_SPEC"
Processing Dimension Attribute 'Request reason code' completed successfully. 1698 rows have been read.
Start time: 30.11.2006 09:45:21; End time: 30.11.2006 09:45:21; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
"PIPE01_LU_AGMT_REQUEST_SPEC"."EXTERNAL_REFERENCE" AS "IPE01_LU_AGMT_REQUEST_SPEC0_0","PIPE01_LU_AGMT_REQUEST_SPEC"."EXTERNE_DARSTELLUNG" AS "IPE01_LU_AGMT_REQUEST_SPEC0_1","PIPE01_LU_AGMT_REQUEST_SPEC"."NAME" AS "IPE01_LU_AGMT_REQUEST_SPEC0_2"
FROM "PIPE01"."LU_AGMT_REQUEST_PROFILE" AS "PIPE01_LU_AGMT_REQUEST_SPEC"
Processing Dimension Attribute 'Request reason' failed.
Start time: 30.11.2006 09:45:21; End time: 30.11.2006 09:45:21; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
"PIPE01_LU_AGMT_REQUEST_SPEC"."EXTERNAL_REFERENCE" AS "IPE01_LU_AGMT_REQUEST_SPEC0_0","PIPE01_LU_AGMT_REQUEST_SPEC"."EXTERNE_DARSTELLUNG" AS "IPE01_LU_AGMT_REQUEST_SPEC0_1","PIPE01_LU_AGMT_REQUEST_SPEC"."NAME" AS "IPE01_LU_AGMT_REQUEST_SPEC0_2","PIPE01_LU_AGMT_REQUEST_SPEC"."CODE_GROUP_VALUE" AS "IPE01_LU_AGMT_REQUEST_SPEC0_3","PIPE01_LU_AGMT_REQUEST_SPEC"."SPARTE" AS "IPE01_LU_AGMT_REQUEST_SPEC0_4"
FROM "PIPE01"."LU_AGMT_REQUEST_PROFILE" AS "PIPE01_LU_AGMT_REQUEST_SPEC"
Error Messages 1
Fehler im Back-End-Datenbankzugriffsmodul. OLE DB hat den unbekannten Status '' für die 1-Spalte gemeldet. Fehler im OLAP-Speichermodul: Fehler beim Verarbeiten des Request reason-Attributs der Agreement request profile-Dimension aus der DWH_AT-Datenbank.
Errors and Warnings from Response
Fehler im Back-End-Datenbankzugriffsmodul. OLE DB hat den unbekannten Status '' für die 1-Spalte gemeldet.
Fehler im OLAP-Speichermodul: Fehler beim Verarbeiten des Request reason-Attributs der Agreement request profile-Dimension aus der DWH_AT-Datenbank.
Fehler im OLAP-Speichermodul: Der Verarbeitungsvorgang wurde beendet, da die Anzahl von Fehlern, die w?hrend der Verarbeitung ermittelt wurden, die für den Vorgang maximal zul?ssige Anzahl von Fehlern erreicht hat.

|||

Hi Hannes,

I believe that developers in our area recently tested the DB2 v9.1 client driver (IBM OLE DB Provider for DB2, using IBM DB2 ODBC Driver version 9.01.00356), and it resolved this issue.

|||

We will test later on and I reply then

thanks Hannes

Cannot run report on the report server with ssis as a datasource

Helo All,

I have report which uses an ssis package as a datasource. when I develop the report on the visual studio seems like it runs fine. But when I deployed the same report on to the server (report server 64 bit machine) it is throwing me an error:

An error has occurred during report processing.

Cannot create a connection to data source 'DataSource1'.

is not a valid Win32 application. (Exception from HRESULT: 0x800700C1)

I have both sql sever and reporting server on the same machine and using windows credentials for both the servers(sql and reporting).Then I believe there is no credential issue.

My connection string looks something like this:

="/FILE C:\NTSDW\KPI\KPI_SSIS\NTSKPICCA_DATA_RECON_DELIVERY.dtsx /SET \Package.Variables[User::V_SSRS_PARAM].Properties[Value];" & """" & Parameters!V_SETT_DATE.Value & """"

Could any please help me out of this?

Note: SSIS is parameterized here!

Regards,

Raju

Does it work if you remove the parameter?

Also, enable logging in the package so you can tell if SSRS is actually executing it.

|||

Hi,

I am using ssis logging and found there is a connectivity issue. I am able to connect to the source systems but not able to connect to the target(dwh) system. But I am able to run the package with the same configuration( configurations stored in xml file on the drive ) . I am using windows authentication to connect to the target systems.

The report manager also runs on the windows authentication mode. I am not sure where exctly the issue is. I think there is some authentication(ASP NET user) issue with report manager connecting to the target system.

|||

Yes, it is likely that account that SSRS is running under can't access the database. Can you change to use a SQL Server login to connect to the data source?

|||

Yes, it worked with SQL login, but with direct XML configuration type. It did not work with Indirect configuration using the environment variable. What could be the reason? Any solution for this? This will cause our build/deployment to QA and Prod difficult.

Thanks for your help...

|||Did you restart all the services (SSIS, SSRS, etc)? You have to restart the services for them to pick up new env. variables.|||

I did start the SSRS and SSIS services, the issue is the report server is not able to call the package itself.I found that the package is not invoked as i have enabled the logging. No log file is wirtten.It gives me the same old error "Not a valid win32 application" .

Note: The same package is running fine on all the 32 bit machines , but not on the 64 bit machines.

|||What data source are you using? Does it have a 64-bit driver?|||

Hello All,

The issue got resolved. I was having windows sp2 and sql sevrer sp1 on my server. After installing sp2 sql server the issue got resolved and seems to be running fine.

Thanks to you all.

Raju

Cannot run report on the report server with ssis as a datasource

Helo All,

I have report which uses an ssis package as a datasource. when I develop the report on the visual studio seems like it runs fine. But when I deployed the same report on to the server (report server 64 bit machine) it is throwing me an error:

An error has occurred during report processing.

Cannot create a connection to data source 'DataSource1'.

is not a valid Win32 application. (Exception from HRESULT: 0x800700C1)

I have both sql sever and reporting server on the same machine and using windows credentials for both the servers(sql and reporting).Then I believe there is no credential issue.

My connection string looks something like this:

="/FILE C:\NTSDW\KPI\KPI_SSIS\NTSKPICCA_DATA_RECON_DELIVERY.dtsx /SET \Package.Variables[User::V_SSRS_PARAM].Properties[Value];" & """" & Parameters!V_SETT_DATE.Value & """"

Could any please help me out of this?

Note: SSIS is parameterized here!

Regards,

Raju

Does it work if you remove the parameter?

Also, enable logging in the package so you can tell if SSRS is actually executing it.

|||

Hi,

I am using ssis logging and found there is a connectivity issue. I am able to connect to the source systems but not able to connect to the target(dwh) system. But I am able to run the package with the same configuration( configurations stored in xml file on the drive ) . I am using windows authentication to connect to the target systems.

The report manager also runs on the windows authentication mode. I am not sure where exctly the issue is. I think there is some authentication(ASP NET user) issue with report manager connecting to the target system.

|||

Yes, it is likely that account that SSRS is running under can't access the database. Can you change to use a SQL Server login to connect to the data source?

|||

Yes, it worked with SQL login, but with direct XML configuration type. It did not work with Indirect configuration using the environment variable. What could be the reason? Any solution for this? This will cause our build/deployment to QA and Prod difficult.

Thanks for your help...

|||Did you restart all the services (SSIS, SSRS, etc)? You have to restart the services for them to pick up new env. variables.|||

I did start the SSRS and SSIS services, the issue is the report server is not able to call the package itself.I found that the package is not invoked as i have enabled the logging. No log file is wirtten.It gives me the same old error "Not a valid win32 application" .

Note: The same package is running fine on all the 32 bit machines , but not on the 64 bit machines.

|||What data source are you using? Does it have a 64-bit driver?|||

Hello All,

The issue got resolved. I was having windows sp2 and sql sevrer sp1 on my server. After installing sp2 sql server the issue got resolved and seems to be running fine.

Thanks to you all.

Raju

Cannot run report on the report server with ssis as a datasource

Helo All,

I have report which uses an ssis package as a datasource. when I develop the report on the visual studio seems like it runs fine. But when I deployed the same report on to the server (report server 64 bit machine) it is throwing me an error:

An error has occurred during report processing.

Cannot create a connection to data source 'DataSource1'.

is not a valid Win32 application. (Exception from HRESULT: 0x800700C1)

I have both sql sever and reporting server on the same machine and using windows credentials for both the servers(sql and reporting).Then I believe there is no credential issue.

My connection string looks something like this:

="/FILE C:\NTSDW\KPI\KPI_SSIS\NTSKPICCA_DATA_RECON_DELIVERY.dtsx /SET \Package.Variables[User::V_SSRS_PARAM].Properties[Value];" & """" & Parameters!V_SETT_DATE.Value & """"

Could any please help me out of this?

Note: SSIS is parameterized here!

Regards,

Raju

Does it work if you remove the parameter?

Also, enable logging in the package so you can tell if SSRS is actually executing it.

|||

Hi,

I am using ssis logging and found there is a connectivity issue. I am able to connect to the source systems but not able to connect to the target(dwh) system. But I am able to run the package with the same configuration( configurations stored in xml file on the drive ) . I am using windows authentication to connect to the target systems.

The report manager also runs on the windows authentication mode. I am not sure where exctly the issue is. I think there is some authentication(ASP NET user) issue with report manager connecting to the target system.

|||

Yes, it is likely that account that SSRS is running under can't access the database. Can you change to use a SQL Server login to connect to the data source?

|||

Yes, it worked with SQL login, but with direct XML configuration type. It did not work with Indirect configuration using the environment variable. What could be the reason? Any solution for this? This will cause our build/deployment to QA and Prod difficult.

Thanks for your help...

|||Did you restart all the services (SSIS, SSRS, etc)? You have to restart the services for them to pick up new env. variables.|||

I did start the SSRS and SSIS services, the issue is the report server is not able to call the package itself.I found that the package is not invoked as i have enabled the logging. No log file is wirtten.It gives me the same old error "Not a valid win32 application" .

Note: The same package is running fine on all the 32 bit machines , but not on the 64 bit machines.

|||What data source are you using? Does it have a 64-bit driver?|||

Hello All,

The issue got resolved. I was having windows sp2 and sql sevrer sp1 on my server. After installing sp2 sql server the issue got resolved and seems to be running fine.

Thanks to you all.

Raju

Cannot run report on the report server with ssis as a datasource

Helo All,

I have report which uses an ssis package as a datasource. when I develop the report on the visual studio seems like it runs fine. But when I deployed the same report on to the server (report server 64 bit machine) it is throwing me an error:

An error has occurred during report processing.

Cannot create a connection to data source 'DataSource1'.

is not a valid Win32 application. (Exception from HRESULT: 0x800700C1)

I have both sql sever and reporting server on the same machine and using windows credentials for both the servers(sql and reporting).Then I believe there is no credential issue.

My connection string looks something like this:

="/FILE C:\NTSDW\KPI\KPI_SSIS\NTSKPICCA_DATA_RECON_DELIVERY.dtsx /SET \Package.Variables[User::V_SSRS_PARAM].Properties[Value];" & """" & Parameters!V_SETT_DATE.Value & """"

Could any please help me out of this?

Note: SSIS is parameterized here!

Regards,

Raju

Does it work if you remove the parameter?

Also, enable logging in the package so you can tell if SSRS is actually executing it.

|||

Hi,

I am using ssis logging and found there is a connectivity issue. I am able to connect to the source systems but not able to connect to the target(dwh) system. But I am able to run the package with the same configuration( configurations stored in xml file on the drive ) . I am using windows authentication to connect to the target systems.

The report manager also runs on the windows authentication mode. I am not sure where exctly the issue is. I think there is some authentication(ASP NET user) issue with report manager connecting to the target system.

|||

Yes, it is likely that account that SSRS is running under can't access the database. Can you change to use a SQL Server login to connect to the data source?

|||

Yes, it worked with SQL login, but with direct XML configuration type. It did not work with Indirect configuration using the environment variable. What could be the reason? Any solution for this? This will cause our build/deployment to QA and Prod difficult.

Thanks for your help...

|||Did you restart all the services (SSIS, SSRS, etc)? You have to restart the services for them to pick up new env. variables.|||

I did start the SSRS and SSIS services, the issue is the report server is not able to call the package itself.I found that the package is not invoked as i have enabled the logging. No log file is wirtten.It gives me the same old error "Not a valid win32 application" .

Note: The same package is running fine on all the 32 bit machines , but not on the 64 bit machines.

|||What data source are you using? Does it have a 64-bit driver?|||

Hello All,

The issue got resolved. I was having windows sp2 and sql sevrer sp1 on my server. After installing sp2 sql server the issue got resolved and seems to be running fine.

Thanks to you all.

Raju

Cannot run report on the report server with ssis as a datasource

Helo All,

I have report which uses an ssis package as a datasource. when I develop the report on the visual studio seems like it runs fine. But when I deployed the same report on to the server (report server 64 bit machine) it is throwing me an error:

An error has occurred during report processing.

Cannot create a connection to data source 'DataSource1'.

is not a valid Win32 application. (Exception from HRESULT: 0x800700C1)

I have both sql sever and reporting server on the same machine and using windows credentials for both the servers(sql and reporting).Then I believe there is no credential issue.

My connection string looks something like this:

="/FILE C:\NTSDW\KPI\KPI_SSIS\NTSKPICCA_DATA_RECON_DELIVERY.dtsx /SET \Package.Variables[User::V_SSRS_PARAM].Properties[Value];" & """" & Parameters!V_SETT_DATE.Value & """"

Could any please help me out of this?

Note: SSIS is parameterized here!

Regards,

Raju

Does it work if you remove the parameter?

Also, enable logging in the package so you can tell if SSRS is actually executing it.

|||

Hi,

I am using ssis logging and found there is a connectivity issue. I am able to connect to the source systems but not able to connect to the target(dwh) system. But I am able to run the package with the same configuration( configurations stored in xml file on the drive ) . I am using windows authentication to connect to the target systems.

The report manager also runs on the windows authentication mode. I am not sure where exctly the issue is. I think there is some authentication(ASP NET user) issue with report manager connecting to the target system.

|||

Yes, it is likely that account that SSRS is running under can't access the database. Can you change to use a SQL Server login to connect to the data source?

|||

Yes, it worked with SQL login, but with direct XML configuration type. It did not work with Indirect configuration using the environment variable. What could be the reason? Any solution for this? This will cause our build/deployment to QA and Prod difficult.

Thanks for your help...

|||Did you restart all the services (SSIS, SSRS, etc)? You have to restart the services for them to pick up new env. variables.|||

I did start the SSRS and SSIS services, the issue is the report server is not able to call the package itself.I found that the package is not invoked as i have enabled the logging. No log file is wirtten.It gives me the same old error "Not a valid win32 application" .

Note: The same package is running fine on all the 32 bit machines , but not on the 64 bit machines.

|||What data source are you using? Does it have a 64-bit driver?|||

Hello All,

The issue got resolved. I was having windows sp2 and sql sevrer sp1 on my server. After installing sp2 sql server the issue got resolved and seems to be running fine.

Thanks to you all.

Raju

Cannot run report on the report server with ssis as a datasource

Helo All,

I have report which uses an ssis package as a datasource. when I develop the report on the visual studio seems like it runs fine. But when I deployed the same report on to the server (report server 64 bit machine) it is throwing me an error:

An error has occurred during report processing.

Cannot create a connection to data source 'DataSource1'.

is not a valid Win32 application. (Exception from HRESULT: 0x800700C1)

I have both sql sever and reporting server on the same machine and using windows credentials for both the servers(sql and reporting).Then I believe there is no credential issue.

My connection string looks something like this:

="/FILE C:\NTSDW\KPI\KPI_SSIS\NTSKPICCA_DATA_RECON_DELIVERY.dtsx /SET \Package.Variables[User::V_SSRS_PARAM].Properties[Value];" & """" & Parameters!V_SETT_DATE.Value & """"

Could any please help me out of this?

Note: SSIS is parameterized here!

Regards,

Raju

Does it work if you remove the parameter?

Also, enable logging in the package so you can tell if SSRS is actually executing it.

|||

Hi,

I am using ssis logging and found there is a connectivity issue. I am able to connect to the source systems but not able to connect to the target(dwh) system. But I am able to run the package with the same configuration( configurations stored in xml file on the drive ) . I am using windows authentication to connect to the target systems.

The report manager also runs on the windows authentication mode. I am not sure where exctly the issue is. I think there is some authentication(ASP NET user) issue with report manager connecting to the target system.

|||

Yes, it is likely that account that SSRS is running under can't access the database. Can you change to use a SQL Server login to connect to the data source?

|||

Yes, it worked with SQL login, but with direct XML configuration type. It did not work with Indirect configuration using the environment variable. What could be the reason? Any solution for this? This will cause our build/deployment to QA and Prod difficult.

Thanks for your help...

|||Did you restart all the services (SSIS, SSRS, etc)? You have to restart the services for them to pick up new env. variables.|||

I did start the SSRS and SSIS services, the issue is the report server is not able to call the package itself.I found that the package is not invoked as i have enabled the logging. No log file is wirtten.It gives me the same old error "Not a valid win32 application" .

Note: The same package is running fine on all the 32 bit machines , but not on the 64 bit machines.

|||What data source are you using? Does it have a 64-bit driver?|||

Hello All,

The issue got resolved. I was having windows sp2 and sql sevrer sp1 on my server. After installing sp2 sql server the issue got resolved and seems to be running fine.

Thanks to you all.

Raju