I am connecting to an Oracle table in a custom SSIS package. I can create the package using SQL Management Studio and run successfully but when I add that function to my package for BPC, I get errors when running. When I try to run the package generated from SQL Management Studio, I get the same errors. BPC is on an APP server and the BPC SQL is on the DB server. The Oracle client is installed on only the APP server. The connections are using OLE.
I have the ProtectionLevel set to EncryptSensitiveWithUserKey and I get "Failed to decrypt protected xml node 'DTS:Password' with error 0x800900B 'Key not valid for use in specified state.' You may not be authorized to access this information. This error occurs when ther is a cryptographic error. Verify that the correct key is avaiable"
When I change the ProtectionLevel to DontSaveSensitive and I get "SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation. Provider is unable to function until these components are installed.".
Can any help?