Order in Parent Package Configurations

Hi all,

I am pretty new to SSIS and i found some, (to me) unexpected behaviour. Maybe you guys can help me out understanding it.

I am currently building a multi package ETL solution, that uses parent-package configurations to "distribute" variable values from the "root" package to the lower level and "leaf" packages. Each package contains some 8 parent-package variables.

When i ran the entire solution (i.e. the whole tree) I found that some packages aren't getting the correct values for some of their variables. It seems that if a parent-package configuration that wasn't configured correctly (misspelled parent variable name in this case) blocked the other parent-package configurations in the same package from evaluating. When i moved the correct parent-package configuration to execute prior to the incorrect configuration, it ran just ok.

So apperently parent-package configurations are evaluated one by one, and if one of them fails, the "later" configurations aren't evaluated any more.

Is this a feature? or a bug?

Why don't i get a warning in the error list? Should i maybe configure my BIDS in a different fashion?

Hope someone can help me out.

Cheers,

Tom Kronenburg

Tom,

I am not aware of that behaivor using parent-package variables. What I know though is that parent package based configurations are allways resolved in the last place no matter how 'high' they are in the package configuration wizard; which may yield unexpected results if any other configuration depends on a parent-package variable one.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=264502

|||

Rafael,

Thanks, but that was not really what i had problems with. I have only parent-package variables, and the order in which they are presented in the "Package configuration organizer" does present some problems.

E.g. My first parent package configuration has a reference to a variable with a different datatype then expected. (i.e. the configuration CustNo expects an int, and gets a string)

The second parent package configuration is correct.

