Error in OLE DB Command task

Hi all,

After applying the SP2 for Korean - we are getting the following error in the above task.

Transfer Fact Data From StagingDB to Presentation DB: Insert Update MetricFact [1021]: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "'OPTIMIZE' ? ? ?.".

Not sure what the problem is. The same task was running properly before applying the SP2.

For other language SP2 (on other languages) the same task runs correctly and without any error.

sachin.dubey wrote:

Hi all,

After applying the SP2 for Korean - we are getting the following error in the above task.

Transfer Fact Data From StagingDB to Presentation DB: Insert Update MetricFact [1021]: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "'OPTIMIZE' ? ? ?.".

Not sure what the problem is. The same task was running properly before applying the SP2.

For other language SP2 (on other languages) the same task runs correctly and without any error.

Hmmm... strange.

Can you run SQL Server Profiler to see what is getting executed against SQL Server?

-Jamie

|||

I m getting the error at design time also when the Package is opened in BI studio

when i tried using Profiler

it executes exec sp_procedure_params_rowset in backend

and throws following error

???102, ??15, ??1, ????sp_procedure_params_rowset, ?151

'OPTIMIZE' ?????????????.

|||

sachin.dubey wrote:

I m getting the error at design time also when the Package is opened in BI studio

when i tried using Profiler

it executes exec sp_procedure_params_rowset in backend

and throws following error

???102, ??15, ??1, ????sp_procedure_params_rowset, ?151

'OPTIMIZE' ?????????????.

It sounds as though the problem might be outside of SSIS. Try and isolate the problem in SQL Server.

-Jamie

|||

Hmm Thanks Jamie.

I will try there.

Error in OLE DB Command task

Hi all,

After applying the SP2 for Korean - we are getting the following error in the above task.

Transfer Fact Data From StagingDB to Presentation DB: Insert Update MetricFact [1021]: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "'OPTIMIZE' ? ? ?.".

Not sure what the problem is. The same task was running properly before applying the SP2.

For other language SP2 (on other languages) the same task runs correctly and without any error.

sachin.dubey wrote:

Hi all,

After applying the SP2 for Korean - we are getting the following error in the above task.

Transfer Fact Data From StagingDB to Presentation DB: Insert Update MetricFact [1021]: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "'OPTIMIZE' ? ? ?.".

Not sure what the problem is. The same task was running properly before applying the SP2.

For other language SP2 (on other languages) the same task runs correctly and without any error.

Hmmm... strange.

Can you run SQL Server Profiler to see what is getting executed against SQL Server?

-Jamie

|||

I m getting the error at design time also when the Package is opened in BI studio

when i tried using Profiler

it executes exec sp_procedure_params_rowset in backend

and throws following error

???102, ??15, ??1, ????sp_procedure_params_rowset, ?151

'OPTIMIZE' ?????????????.

|||

sachin.dubey wrote:

I m getting the error at design time also when the Package is opened in BI studio

when i tried using Profiler

it executes exec sp_procedure_params_rowset in backend

and throws following error

???102, ??15, ??1, ????sp_procedure_params_rowset, ?151

'OPTIMIZE' ?????????????.

It sounds as though the problem might be outside of SSIS. Try and isolate the problem in SQL Server.

-Jamie

|||

Hmm Thanks Jamie.

I will try there.

error handling while processing a cube

hi

in analysis services when i process a cube i got an error that
data from the fact table isnt exists in the dimenstion table
for example : fact table sales has column suplier that one of its row
has data(suplier id = 170) that doesnt match with the dimension table ( in the dimension table there isnt such supier id)
how can i handle this error ?
can i insert instead someyhing else so that the proccess can continue and not failed ?

Thanks

Eyal

Ideally, you've addressed this problem in your data warehouse/data mart. I recommend to my clients they never allow NULLs in their foreign keys. This requires you to place a NULL member in each dimension -- we usually assign that member the surrogate key value of -1 -- and eliminates this issue.

It sounds like in your example, you have a valid key in your partition, so I would suggest you make sure you dimension is processed before you process your cube/partitions.

If that is not an option, you use use the UnknownMember and ErrorConfiguration properties in SSAS to handle this issue. In the dimension that is causing problems (the supplier dimension in your example?) set the UnknownMember property to either visible or hidden. It defaults to None which makes the UnknownMember inaccessible.

Then, on the measure group that points to this dimension table, change the error configuration to replace missing key values with unknown (using the KeyErrorAction and KeyNotFound properties). Be certain to also set the KeyErrorLimitAction property to Stop Logging when the max error is hit.

One important gotcha on this. If your dimension member lands in your dimensions, the facts assigned to the unknown member do not revert to your new dimension value. To line your facts up correctly with the values in your dimension, you will need to do a full reprocess of the partition.

B.