I have tried a few return options and below is what worked for me:

1) return as a ref cursor

2) read oracle procedure from ssrs just like you read a sql...

Thank you everyone for the feedback.

Thank you Carlo. I also thank Steve Jones for the support.

Martin,

Even string wouldn't work(cannot convert between unicode and nonunicode string error). Staging the data as is and then converting using data conversion would work. You are correct, I should have...

Martin, thank you for the response.

I wish I could do something to prevent something rather than waiting for an error to occur and then fixing it. It becomes tough to...

All,

I have a column in oracle db sit_id which of NUMBER datatype of lenght 22. SSIS reads it as unicodestring of length 38 in the source. I get the...

Thank you Martin! I will follow your suggestion.

@welsh

Sorry for the confusion I created. I meant I used the same credentials to connect to oracle server in the datasource.

When running as a job, I actually encrypted the...

@welsh,

I am actually using the same credentials both in the SQL Server Agent job and BIDS. What kind of added permissions will be needed to run from BIDS. Please advise.

Thank...

@welsh,

I can test the ODBC connection. Actually now, I scheduled the package as a SQL Agent job to run as 32 bit, and it is working fine. I can run...

Welsh Corgi ,

I am testing it from the server.

My connection string:

Provider=OraOLEDB.Oracle.1;Data Source=aaaaa;Persist Security Info=True;Password=***;User ID=uid1

Thanks and Regards,

Praveena.

@steve-2: I just started using regex. I might be able to figure out the complex ones too. Surely will post back in forum if I cant. Thank you...

@steve-2: This article helped a great deal. Though I am not able to search and replace complex ones like the one I was asking earlier, it helps with...

avitale,

With the report as in http://msdn.microsoft.com/en-us/library/cc281302.aspx, nothing appears in the series fields and 'Names' appear in the category area.

If I drag 'Names' to 'series fields area', it...

@opc.three: Thank you! It helped.

I have other question though. I want to read the string between <MDX> and </MDX> tags. How can I do that? I am very new to...