The third parent package configuration expects a variable with a different name (e.g. it expects to get a variable CustNam and the parent only provides CustName

The fourth is correct again.

When running this package, it will fail 1 and 3, evaluate 2 correctly and never evaluate (is that the correct term?) 4.

In the progress tab i will see warnings for the failure of 1 and 3, and a notice that 2 is evaluated correctly, but i will never see any mention of 4.

I guess it's a bug, but it just might be intended to work that way and somewhere the properties of my bids are not set correctly.

Tom

|||

I just reproduced the issue you described. It looks like any parent package variable that comes after an invalid one (when the warning message is: Configuration from a parent variable "xxx" did not occur because there was no parent variable. Error Code: 0xC0010001) is just ignored.

I would suggest to open a bug in the SQL Server connect site http://connect.microsoft.com/SQLServer/Feedback ; if you do so, place a link here so others can validate and vote.

[Microsoft follow-up]

|||

The bug is reported, vote through https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=276366

|||

Tom,

I have unmarked this thread as answered so it can show up in the Microsoft follow up report.

BTW, thanks for openning the bug in the connect site, I have casted my vote

|||

This is a bug and we are aware of it. The bug will be fixed in the next release.

If you need a hotfix for the problem, please contact CSS

|||

Greetings,

This is a known issue; the fix for the bug has been implemented and should be available in an upcoming release of SQL Server.

-David

sql

Oracle Source Connection Error

Hi,

I was trying to connect to Oracle Source in SSIS

1. Err: Test connection failed because of an error in initializing provider. Oracle Client and networking components were not found

Sol: I installeed the Oracle Client tools

2. Err: ORA -12154: TNS: Could not resolve teh connect identifier specified

Sol: Then I point out the TNS file

3. Now I coonect the Oracle db . I go to Connection Manager . Specifies the provide as: Native OLE DB\ Oracle Provide for OLE DB, Select my DB, provide teh user name and password. and check if the connection passed.

Then in my data Flow Task when I add this source and check the Colmns its gives me an error./ sort of warning

The component reported the following warnings:

Warning at {C947B.......} [Ole DB Source[1]]: Cannot retrieve the column code page info from the OLE DB provider. if the components supports teh "DefaultCodePage" property, the code page from that property will be used.....

Will this warning has an significance. i dont see any error in my output data. but is there something I m doing wrong.

4. When I set my ProtectionLevel under Security in package property to "Dont Save Sensitive" in order to easily configure the package I m getting this error: failed Validation. The default one was EncryptSensitiveWithUserKey then its running smoothly.

Can somebody advice me on this?

I addded the all the errors and solution for 1 and 2 becuase in that way this will be helpful in future.

One more thing...

What should be my Package property Protection layer in oreder to smoothly transfer and run the package on other computer.

P.S. I' m using Oracle Source where in I have to give the user name and password in order to connect it.

|||

To use Oracle for development you have to install the correct Oracle client for the version of Oracle you are connecting to and you need a TNS.ORA file with your connection info and permissions in your development folder because your error says your permissions could not be resolved. One more thing if your database is in 9i make sure your client is for 9i and not 10g. Hope this helps.

|||What should be my protection Level in Package in order to make work anywhere on any computer. I am using user name; and password in order to connect to server|||

Microsoft support have covered the protection level and related issues in link one and two covers how to use the Agent to run your packages.

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

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

Oracle query does not work in SSIS

Hello All,

I am trying to run the below query in SSIS, However it does not work, but when I try to run the same query in Oracle client it works fine. Here is the following query:

select 'AAA-'||OWNER AS SOURCE,
table_name,
column_name,
SUBSTR(data_type,1,50) DATA_TYPE ,
SUBSTR(decode(data_type,'NUMBER', DATA_PRECISION, DATA_LENGTH),1,20) DATA_LENGTH
from all_tab_cols
where owner='XXX'
ORDER BY TABLE_NAME, COLUMN_ID

Here ARE the following errorS I get when running from SSIS:

[ORA_AAA_XXX [147]] Error: There was an error with output column "SOURCE" (612) on output "OLE DB Source Output" (157). The column status returned was: "The value could not be converted because of a potential loss of data.".

[ORA_AAA_XXX [147]] Error: The "output column "SOURCE" (612)" failed because error code 0xC0209072 occurred, and the error row disposition on "output column "SOURCE" (612)" specifies failure on error. An error occurred on the specified object of the specified component.

Any help?

Regards,

Raju

I would suggest testing the query in the execute sql task before running the job to try and see if it works there, maybe try another oracle driver and play with the different syntax like || and +.

Larry

|||

Larry,

The problem was with column allias "SOURCE".... looks like it is a keyword in ssis. We just changed the alias to SRC_SCHEMA and it worked. btb we were using the query in execute SQL task.

Thanks

Oracle query does not work in SSIS

Hello All,

I am trying to run the below query in SSIS, However it does not work, but when I try to run the same query in Oracle client it works fine. Here is the following query:

select 'AAA-'||OWNER AS SOURCE,
table_name,
column_name,
SUBSTR(data_type,1,50) DATA_TYPE ,
SUBSTR(decode(data_type,'NUMBER', DATA_PRECISION, DATA_LENGTH),1,20) DATA_LENGTH
from all_tab_cols
where owner='XXX'
ORDER BY TABLE_NAME, COLUMN_ID

Here ARE the following errorS I get when running from SSIS:

[ORA_AAA_XXX [147]] Error: There was an error with output column "SOURCE" (612) on output "OLE DB Source Output" (157). The column status returned was: "The value could not be converted because of a potential loss of data.".

[ORA_AAA_XXX [147]] Error: The "output column "SOURCE" (612)" failed because error code 0xC0209072 occurred, and the error row disposition on "output column "SOURCE" (612)" specifies failure on error. An error occurred on the specified object of the specified component.

Any help?

Regards,

Raju

I would suggest testing the query in the execute sql task before running the job to try and see if it works there, maybe try another oracle driver and play with the different syntax like || and +.

Larry

|||

Larry,

The problem was with column allias "SOURCE".... looks like it is a keyword in ssis. We just changed the alias to SRC_SCHEMA and it worked. btb we were using the query in execute SQL task.

Thanks

Oracle OleDb Provider as Source

Guys,

I am having a nightmarish time getting an Oracle Connection Manager working as a source in my SSIS package.

The CM is called "OLTP_SOURCE". When I inspect the configuration and test connection, it succeeds, however when I go to run the package (both in debug mode and via DTEXECUI) I get the following error:

The AcquireConnection method call to the connection manager "OLTP_SOURCE" failed with error code 0xC0202009

After this happens, if I go into an OLE DB Source within a DFT, I get the following:

No disconnected record set is available for the specified SQL statement.

Now, if I go back into the CM, enter the password and test, it succeeds. From this point, I will go to preview the data in the OLE DB Source, and it comes back fine. However, when I go to run the package, I get the same error time and time again:

The AcquireConnection method call to the connection manager "OLTP_SOURCE" failed with error code 0xC0202009

The quick reader will suggest that the password is not being persisted. To this end, I have tried each of the following techniques to no avail:

1. Double, Triple and Quadruple check that the "save" password option in the CM is checked.

2. Hardcode the connection string in the dtsx XML-behind.

3. Enable Package Configurations and hardcode the connection string in the dstsconfig file.

4. Run the dtsx file using DTEXECUI, providing it with the configuration (that includes the hard-coded password).

5. Run the dtsx file using DTEXECUI, providing it the connection string in the Connection Managager override UI.

Can anyone help shed some light on what might be going on? So far, it is obvious that there has to be something that I am doing wrong because (syntax dialect differences aside) I can't imagine that Oracle sources should be this much of a headache.

Thanks,

Rick

Update:

If I ignore this anamoly and try to step into debug mode, I get the following error within the IDE:

Error at DFT_LoadDimEntities [DTS.Pipeline]: "output "OLE DB Source Output" (11)" contains no output columns. An asynchronous output must contain output columns.

Error at DFT_LoadDimEntities [DTS.Pipeline]: The layout failed validation.

Error at DFT_LoadDimEntities: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

Any suggestions would be greatly appreciated.

Thank you,

Rick

|||

This turned out to be a combination of quirks related to Oracle tooling and syntax.

I have learned that integrating Oracle is a multi-faceted project. The first phase is syntax normalization and dealing with the tooling anamolies that come up. The second phase is getting to true interop, where I can flip flop providers on source and target and have the same package, same code base just work.

I think I am just about done with phase 1 stuff, and here are some things to consider if you run into similar problems (many of these sound rediculously trivial, but when something just works against SQL and stops working against Oracle, it can be madenning):

1. Make sure that you are saving the connection string information during design time. The only way to do this is to select one of the "Encrypt..." options. Whereas SQL source/target Connection Managers seem to hapilly retain connection info (i.e. Windows Auth, makes sense), Oracle credentials in the connection string will not get saved. I have found that this creates a number of un-intuitive error messages.

2. Check your syntax. Fire up Oracle SQL Worksheet and test your code to ensure it is interoperable.

3. Just because your code runs in both SQL and Oracle doesn't preclude strange "tooling" issues as I can them from cropping up. For example, in an OLE DB Source task, the MSDAORA provider does not seem to like comments (-- Blah) as the first line. This one drove me nuts for a good couple of days.

If I think of anything else, I'll post it, but I think that most of these problems have been addressed here and on the following blog posting which may prove helpful to others: http://rickgaribay.net/archive/2007/03/15/font-facearialstrikeadventuresstrikefont-contortions--with-ssis-oracle-interop.aspx

If anyone has specific questions, feel free to post or contact me with questions- I'd be happy to share any knowledge I've gleaned along the way.

Rick

Oracle OleDb Provider as Source

Guys,

I am having a nightmarish time getting an Oracle Connection Manager working as a source in my SSIS package.

The CM is called "OLTP_SOURCE". When I inspect the configuration and test connection, it succeeds, however when I go to run the package (both in debug mode and via DTEXECUI) I get the following error:

The AcquireConnection method call to the connection manager "OLTP_SOURCE" failed with error code 0xC0202009

After this happens, if I go into an OLE DB Source within a DFT, I get the following:

No disconnected record set is available for the specified SQL statement.

Now, if I go back into the CM, enter the password and test, it succeeds. From this point, I will go to preview the data in the OLE DB Source, and it comes back fine. However, when I go to run the package, I get the same error time and time again:

The AcquireConnection method call to the connection manager "OLTP_SOURCE" failed with error code 0xC0202009

The quick reader will suggest that the password is not being persisted. To this end, I have tried each of the following techniques to no avail:

1. Double, Triple and Quadruple check that the "save" password option in the CM is checked.

2. Hardcode the connection string in the dtsx XML-behind.

3. Enable Package Configurations and hardcode the connection string in the dstsconfig file.

4. Run the dtsx file using DTEXECUI, providing it with the configuration (that includes the hard-coded password).

5. Run the dtsx file using DTEXECUI, providing it the connection string in the Connection Managager override UI.

Can anyone help shed some light on what might be going on? So far, it is obvious that there has to be something that I am doing wrong because (syntax dialect differences aside) I can't imagine that Oracle sources should be this much of a headache.

Thanks,

Rick

Update:

If I ignore this anamoly and try to step into debug mode, I get the following error within the IDE:

Error at DFT_LoadDimEntities [DTS.Pipeline]: "output "OLE DB Source Output" (11)" contains no output columns. An asynchronous output must contain output columns.

Error at DFT_LoadDimEntities [DTS.Pipeline]: The layout failed validation.

Error at DFT_LoadDimEntities: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

Any suggestions would be greatly appreciated.

Thank you,

Rick

|||

This turned out to be a combination of quirks related to Oracle tooling and syntax.

I have learned that integrating Oracle is a multi-faceted project. The first phase is syntax normalization and dealing with the tooling anamolies that come up. The second phase is getting to true interop, where I can flip flop providers on source and target and have the same package, same code base just work.

I think I am just about done with phase 1 stuff, and here are some things to consider if you run into similar problems (many of these sound rediculously trivial, but when something just works against SQL and stops working against Oracle, it can be madenning):

1. Make sure that you are saving the connection string information during design time. The only way to do this is to select one of the "Encrypt..." options. Whereas SQL source/target Connection Managers seem to hapilly retain connection info (i.e. Windows Auth, makes sense), Oracle credentials in the connection string will not get saved. I have found that this creates a number of un-intuitive error messages.

2. Check your syntax. Fire up Oracle SQL Worksheet and test your code to ensure it is interoperable.

3. Just because your code runs in both SQL and Oracle doesn't preclude strange "tooling" issues as I can them from cropping up. For example, in an OLE DB Source task, the MSDAORA provider does not seem to like comments (-- Blah) as the first line. This one drove me nuts for a good couple of days.

If I think of anything else, I'll post it, but I think that most of these problems have been addressed here and on the following blog posting which may prove helpful to others: http://rickgaribay.net/archive/2007/03/15/font-facearialstrikeadventuresstrikefont-contortions--with-ssis-oracle-interop.aspx

If anyone has specific questions, feel free to post or contact me with questions- I'd be happy to share any knowledge I've gleaned along the way.

Rick

Oracle OLEDB in SSIS - problem with 64bit

I have a 64bit Windows 2003 Server with SQL 2005 (64bit) installed. I would want to fetch data from an Oracle database that resides on a different server and load it into SQL2005 through SSIS. This is part of migration from SQL2000.

I have tried to run Oracle client 10G (64bit) with no success, since SSIS is a 32bit application.

I uninstalled the 64bit Oracle client and installed the 32bit version and can create an Oracle OLEDB Provider as Connection Manager and preview the data from the Oracle source, but when I run the package it displays an error almost directly and the transfer stops.

--

[OLE DB Source [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "BOFLEV.bofinc" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

--

I have tried to change the path for SSIS from Program Files (x86) to ProgramFilesx86 with no luck.

I have changed the Run64BitRunTime to false.

Is there anyone that has managed to resolve this, I really do not want to use a third party software for this!

SSIS has both 32-bit and 64-bit flavors. The dev environment (BIDS) only uses the 32-bit version, though.

Can you connect to Oracle through the 32-bit driver from any other application (like Access)? If not, it sounds lile the driver installation is corrupt.

|||

Yeah we managed to get this one sorted after much searching.

Be very careful with the version of oracle 10g you are using, if memory serves me right it needs to be 10.2..0.1.8 or higher, it is not sufficient to have only 10g 64 bit, it must be patched also.

We used the microsoft oracle driver for the 32bit development of the packages using BIDS, we used configurations for the connections thus allowing us to change the provider type from msdaora to oraoledb.oracle.1 for when we transfered and scheduled the package on the SSIS 64 bit. So far we have used this scenario for about 80 packages without fail.

Regards

John

Oracle drivers in connection managers

I am not seeing an option to use the OraOLEDB.Oracle driver when defining connections in ssis. This driver shows up in management studio when trying to create linked servers so I don't know why it wouldn't show up here. All the client tools are installed fine and the linked server works. Any ideas?This might be a novice answer, but are you sure the actual OLE driver is installed? I'm no Oracle expert by far, but the client and the actual driver that my Oracle dba gives for sql server installation are on seperate disks.|||Yes, the oledb drivers are installed. One important thing I failed to mention is that this is a 64 bit server. What appears to be happening is that even though we can create a linked server with the 64 bit oracle oledb driver, when you create a package in ssis it only lists the 32 bit native oledb drivers. I confirmed this by installing the 32 bit oracle client stuff as well and now the oracle oledb driver is listed in the drop downs. This seems a little absurd that ssis would only use the 32 bit drivers, can anyone explain this?|||

"The 32-bit SSIS Designer displays only 32-bit providers that are installed on the local computer. If you want to select a specific 64-bit provider to configure a connection manager, you must have the 32-bit version of the provider installed. However, you can still run the package in 64-bit mode in the development environment. Because the 32-bit and 64-bit versions of a provider have the same ID, the runtime selects the appropriate version to use based on the value of the Run64BitRuntime project property, which by default is True."

Found in this article....

http://msdn2.microsoft.com/en-us/library(d=robot)/ms141766.aspx

|||

Thanks for the info, that's a big help.

I understand why they did this but just like every other weird x64 quirk you have to spend hours digging through various documentation sources to even find a mention of it. Please just give us a comprehensive x64 guide instead of spreading stuff out in a thousand different places.

Oracle Connection String Stored in SQL Server Configuration Not Working

I'm trying to store connection information in a SQL Server SSIS Configuration. I can see the information in the SSISConfigurations table and it appears to be reading the data as the package loads in the designer, but I'm getting connection failure messages

(The AcquireConnection method call to the connection manager "APPLPROD" failed with error code 0xC0202009. MTL_SYSTEM_ITEMS.dtsx 0 0).

Do I have to do anything in the package other than set up the initial configuration? Any clues as to why my connection isn't picking up the configuration information?
Thanks!

Mark

Hi Mark,

for security reasons, we stripe out the password from a connection string. You need to go and change the configuration to include the password.

HTH,
Ovidiu Burlacu

|||Ovidiu,

in the case of OLEDB provider for Oracle I made the experience that providing the password
in a package configuration does not work. With a ADO.Net Provider this works fine.

This is of course a disappointing limitation but I hope that this will be solved in some of the future
releases...

Fridtjof|||

This has been our experience as well; we are able to get the Oracle connection properly set from an XML configuration, and we have set all the properties in the SQL Server configuration, but the SQL configuration just does not work.

We have multiple packages in our projects, and each package pointed to the same configuration file, and when we deployed we were getting an error about the file already existing (apparently another known bug) - hence the attempt to switch to a SQL Server configuration. The ADO.Net Provider is an option, but we're using dynamic SQL so we set the Data Access Mode in the connection to "SQL Command from Variable", and I can't seem to be able to set this property using a Data Reader source. So right now we're kind of stuck. Any suggestions?

Thanks,

Mark

|||

Here are a couple examples of how I am doing this:

Oracle adonet

<Configuration ConfiguredType="Property" Path="\Package.Connections[one.adonet].Properties[ConnectionString]" ValueType="String">

<ConfiguredValue>Data Source=dwprod;User ID=appacnt;Password=notsecret;Persist Security Info=True;</ConfiguredValue>

</Configuration>

</DTSConfiguration>

-Oracle OLEDB

<?xml version="1.0"?>

<DTSConfiguration>

<Configuration ConfiguredType="Property" Path="\Package.Connections[one.oledb].Properties[ConnectionString]" ValueType="String">

<ConfiguredValue>Data Source=dwprod;User ID=appacnt;Password=notsecret;Provider=MSDAORA.1;Persist Security Info=True;</ConfiguredValue>

</Configuration>

</DTSConfiguration>

--SQL Server oledb

<?xml version="1.0"?>

<DTSConfiguration>

<Configuration ConfiguredType="Property" Path="\Package.Connections[datamart.SM.oledb].Properties[ConnectionString]" ValueType="String">

<ConfiguredValue>Data Source=datamart;User ID=ssis agent;Password=Guessit2;Initial Catalog=SM;Provider=SQLOLEDB.1;Persist Security Info=True;</ConfiguredValue>

</Configuration>

</DTSConfiguration>

|||Mark,

since I've been using dynamic SQL for Oracle and package configuration I don't see
any alternative to using OLEDB. I don't store the passwords in the pkg config but
as long as pwds don't change when deploying packages to other machinesthis
means no problem.

Nevertheless I don't give up hoping, that in SP1 storing pwds in Package configs for
OLEDB will work *dreaming*

Fridtjof|||

Friedel wrote:

Mark,
since I've been using dynamic SQL for Oracle and package configuration I don't see
any alternative to using OLEDB. I don't store the passwords in the pkg config but
as long as pwds don't change when deploying packages to other machinesthis
means no problem.
Nevertheless I don't give up hoping, that in SP1 storing pwds in Package configs for
OLEDB will work *dreaming*
Fridtjof

It DOES work and Phillipe has shown you examples of it working. I've got exactly the same thing set up for a number of Oracle sources and they all work perfectly well. If something isn't working it isn't the configuration - maybe this is masking something else.

-Jamie

|||Jamie,

I was talking about OLDEB and *NOT* about ADO.NET connector what phillipe has shown.
I agree with you that it works with ADO.NET but not with MS OLEDB provider for Oracle!

Fridtjof|||

Friedel wrote:

Jamie,
I was talking about OLDEB and *NOT* about ADO.NET connector what phillipe has shown.
I agree with you that it works with ADO.NET but not with MS OLEDB provider for Oracle!
Fridtjof

Ah, OK. My apologies, I thought Phillipe was talking about OLE DB. But, what I said is still true. I have this working using an OLE DB Provider with no problems at all.

-Jamie

|||Oh, you're right. I've done some tests with OLEDB for Oracle again and the results are positive.
I don't know what went wrong when I tested this some time ago...

Fridtjof

Oracle Connection String Stored in SQL Server Configuration Not Working

I'm trying to store connection information in a SQL Server SSIS Configuration. I can see the information in the SSISConfigurations table and it appears to be reading the data as the package loads in the designer, but I'm getting connection failure messages

(The AcquireConnection method call to the connection manager "APPLPROD" failed with error code 0xC0202009. MTL_SYSTEM_ITEMS.dtsx 0 0).

Do I have to do anything in the package other than set up the initial configuration? Any clues as to why my connection isn't picking up the configuration information?
Thanks!

Mark

Hi Mark,

for security reasons, we stripe out the password from a connection string. You need to go and change the configuration to include the password.

HTH,
Ovidiu Burlacu

|||Ovidiu,

in the case of OLEDB provider for Oracle I made the experience that providing the password
in a package configuration does not work. With a ADO.Net Provider this works fine.

This is of course a disappointing limitation but I hope that this will be solved in some of the future
releases...

Fridtjof|||

This has been our experience as well; we are able to get the Oracle connection properly set from an XML configuration, and we have set all the properties in the SQL Server configuration, but the SQL configuration just does not work.

We have multiple packages in our projects, and each package pointed to the same configuration file, and when we deployed we were getting an error about the file already existing (apparently another known bug) - hence the attempt to switch to a SQL Server configuration. The ADO.Net Provider is an option, but we're using dynamic SQL so we set the Data Access Mode in the connection to "SQL Command from Variable", and I can't seem to be able to set this property using a Data Reader source. So right now we're kind of stuck. Any suggestions?

Thanks,

Mark

|||

Here are a couple examples of how I am doing this:

Oracle adonet

<Configuration ConfiguredType="Property" Path="\Package.Connections[one.adonet].Properties[ConnectionString]" ValueType="String">

<ConfiguredValue>Data Source=dwprod;User ID=appacnt;Password=notsecret;Persist Security Info=True;</ConfiguredValue>

</Configuration>

</DTSConfiguration>

-Oracle OLEDB

<?xml version="1.0"?>

<DTSConfiguration>

<Configuration ConfiguredType="Property" Path="\Package.Connections[one.oledb].Properties[ConnectionString]" ValueType="String">

<ConfiguredValue>Data Source=dwprod;User ID=appacnt;Password=notsecret;Provider=MSDAORA.1;Persist Security Info=True;</ConfiguredValue>

</Configuration>

</DTSConfiguration>

--SQL Server oledb

<?xml version="1.0"?>

<DTSConfiguration>

<Configuration ConfiguredType="Property" Path="\Package.Connections[datamart.SM.oledb].Properties[ConnectionString]" ValueType="String">

<ConfiguredValue>Data Source=datamart;User ID=ssis agent;Password=Guessit2;Initial Catalog=SM;Provider=SQLOLEDB.1;Persist Security Info=True;</ConfiguredValue>

</Configuration>

</DTSConfiguration>

|||Mark,

since I've been using dynamic SQL for Oracle and package configuration I don't see
any alternative to using OLEDB. I don't store the passwords in the pkg config but
as long as pwds don't change when deploying packages to other machinesthis
means no problem.

Nevertheless I don't give up hoping, that in SP1 storing pwds in Package configs for
OLEDB will work *dreaming*

Fridtjof
|||

Friedel wrote:

Mark,
since I've been using dynamic SQL for Oracle and package configuration I don't see
any alternative to using OLEDB. I don't store the passwords in the pkg config but
as long as pwds don't change when deploying packages to other machinesthis
means no problem.
Nevertheless I don't give up hoping, that in SP1 storing pwds in Package configs for
OLEDB will work *dreaming*
Fridtjof

It DOES work and Phillipe has shown you examples of it working. I've got exactly the same thing set up for a number of Oracle sources and they all work perfectly well. If something isn't working it isn't the configuration - maybe this is masking something else.

-Jamie

|||Jamie,

I was talking about OLDEB and *NOT* about ADO.NET connector what phillipe has shown.
I agree with you that it works with ADO.NET but not with MS OLEDB provider for Oracle!

Fridtjof
|||

Friedel wrote:

Jamie,
I was talking about OLDEB and *NOT* about ADO.NET connector what phillipe has shown.
I agree with you that it works with ADO.NET but not with MS OLEDB provider for Oracle!
Fridtjof

Ah, OK. My apologies, I thought Phillipe was talking about OLE DB. But, what I said is still true. I have this working using an OLE DB Provider with no problems at all.

-Jamie

|||Oh, you're right. I've done some tests with OLEDB for Oracle again and the results are positive.
I don't know what went wrong when I tested this some time ago...

Fridtjof

Oracle connection information in SSIS package.

Hi,

I want to make a SSIS package with Oracle and deploy it in no of oracle databases, for it every time I have to open package and change connection information.

How can I make oracle connection information as variable value so that when I deploy my package on Oracle database it will pick all oracle connection information(User Id, Pwd, Server Name) automatically.

Please let me know about this.

Thanks

Hi Anurag,

Experts / MVPs have already addressed this issues about dynamically assigning the Connection details. Follow the steps below

1) Create a table in one of your Oracle database with following fields:

ConnectionDetails(UserID, PWD, ServerName)

2) Insert details of all servers you want to deploy

3) Open the New SSIS Package, Drag and drop an execute sql task, write the query (Select * from ConnectionDetails) to retrieve Conectiondetails into a ResultSet Variable say User::ResultSet of an Object type.

