Errors in Connecting to Oracle system from SAP BPC 5.1 Using SSIS Package

[Former Member]

Hi All,

I tested my SSIS Packages, running into connection problems to Oracle database when running SSIS package from SAP BPC 5.1 DM is not resolved.

Following is brief about the error message when running SSIS Package from SAP BPC 5.1 DM.

Errors:

I have created a SSIS Package which connects to Oracle data base and extracts data and loads into BPC 5.1 dimensions. I have done all the connections through connection manager and able to test the connection to Oracle successfully, but when I executed the SSIS Package from Data manager of SAP BPC 5.1 , the SSIS Package gets failed and not able to see any steps in detail log. It display only the error message u2018Hresult: 0x80040E4D Description: u201CORA-01017: invalid username/password; logon deniedu2019.

I have done EVMODIFY dynamic script coding and hard coded following statements.

CONNECTION(ETIME,USERID,%XXXXX%)

CONNECTION(ETIME,PASSWORD,%XXXX%

Basically, there are error messages displayed regarding Oracle OLE DB Connections in detail log, but itu2019s not displaying any SSIS steps executed in detail log and I am not sure whether itu2019s taking the Oracle User ID and Passwords hard coded in EVMODIFY or not.

I appreciate your inputs.

Regards,

Sreekanth.

[Former Member]

Password is not a public property of the OLEDB connection that can be set via ModifyScript. You will have to use an SSIS Package Configuration to set the connection password, or save the password in the package as saving the package as a SQL package instead of a file. Then set the DataManager job to use the sql version instead.

[Former Member]

Hi Sara,

Oracle OLE DB Connections are already created and tested successfully from MS Server, when i tried to run the SSIS package from SAP BPC 5.1 DM, i am error out. What's the issue here is, you need to input manually password in SSIS Package and test it then the connection is successful, but when you try to save the password in SSIS OLEDB Oracle task, i am not able to save it. So there could be a problem with saving password first in SSIS task. Ok then what i can do is i can hard code the source system User id, Password in EVMODIFY so that when executing SSIS Package, the EVMODIFY should execute first and take the Connection passwords from EVMODIFY, but it's not doing that way and it's trying take the password from SSIS task which is not getting saved.

Have you created SSIS Package which connects to Oracle source system and extracts data, if so , please let me know how you are able to succeed to connect to Oracle source system and run it from SAP BPC 5.1 DM successfully.

Thanks for your input.

Regards,

Sreekanth

[Former Member]

Yes I have both by utilizing the SSIS Package configurations (I've successfully used XML and SQL Table configurations) to provide the Oracle connection password at runtime. I've also save the ssis package as a SQL package with the password to the Oracle connection saved internal to it. Then it uses SQL Server access permissions to provide the security to resolve the password. I'm currently attempting to use a script task to dynamically set the connection password at runtime, alas, no sucess there yet.

[Former Member]

Hi Sara,

Can you specify , how you are using XML and SQL Table configurations to provide the Oracle connection password at runtime.

I created XML file and specified password and inserted the file in Oracle Connection Task-->File Name and tried testing the connection but got an error 'Test connection failed because of an error in initializing provider'.

I have not yet configured the SQL Table configurations.

Can you let me know, am i missing some thing here and i appreciate if you can let me know what are the steps that you did in configuration of XML file and coded password and SQL Table configuration.

Regards,

Sreekanth.

[Former Member]

http://technet.microsoft.com/en-us/library/cc895212.aspx

[Former Member]

Hi Sara,

I resolved the password issue and able to get it through XML SSIS configuration.

Do you know, parameters passing using SAP BPC 5.1 EVMODIFY dynamic script. If you need any information can you please pass on to me.

I am currently utilizing SAP BPC 5.1 data manager dynamic script to pass parameters, but currently i am facing an issue with the editor.

If you did the parameter passing through SAP BPC 5.1 data manager and able to succeed, please let me know.

I also posted another post, you may reply for that.

Regards,

Sreekanth.

[Former Member]

Yes, I've successfully passed parameters via the MODIFYSCRIPT variable, both by editing the big string in the Value property of the MODIFYSCRIPT in VisualStudios and using the Dynamic editior accessed thru eData > Organize package list.

There are some other posts in the thread concerning updating parameters via MODIFYSCRIPT. If you use the eData > Organize package option to modify the package, you will see a list a TASK and CONNECTIONs in the package, if the item you are wanting to modify at runtime is in the TASK list you need to use a TASK(taskname,propertyname,value) command. If it's in the CONNECTIONs list, you'll need to use a connection command, CONNECTION(connectionname,propertyname,value), etc.. To see the available properties, you select the task or connection in the Advance editor list and see a list of available properties..that's in another thread in this forum with a little more detail.

I've use existing BPC variables, %APP%, %APPSET%, %USER% and created my own variables using INFO(%my%,value) to build values to pass thru parameters.

I know that sometimes it is easier to start with an existing standard package and modify the value of the MODIFYSCRIPT as opposed to creating a new one. There are sometimes issues with saving using the Dynamic editor. Which seems to be related to some unprintable characters either missing from the value of the MODIFYSCRIPT or they should not be there....Never got a resolution to that.