Runtime data source error

Hello

I have a defined data source to an oracle server. I've alredy intalled oracle client, and setup my data source to save the user and password. I'm using .NET provider/OracleClient DataProvider Connection. When I click on "Test Connection" Button, SSIS reports SUCCESS. In Connection Manager TAB I created on connection called "OracleServer" from my oracle data source, described above.

In my package, I defined a DataReaderSource task, I specified "OracleServer" as a connection to it. I can preview data and view oracle's columns name..., so it make me think that everything is fine. But when It execute the task it FAIL and notify logon error and that password can't be blank.

Please help, I need read from the oracle server!!

Thank you.

This may depend on the connection manager, the provider and your ProtectionLevel in the package. Check these.

One of them means you loose the password. If you don't know, between you clicking Execute in the designer and the package running, it is saved, and loaded into a separate execution host. The host then runs the package and passes back the debug messages to BIDS/VS to update the UI with pretty colours for you. So even though the password is there in your current BIDS/VS session, it is probably being lost on the save, prior to being loaded into the execution host. If you close the package in BIDS, and re-open do you still have the password? I would guess not, and this is the issue you see when trying to execute it.

You can play with the various settings, but my favoured approach is to set the package ProtectionLevel to DontSaveSensitive. This means no passwords are ever saved, but you then use a Configuration to set them. Going forward every time the package is loaded, by an execution host or the designer or other tool, it will read the configuration and apply the password or any other configuration you may have set. Problem solved! it may seem like hard work, but if you plan on deploying packages this is really got to be good practise.

|||

Hello,

When I close BIDS and reopen it, I can still see the password. However let me tell you that I already find the solution.

The problem was that my oracle server is 10.1.x and my oracle's drivers installed on SQL Server machine was 10.2.x. Only I unistalled these drivers and install 10.1 version and everything works!.

thanks for your advices.

Hernan.