4) Drag and drop the For Each Loop container after the Execute SQL Task and configure details to retrieve each row

5) Store output column values in Package Variables say "v_UserID", "v_passwd" , "v_serverName"

6) Drag and Drop a DataFlow Task into the ForEach Loop Container, determine the source, destination and transformation mappings required.

7) Now assign these variables to connection manager whose detail should change dynamically via expression builder Say

ServerName = @.[User::v_serverName]

UserName=@.[User::v_UserID]

Thanks

Subhash Subramanyam

|||

Hi Subash,

Slew of thanks for speedy reply.

I need a little bit change in first 3 steps, instead of making a table in oracle database, i want to keep oracle server and service name information in Text file and reading from this file.

Thanks

|||

If you want to read from a file, Instead of first two steps you can place a script task that can read the data from the text file to populate a resultset variable which you can use in for each loop.

Oracle connection fail with Microsoft OLEDB provider for Oracle MSDAORA.1

Hello,

On my dev server I have working ssis packages that use connections Microsoft OLEDB provider for Oracle MSDAORA.1 and Oracle provider for oledb and OracleClient data provider.

I use one or the other according to my needs.

In anticipation and to prepare for the build of a new production server, I have build a test server from scratch and deployed to it the entire dev.

Almost everything works except Microsoft OLEDB provider for Oracle.

