Config Error (Oracle 10g Source)

Hello All

I was trying to configure my package. I have a Oracle 10g DB as source. I used "Oracle provider for OLE Db" as provider in connection manager. I entered user name and password.

When I set my package protection level to : DontSaveSensitive and executed the package

it gives me this error:

[Connection manager "WDEV.wuser1"] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ORA-01005: null password given; logon denied".

When i go back to connection manager I see the blank password. I retype it but again when I execute I get the above error.

I configured the package with same setting and in the config file I mentioned the passwords and all details but again I when I execute the package i get the below error:

[Connection manager "WDEV.wuser1"] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

Since it was not storing password OR not executing without password I set the Protection Level to : "EncryptAllWithPassword"

and excute it I works Great. and runs Awesome. Except I get this Warning

[OLE DB Source [1]] Warning: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

Question 1: "CAN I IGNORE THIS WARNING?'

Question2: "THE PACKAGE RUNS GOOD WHEN I SET THE PROTECTIONLEVEL =EncryptAllWithPassword. With this settings when i configure my package it gives me error. Why is it so Any Help please Below plz find the config file?"

[OLE DB Source [1]] Error: The AcquireConnection method call to the connection manager "WDEV.wuser1" failed with error code 0xC0202009.

[Connection manager "WDEV.wuser1"] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

x<?xml version="1.0" ?>

- <DTSConfiguration>

- <DTSConfigurationHeading>

<DTSConfigurationFileInfo GeneratedBy="AWM" GeneratedFromPackageName="Package1" GeneratedFromPackageID="{289810A-ASD0-4698-BAC5-3270D3E5D17D}" GeneratedDate="9/9/2007 10:18:10 PM" />

</DTSConfigurationHeading>

- <Configuration ConfiguredType="Property" Path="\Package.Connections[WDEV.wuser1].Properties[ConnectionString]" ValueType="String">

<ConfiguredValue>Provider=OraOLEDB.Oracle.1;Data Source=WDEV;User ID=wuser;Password:XYZ;</ConfiguredValue>

</Configuration>

- <Configuration ConfiguredType="Property" Path="\Package.Connections[WDEV.ccuser1].Properties[InitialCatalog]" ValueType="String">

<ConfiguredValue />

</Configuration>

- <Configuration ConfiguredType="Property" Path="\Package.Connections[WDEV.wuser1].Properties[Name]" ValueType="String">

<ConfiguredValue>WDEV.wuser1</ConfiguredValue>

</Configuration>

- <Configuration ConfiguredType="Property" Path="\Package.Connections[WDEV.wuser1].Properties[Password]" ValueType="String">

XYZ<ConfiguredValue />

</Configuration>

- <Configuration ConfiguredType="Property" Path="\Package.Connections[WDEV.wuser1].Properties[ServerName]" ValueType="String">

<ConfiguredValue>WDEV</ConfiguredValue>

</Configuration>

- <Configuration ConfiguredType="Property" Path="\Package.Connections[WDEV.wuser1].Properties[UserName]" ValueType="String">

<ConfiguredValue>wuser</ConfiguredValue>

</Configuration>

- <Configuration ConfiguredType="Property" Path="\Package.Properties[PackagePassword]" ValueType="String">

<ConfiguredValue /> AWM

</Configuration>

</DTSConfiguration>

Thanks a lotsI had a similar problem with a pair of SSIS running with Oracle. I put "Rely on server storage and.."|||I can help with question number 2.

By default SSIS do not stored any sensitive information when the package is saved. That is given by the ProtectionLevel property you find at the package level. You can use a combination of 'package configurations' and/or protection level value to set connection strings and any other dynamic/sensitive data at execution time. This KB article shows 5 methods to get around the issue you are having; I personally used the #4:

http://support.microsoft.com/kb/918760

I cannot help with the #1 as I haven't worked with SSIS and Oracle at all.

Conditional Trigger?

Still new to programming in SQL but need to do the following:

On a table with Provider information need to set a trigger? that will prevent anyone from using our own taxID number. The problem is that six Providers belong to us and for them that taxID is acceptable.

My question is can a constraint be set that applies to every row in the table except my list of six, if not how would I set up a trigger that will fire for INSERT and UPDATE that will exclude the six providerID's?

tblProvider is table
ProviderID is Unique Key
TaxID is field name for the tax id
Application only updates or inserts one row at a time

Thanks,

BrentIf I understand you right, try this... I assumed some information for this example... this constraint will let the TaxID be anything for ProviderIDs between 1 and 6 and restrict it from using TaxID for any other ProviderID

ALTER TABLE tblProvider
ADD CONSTRAINT ck_Provider_TaxID
CHECK (TaxID <> 100 or ProviderID in (1,2,3,4,5,6))

Originally posted by baolive
Still new to programming in SQL but need to do the following:

On a table with Provider information need to set a trigger? that will prevent anyone from using our own taxID number. The problem is that six Providers belong to us and for them that taxID is acceptable.

My question is can a constraint be set that applies to every row in the table except my list of six, if not how would I set up a trigger that will fire for INSERT and UPDATE that will exclude the six providerID's?

tblProvider is table
ProviderID is Unique Key
TaxID is field name for the tax id
Application only updates or inserts one row at a time

Thanks,

Brent|||Thanks for the reponse HueyStLoui.

Tried the code and it seems to be having a problem with the TaxID column. As it verifies against the first entry it gives the following error:

Syntax error converting the varchar value '111-11-1111' to a column of data type int.

(NOTE: the number has been changed as I don't want to post someones SSN).

The TaxID column is a varchar15. Data contained is either in SSN format (NNN_NN_NNNN) or Federal Tax ID (NN-NNNNNNN).

Brent|||Brent,

You will need to change the constraint I posted slightly to match the datatypes of your columns (I just assumed both where integers). If TaxID is a varchar, just put single quotes around the value... (same goes for ProviderID if it is a varchar as well). Note.. you will need to change the ProviderID's listed to be the 6 you mentioned in your original post.

ALTER TABLE tblProvider
ADD CONSTRAINT ck_Provider_TaxID
CHECK (TaxID <> '111-11-1111' or ProviderID in (1,2,3,4,5,6))

Originally posted by baolive
Thanks for the reponse HueyStLoui.

Tried the code and it seems to be having a problem with the TaxID column. As it verifies against the first entry it gives the following error:

Syntax error converting the varchar value '111-11-1111' to a column of data type int.

(NOTE: the number has been changed as I don't want to post someones SSN).

The TaxID column is a varchar15. Data contained is either in SSN format (NNN_NN_NNNN) or Federal Tax ID (NN-NNNNNNN).

Brent|||Thanks,
Looks like that will work for us.