ssis packages on the test machine will return an error

Error at Pull Calendar from One [OLE DB Source [1]]: The AcquireConnection method call to the connection manager "one.oledb" failed with error code 0xC0202009.

Error at Pull Calendar from One [DTS.Pipeline]: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.

[Connection manager "one.oledb"]: An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle error occurred, but error message could not be retrieved from Oracle.".

I have used the same installers for OS, SQL and Oracle SQL*Net on both dev and test machines. The install and then the restore/deployment on Test went fine.

Does anyone could point me to the right direction to solve this issue?

Thanks,

Philippe

Are you running it on a 64 bit system? Or are you using BLOBs?

If answer to either of them is yes, then you have a problem. Are you using Oracle as a source or destination?

|||

Hi,

I do not use BLOBs nor 64 bits. I have a 32 Bits DELL Precision worksation that I use as test server with Windows 2003 Enterprise SP1 all patches and the RTM release of SQL 2005 dev. For Oracle Client, I use the 9.i version.

I use Oracle only to read data from using select statements, I do not use linked servers.

All other Oracle providers works on that machine but the Microsoft one.

All databases were restored from the dev backup

Before rebuilding this machine, this provider was working.

After disk format and OS install, I did rebuild the machine in this order:

1) Oracle Client

2) SQL 2005

3) VS Studio 2005

4) Restore of DB's

5) Restore of sys variables and xml configurations as well as surface config

6) Restore of logins and access settings

7) VSS 2005 RTM, I take the SSIS package from there, I know it works on the other server.

Thanks,

Philippe

|||Hi,

I use "Microsoft OLE DB Provider for Oracle" and it works fine.
I remember having problems with the TNS-Entry in the connection manager
that didn't match with the TNSNAMES.ORA

Did you try the "Test connection" button in the connection config?
Do you save the pwd in the connection?
What is your package protection level? When you select don't save sensitive the pwd isn't saved.|||

Hi,

I did copy over the TNSNAME.ORA that I use in the DEV Server.

I made sure that the default domain is the same than it is in Dev in the Oracle configuration assistant and can test the connection successfully.

I do not save the sensitive in my packages, i use config files to store the connection string including the password.

This should not be relevant to this problem anyway because, if I create a new connection in the package and supply the password and then test the connection, it fails right there so the problem is at the data provider level.

Thanks,

Philippe

|||

Try changing your package to not use configuration files and instead embed the information into it.

Then try and see if it magically works.

I've been finding that using configuration files seems a bit buggy. When they run on the server it seems like sometimes SSIS can't load them causing the package to fail.

In my most recent case, I am getting the exact same error as you above and once i removed the configuration file it is now working seeming to indicate a problem loading the configuration. Since SSIS doesn't log any info that i can find about what is happening, it is virtually impossible to tell what is going on so I'm having to go with just hardcoding for now. Can't spend more time on figuring it out.

|||

This issue is very disturbing.

Yesterday, I connected again to this test server and all by magic, the Microsoft OLEDB provider was working again. I was able to create a new ssis, a new MS OLEDB For Oracle and it worked.

I then tried to run one of these packages that uses config files (and that run perfectly on the dev machine).

Same error than before.

Then, I went back to VS Studio, creatyed a new package, created a new MS OLEDB For Oracle, and it did not work. Nor did work the new package created just before trying the other older package...

I am pulling my hair on that one. The reason why I use config files is to avoid 2 major issues:
1) Package creator Password expiration every 90 days that kills all the encrypted content
2) Difficulties to quickly deploy packages from machine to machine

I will get a new production server real soon. I will then know if this problem is a reproductible bug or if it is specific to my newly rebuild test machine. I am not too optimistic...

Regards,

Philippe

|||I'm seeing this same problem as well. As soon as a configuration file is create the error starts. Even when modifying the .dtsconfig file to add the username & password then using dtexec to launch the package with the .dtsconfig file the error still occurs.|||

Could this be the problem?

http://blogs.conchango.com/jamiethomson/archive/2006/09/19/OLE-DB_3A00_-Cannot-connect-using-Microsoft-OLE-DB-Provider-for-Oracle.aspx

-Jamie

|||What helped me, too, was setting the system variable ORACLE_HOME to the oracle home directory.|||

hi,

This article was good.

Oracle connection is used to get Data in my case.

I get this error becasue package protection level was don't save sensitive the pwd.

So at execution time it says null password.

although it is not good practice to use protection level EncryptSensitiveWithPassword. but it works for my case.

Oracle connection fail with Microsoft OLEDB provider for Oracle MSDAORA.1

Hello,

On my dev server I have working ssis packages that use connections Microsoft OLEDB provider for Oracle MSDAORA.1 and Oracle provider for oledb and OracleClient data provider.

I use one or the other according to my needs.

In anticipation and to prepare for the build of a new production server, I have build a test server from scratch and deployed to it the entire dev.

Almost everything works except Microsoft OLEDB provider for Oracle.

ssis packages on the test machine will return an error

Error at Pull Calendar from One [OLE DB Source [1]]: The AcquireConnection method call to the connection manager "one.oledb" failed with error code 0xC0202009.

Error at Pull Calendar from One [DTS.Pipeline]: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.

[Connection manager "one.oledb"]: An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle error occurred, but error message could not be retrieved from Oracle.".

I have used the same installers for OS, SQL and Oracle SQL*Net on both dev and test machines. The install and then the restore/deployment on Test went fine.

Does anyone could point me to the right direction to solve this issue?

Thanks,

Philippe

Are you running it on a 64 bit system? Or are you using BLOBs?

If answer to either of them is yes, then you have a problem. Are you using Oracle as a source or destination?

|||

Hi,

I do not use BLOBs nor 64 bits. I have a 32 Bits DELL Precision worksation that I use as test server with Windows 2003 Enterprise SP1 all patches and the RTM release of SQL 2005 dev. For Oracle Client, I use the 9.i version.

I use Oracle only to read data from using select statements, I do not use linked servers.

All other Oracle providers works on that machine but the Microsoft one.

All databases were restored from the dev backup

Before rebuilding this machine, this provider was working.

After disk format and OS install, I did rebuild the machine in this order:

1) Oracle Client

2) SQL 2005

3) VS Studio 2005

4) Restore of DB's

5) Restore of sys variables and xml configurations as well as surface config

6) Restore of logins and access settings

7) VSS 2005 RTM, I take the SSIS package from there, I know it works on the other server.

Thanks,

Philippe

|||Hi,

I use "Microsoft OLE DB Provider for Oracle" and it works fine.
I remember having problems with the TNS-Entry in the connection manager
that didn't match with the TNSNAMES.ORA

Did you try the "Test connection" button in the connection config?
Do you save the pwd in the connection?
What is your package protection level? When you select don't save sensitive the pwd isn't saved.
|||

Hi,

I did copy over the TNSNAME.ORA that I use in the DEV Server.

I made sure that the default domain is the same than it is in Dev in the Oracle configuration assistant and can test the connection successfully.

I do not save the sensitive in my packages, i use config files to store the connection string including the password.

This should not be relevant to this problem anyway because, if I create a new connection in the package and supply the password and then test the connection, it fails right there so the problem is at the data provider level.

Thanks,

Philippe

|||

Try changing your package to not use configuration files and instead embed the information into it.

Then try and see if it magically works.

I've been finding that using configuration files seems a bit buggy. When they run on the server it seems like sometimes SSIS can't load them causing the package to fail.

In my most recent case, I am getting the exact same error as you above and once i removed the configuration file it is now working seeming to indicate a problem loading the configuration. Since SSIS doesn't log any info that i can find about what is happening, it is virtually impossible to tell what is going on so I'm having to go with just hardcoding for now. Can't spend more time on figuring it out.

|||

This issue is very disturbing.

Yesterday, I connected again to this test server and all by magic, the Microsoft OLEDB provider was working again. I was able to create a new ssis, a new MS OLEDB For Oracle and it worked.

I then tried to run one of these packages that uses config files (and that run perfectly on the dev machine).

Same error than before.

Then, I went back to VS Studio, creatyed a new package, created a new MS OLEDB For Oracle, and it did not work. Nor did work the new package created just before trying the other older package...

I am pulling my hair on that one. The reason why I use config files is to avoid 2 major issues:
1) Package creator Password expiration every 90 days that kills all the encrypted content
2) Difficulties to quickly deploy packages from machine to machine

I will get a new production server real soon. I will then know if this problem is a reproductible bug or if it is specific to my newly rebuild test machine. I am not too optimistic...

Regards,

Philippe

|||I'm seeing this same problem as well. As soon as a configuration file is create the error starts. Even when modifying the .dtsconfig file to add the username & password then using dtexec to launch the package with the .dtsconfig file the error still occurs.|||

Could this be the problem?

http://blogs.conchango.com/jamiethomson/archive/2006/09/19/OLE-DB_3A00_-Cannot-connect-using-Microsoft-OLE-DB-Provider-for-Oracle.aspx

-Jamie

|||What helped me, too, was setting the system variable ORACLE_HOME to the oracle home directory.|||

hi,

This article was good.

Oracle connection is used to get Data in my case.

I get this error becasue package protection level was don't save sensitive the pwd.

So at execution time it says null password.

although it is not good practice to use protection level EncryptSensitiveWithPassword. but it works for my case.

Oracle connection fail with Microsoft OLEDB provider for Oracle MSDAORA.1

Hello,

On my dev server I have working ssis packages that use connections Microsoft OLEDB provider for Oracle MSDAORA.1 and Oracle provider for oledb and OracleClient data provider.

I use one or the other according to my needs.

In anticipation and to prepare for the build of a new production server, I have build a test server from scratch and deployed to it the entire dev.

Almost everything works except Microsoft OLEDB provider for Oracle.

ssis packages on the test machine will return an error

Error at Pull Calendar from One [OLE DB Source [1]]: The AcquireConnection method call to the connection manager "one.oledb" failed with error code 0xC0202009.

Error at Pull Calendar from One [DTS.Pipeline]: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.

[Connection manager "one.oledb"]: An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle error occurred, but error message could not be retrieved from Oracle.".

I have used the same installers for OS, SQL and Oracle SQL*Net on both dev and test machines. The install and then the restore/deployment on Test went fine.

Does anyone could point me to the right direction to solve this issue?

Thanks,

Philippe

Are you running it on a 64 bit system? Or are you using BLOBs?

If answer to either of them is yes, then you have a problem. Are you using Oracle as a source or destination?

|||

Hi,

I do not use BLOBs nor 64 bits. I have a 32 Bits DELL Precision worksation that I use as test server with Windows 2003 Enterprise SP1 all patches and the RTM release of SQL 2005 dev. For Oracle Client, I use the 9.i version.

I use Oracle only to read data from using select statements, I do not use linked servers.

All other Oracle providers works on that machine but the Microsoft one.

All databases were restored from the dev backup

Before rebuilding this machine, this provider was working.

After disk format and OS install, I did rebuild the machine in this order:

1) Oracle Client

2) SQL 2005

3) VS Studio 2005

4) Restore of DB's

5) Restore of sys variables and xml configurations as well as surface config

6) Restore of logins and access settings

7) VSS 2005 RTM, I take the SSIS package from there, I know it works on the other server.

Thanks,

Philippe

|||Hi,

I use "Microsoft OLE DB Provider for Oracle" and it works fine.
I remember having problems with the TNS-Entry in the connection manager
that didn't match with the TNSNAMES.ORA

Did you try the "Test connection" button in the connection config?
Do you save the pwd in the connection?
What is your package protection level? When you select don't save sensitive the pwd isn't saved.
|||

Hi,

I did copy over the TNSNAME.ORA that I use in the DEV Server.

I made sure that the default domain is the same than it is in Dev in the Oracle configuration assistant and can test the connection successfully.

I do not save the sensitive in my packages, i use config files to store the connection string including the password.

This should not be relevant to this problem anyway because, if I create a new connection in the package and supply the password and then test the connection, it fails right there so the problem is at the data provider level.

Thanks,

Philippe

|||

Try changing your package to not use configuration files and instead embed the information into it.

Then try and see if it magically works.

I've been finding that using configuration files seems a bit buggy. When they run on the server it seems like sometimes SSIS can't load them causing the package to fail.

In my most recent case, I am getting the exact same error as you above and once i removed the configuration file it is now working seeming to indicate a problem loading the configuration. Since SSIS doesn't log any info that i can find about what is happening, it is virtually impossible to tell what is going on so I'm having to go with just hardcoding for now. Can't spend more time on figuring it out.

|||

This issue is very disturbing.

Yesterday, I connected again to this test server and all by magic, the Microsoft OLEDB provider was working again. I was able to create a new ssis, a new MS OLEDB For Oracle and it worked.

I then tried to run one of these packages that uses config files (and that run perfectly on the dev machine).

Same error than before.

Then, I went back to VS Studio, creatyed a new package, created a new MS OLEDB For Oracle, and it did not work. Nor did work the new package created just before trying the other older package...

I am pulling my hair on that one. The reason why I use config files is to avoid 2 major issues:
1) Package creator Password expiration every 90 days that kills all the encrypted content
2) Difficulties to quickly deploy packages from machine to machine

I will get a new production server real soon. I will then know if this problem is a reproductible bug or if it is specific to my newly rebuild test machine. I am not too optimistic...

Regards,

Philippe

|||I'm seeing this same problem as well. As soon as a configuration file is create the error starts. Even when modifying the .dtsconfig file to add the username & password then using dtexec to launch the package with the .dtsconfig file the error still occurs.|||

Could this be the problem?

http://blogs.conchango.com/jamiethomson/archive/2006/09/19/OLE-DB_3A00_-Cannot-connect-using-Microsoft-OLE-DB-Provider-for-Oracle.aspx

-Jamie

|||What helped me, too, was setting the system variable ORACLE_HOME to the oracle home directory.|||

hi,

This article was good.

Oracle connection is used to get Data in my case.

I get this error becasue package protection level was don't save sensitive the pwd.

So at execution time it says null password.

although it is not good practice to use protection level EncryptSensitiveWithPassword. but it works for my case.

Oracle connection fail with Microsoft OLEDB provider for Oracle MSDAORA.1

Hello,

On my dev server I have working ssis packages that use connections Microsoft OLEDB provider for Oracle MSDAORA.1 and Oracle provider for oledb and OracleClient data provider.

I use one or the other according to my needs.

In anticipation and to prepare for the build of a new production server, I have build a test server from scratch and deployed to it the entire dev.

Almost everything works except Microsoft OLEDB provider for Oracle.

ssis packages on the test machine will return an error

Error at Pull Calendar from One [OLE DB Source [1]]: The AcquireConnection method call to the connection manager "one.oledb" failed with error code 0xC0202009.

Error at Pull Calendar from One [DTS.Pipeline]: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.

[Connection manager "one.oledb"]: An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle error occurred, but error message could not be retrieved from Oracle.".

I have used the same installers for OS, SQL and Oracle SQL*Net on both dev and test machines. The install and then the restore/deployment on Test went fine.

Does anyone could point me to the right direction to solve this issue?

Thanks,

Philippe

Are you running it on a 64 bit system? Or are you using BLOBs?

If answer to either of them is yes, then you have a problem. Are you using Oracle as a source or destination?

|||

Hi,

I do not use BLOBs nor 64 bits. I have a 32 Bits DELL Precision worksation that I use as test server with Windows 2003 Enterprise SP1 all patches and the RTM release of SQL 2005 dev. For Oracle Client, I use the 9.i version.

I use Oracle only to read data from using select statements, I do not use linked servers.

All other Oracle providers works on that machine but the Microsoft one.

All databases were restored from the dev backup

Before rebuilding this machine, this provider was working.

After disk format and OS install, I did rebuild the machine in this order:

1) Oracle Client

2) SQL 2005

3) VS Studio 2005

4) Restore of DB's

5) Restore of sys variables and xml configurations as well as surface config

6) Restore of logins and access settings

7) VSS 2005 RTM, I take the SSIS package from there, I know it works on the other server.

Thanks,

Philippe

|||Hi,

I use "Microsoft OLE DB Provider for Oracle" and it works fine.
I remember having problems with the TNS-Entry in the connection manager
that didn't match with the TNSNAMES.ORA

Did you try the "Test connection" button in the connection config?
Do you save the pwd in the connection?
What is your package protection level? When you select don't save sensitive the pwd isn't saved.
|||

Hi,

I did copy over the TNSNAME.ORA that I use in the DEV Server.

I made sure that the default domain is the same than it is in Dev in the Oracle configuration assistant and can test the connection successfully.

I do not save the sensitive in my packages, i use config files to store the connection string including the password.

This should not be relevant to this problem anyway because, if I create a new connection in the package and supply the password and then test the connection, it fails right there so the problem is at the data provider level.

Thanks,

Philippe

|||

Try changing your package to not use configuration files and instead embed the information into it.

Then try and see if it magically works.

I've been finding that using configuration files seems a bit buggy. When they run on the server it seems like sometimes SSIS can't load them causing the package to fail.

In my most recent case, I am getting the exact same error as you above and once i removed the configuration file it is now working seeming to indicate a problem loading the configuration. Since SSIS doesn't log any info that i can find about what is happening, it is virtually impossible to tell what is going on so I'm having to go with just hardcoding for now. Can't spend more time on figuring it out.

|||

This issue is very disturbing.

Yesterday, I connected again to this test server and all by magic, the Microsoft OLEDB provider was working again. I was able to create a new ssis, a new MS OLEDB For Oracle and it worked.

I then tried to run one of these packages that uses config files (and that run perfectly on the dev machine).

Same error than before.

Then, I went back to VS Studio, creatyed a new package, created a new MS OLEDB For Oracle, and it did not work. Nor did work the new package created just before trying the other older package...

I am pulling my hair on that one. The reason why I use config files is to avoid 2 major issues:
1) Package creator Password expiration every 90 days that kills all the encrypted content
2) Difficulties to quickly deploy packages from machine to machine

I will get a new production server real soon. I will then know if this problem is a reproductible bug or if it is specific to my newly rebuild test machine. I am not too optimistic...

Regards,

Philippe

|||I'm seeing this same problem as well. As soon as a configuration file is create the error starts. Even when modifying the .dtsconfig file to add the username & password then using dtexec to launch the package with the .dtsconfig file the error still occurs.|||

Could this be the problem?

http://blogs.conchango.com/jamiethomson/archive/2006/09/19/OLE-DB_3A00_-Cannot-connect-using-Microsoft-OLE-DB-Provider-for-Oracle.aspx

-Jamie

|||What helped me, too, was setting the system variable ORACLE_HOME to the oracle home directory.|||

hi,

This article was good.

Oracle connection is used to get Data in my case.

I get this error becasue package protection level was don't save sensitive the pwd.

So at execution time it says null password.

although it is not good practice to use protection level EncryptSensitiveWithPassword. but it works for my case.

Oracle Acquire Connection for ssis return null

Hi All!

I'm writing a custom component in c# for SSIS and I have a problem with AcquireConnection...

I wrote this code:

public override void AcquireConnections(object transaction)

{

if (ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager != null)

{

ConnectionManager cm = DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager);

ConnectionManagerAdoNet cmAdo = cm.InnerObject as ConnectionManagerAdoNet;

if (cmAdo == null)

throw new Exception("The ConnectionManager " + cm.Name + " is not an ADO connection.");

this.conn = cmAdo.AcquireConnection(transaction) as OracleConnection;

}

but the 'conn' is ALWAYS null...

I tried

this.conn = ((IDTSConnectionManagerDatabaseParameters90)cmAdo).GetConnectionForSchema() as OracleConnection;

too, but no result: the 'conn' is null again...

If I use oledbconnection or sqlconnection instead of oracleconnection the method works fine... I really don't understand

could you help me plz?

Using "as" will return NULL if the object can't be cast to the specified type. Is the connection manager actually an Oracle connection?|||

Yes, the connection manager is an Oracle Connection.
I use .Net Providers --> OracleClient Data Provider....

|||

I find the problem: I reference the class Oracle.DataAccess.Client and Oracle.DataAccess.Type and the 'conn' is null...

If I reference the System.Data.OracleClient all is right!

But I must use the Oracle.DataAccess... :-(

Has anybody use it yet?

|||

What is Oracle.DataAccess.Client? Is it an ADO.NET provider? If it is an ADO.NET provider you need to use an appropriate connection type that comes with it. Or you should be able to use generic connection (DbConnection) object.

HTH.

|||

Check the type of the object being returned, as there is some cinfusion as to what you have done here. Perhaps some code like -

object test = cmAdo.AcquireConnection(transaction);

Debug.Assert(false, test.Type.ToString());

I would also look at the ConnectionManagerType property for the connection, as that shoudl also tell you the type of ADo.Net connection you can expect back. See th example value below, showing that I used the MS ADO.Net oracl provider, and the class I know is System.Data.OracleClient.OracleConnection, found in the assemby System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089.

ADO.NETystem.Data.OracleClient.OracleConnection, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089

Perhaps you have not used a managed provider, but the .Net OleDb provider to connect to an Oracle OLE-DB provider. In which case your connection manager type would be -

ADO.NETystem.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089