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, SQL+ and calling a stored proc in a package

I have this code:

declare
return_status number;
return_msg varchar2(255);
variable retval number;
execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', return_status, return_msg);
/

I get SP2-0552: Bind variable "RETVAL" not declared.

I have searched Google and the Oracle docs and I cannot find ONE single decent example of calling a stored procedure that's in a package and where that stored procedure has OUT parameters.

HELP!!?!?!

n/m

this worked:

declare
return_status number;
return_msg varchar2(255);
begin
update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', return_status, return_msg);
end;
/Originally posted by nightowlky
I have this code:

declare
return_status number;
return_msg varchar2(255);
variable retval number;
execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', return_status, return_msg);
/

I get SP2-0552: Bind variable "RETVAL" not declared.

I have searched Google and the Oracle docs and I cannot find ONE single decent example of calling a stored procedure that's in a package and where that stored procedure has OUT parameters.

HELP!!?!?!

n/m

this worked:

declare
return_status number;
return_msg varchar2(255);
begin
update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', return_status, return_msg);
end;
/
The problem is that you are confusing SQL Plus syntax with PL/SQL syntax. VARIABLE and EXECUTE are SQL Plus commands, so your example should look like one of these:

1) All PL/SQL variables:

declare
return_status number;
return_msg varchar2(255);
retval number; -- no VARIABLE keyword
begin
retval := -- No EXECUTE and no ":"
update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', return_status, return_msg);
end;
/

2) SQL Plus variables:

SQL> variable retval number;
SQL> variable return_status number;
SQL> variable return_msg varchar2(255);
SQL> execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg);

3) Combination of SQL Plus and PL/SQL variables:

SQL> variable retval number;

declare
return_status number;
return_msg varchar2(255);
begin
:retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', return_status, return_msg);
end;
/

The difference is that SQL Plus variables are available to SQL Plus after the PL/SQL block has completed execution, i.e. in examples 2 and 3 you can go on to use :retval again.|||Thanks, Andrew.

But, using all 'variable' statements, I get this:

Wrote file afiedt.buf

1 variable retval number;
2 variable return_status number;
3 variable return_msg varchar2(255);
4* execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg);
SQL> run
1 variable retval number;
2 variable return_status number;
3 variable return_msg varchar2(255);
4* execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg);
SP2-0552: Bind variable "RETURN_MSG" not declared.

All I'm really trying to do is be able to print out the values of return_status and return_msg.

I'm all new to Oracle, SQL+, PL/SQL. I come from a long history of MS SQL Server. Speaking of which, are there tools similar to the MS tools for writing queries, importing data, etc.? Seems all I have installed on this workstation is SQL+ and WinSQL (which chokes on syntax that works in SQL+ ??)|||Originally posted by nightowlky
Thanks, Andrew.

But, using all 'variable' statements, I get this:

Wrote file afiedt.buf

1 variable retval number;
2 variable return_status number;
3 variable return_msg varchar2(255);
4* execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg);
SQL> run
1 variable retval number;
2 variable return_status number;
3 variable return_msg varchar2(255);
4* execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg);
SP2-0552: Bind variable "RETURN_MSG" not declared.

All I'm really trying to do is be able to print out the values of return_status and return_msg.

I'm all new to Oracle, SQL+, PL/SQL. I come from a long history of MS SQL Server. Speaking of which, are there tools similar to the MS tools for writing queries, importing data, etc.? Seems all I have installed on this workstation is SQL+ and WinSQL (which chokes on syntax that works in SQL+ ??)
The problem is that the RUN command treats contents of the buffer as a single command, which this is not. Instead of using RUN, try this:

SQL> @.afiedt.buf

That correctly processes 4 "statements" (3 VARIABLE commands and one EXECUTE command).|||ah...

well...

it gets better:

SQL> @.afiedt.buf

PL/SQL procedure successfully completed.

variable return_status number;
*
ERROR at line 1:
ORA-00900: invalid SQL statement

DOH!|||Originally posted by nightowlky
ah...

well...

it gets better:

SQL> @.afiedt.buf

PL/SQL procedure successfully completed.

variable return_status number;
*
ERROR at line 1:
ORA-00900: invalid SQL statement

DOH!
What does afiedt.buf contain now?|||VARIABLE return_status NUMBER
VARIABLE return_msg VARCHAR2(255)
EXECUTE update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg)
/

I don't believe the whole procedure returns a value (other than the out parameters) so I removed the retval variable.|||Originally posted by nightowlky
VARIABLE return_status NUMBER
VARIABLE return_msg VARCHAR2(255)
EXECUTE update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg)
/

I don't believe the whole procedure returns a value (other than the out parameters) so I removed the retval variable.
You don't need or want that "/" on the last line. Remove that and the error should go away!|||Ah!

The sweet smell of success!

Thank you, Andrew!

Thank you!

:D|||Why dont you try using dbms_output.put_line function to display the output ? The steps are as follows :

-- test1.sql
declare
l_ret_val number;
begin
l_ret_val := call_function();
dbms_output.put_line(l_ret_val);
end;
/

Now at sql prompt execute the following commands :
sql> set serveroutput on
sql> @.test1.sql

Hope this helps ...|||I'll try that, too.

As I said...I'm quite the Oracle newbie and I'm trying to just find stuff that works. And finding examples of some of this stuff is like trying to put your elbow in your ear (can almost get there but not quite) :)|||Originally posted by nightowlky
I'll try that, too.

As I said...I'm quite the Oracle newbie and I'm trying to just find stuff that works. And finding examples of some of this stuff is like trying to put your elbow in your ear (can almost get there but not quite) :)
When using VARIABLE in SQL Plus, you will find the PRINT command handy:

SQL> variable a number
SQL> exec :a := 123

PL/SQL procedure successfully completed.

SQL> print a

A
----
123

Oracle Stored Procedure Syntax

I'd like to write a report with a data set that calls an Oracle data source
for its result set. I have a package with a procedure that returns a ref
cursor as an out variable. What is the syntax for calling this procedure
from the Report Designer? Does the out variable with the cursor need to be
mapped to anything? Are input parameters on the procedure anything other
than regular @.parameter references?
Thanks!John,
The sql syntax is identical to the that used for MS SQL Server, except for
the @.. Switch it to a : (colon)
and the parameters will work just fine, example:
Select Blah
From tblBlah
where Blah.tblBlah = :param
I like to use the IN Keyword. If you set your parameters as multi-select,
the parameters are passed in as an string seperated by commas, so your Where
clause would look like this:
Where Blah.tblBlah IN (:Param)
Hope this helps,
rwiethorn
"John W at Sungard HE" wrote:
> I'd like to write a report with a data set that calls an Oracle data source
> for its result set. I have a package with a procedure that returns a ref
> cursor as an out variable. What is the syntax for calling this procedure
> from the Report Designer? Does the out variable with the cursor need to be
> mapped to anything? Are input parameters on the procedure anything other
> than regular @.parameter references?
> Thanks!|||Hi,
Continuing with John's question, I want to know, What is the syntax for
calling the oracle stored procedure from the Report Designer? My procedure is
returning a ref cursor as an out variable.
Regards,
Aruna
"rwiethorn" wrote:
> John,
> The sql syntax is identical to the that used for MS SQL Server, except for
> the @.. Switch it to a : (colon)
> and the parameters will work just fine, example:
> Select Blah
> From tblBlah
> where Blah.tblBlah = :param
> I like to use the IN Keyword. If you set your parameters as multi-select,
> the parameters are passed in as an string seperated by commas, so your Where
> clause would look like this:
> Where Blah.tblBlah IN (:Param)
> Hope this helps,
> rwiethorn
>
> "John W at Sungard HE" wrote:
> > I'd like to write a report with a data set that calls an Oracle data source
> > for its result set. I have a package with a procedure that returns a ref
> > cursor as an out variable. What is the syntax for calling this procedure
> > from the Report Designer? Does the out variable with the cursor need to be
> > mapped to anything? Are input parameters on the procedure anything other
> > than regular @.parameter references?
> >
> > Thanks!|||Thanks for the response - the bit with the parameters defintely helps. Do
you have an example that references a stored procedure instead of a select
statement? For example, do I have to wrap the procedure call in a PL/SQL
block like:
BEGIN
CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
END
or is it sufficient to call the procedure without the block like:
CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
And is the above parameter syntax going to work? What about the output
parameter that accepts the cursor back from the stored procedure? Is that
just set up as a report parameter like any other, even though it's an output
parameter? Does it matter where the output parameter is placed in the call
list, i.e. does it need to appear either first or last?
Thanks!
JW
"rwiethorn" wrote:
> John,
> The sql syntax is identical to the that used for MS SQL Server, except for
> the @.. Switch it to a : (colon)
> and the parameters will work just fine, example:
> Select Blah
> From tblBlah
> where Blah.tblBlah = :param
> I like to use the IN Keyword. If you set your parameters as multi-select,
> the parameters are passed in as an string seperated by commas, so your Where
> clause would look like this:
> Where Blah.tblBlah IN (:Param)
> Hope this helps,
> rwiethorn
>
> "John W at Sungard HE" wrote:
> > I'd like to write a report with a data set that calls an Oracle data source
> > for its result set. I have a package with a procedure that returns a ref
> > cursor as an out variable. What is the syntax for calling this procedure
> > from the Report Designer? Does the out variable with the cursor need to be
> > mapped to anything? Are input parameters on the procedure anything other
> > than regular @.parameter references?
> >
> > Thanks!|||change the command type to stored procedure.
The syntax then looks like this
packagename.procedurename
no need to put any parameters in here.
John W at Sungard HE wrote:
> Thanks for the response - the bit with the parameters defintely helps. Do
> you have an example that references a stored procedure instead of a select
> statement? For example, do I have to wrap the procedure call in a PL/SQL
> block like:
> BEGIN
> CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> END
> or is it sufficient to call the procedure without the block like:
> CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> And is the above parameter syntax going to work? What about the output
> parameter that accepts the cursor back from the stored procedure? Is that
> just set up as a report parameter like any other, even though it's an output
> parameter? Does it matter where the output parameter is placed in the call
> list, i.e. does it need to appear either first or last?
> Thanks!
> JW
> "rwiethorn" wrote:
> > John,
> > The sql syntax is identical to the that used for MS SQL Server, except for
> > the @.. Switch it to a : (colon)
> > and the parameters will work just fine, example:
> > Select Blah
> > From tblBlah
> > where Blah.tblBlah = :param
> >
> > I like to use the IN Keyword. If you set your parameters as multi-select,
> > the parameters are passed in as an string seperated by commas, so your Where
> > clause would look like this:
> > Where Blah.tblBlah IN (:Param)
> >
> > Hope this helps,
> > rwiethorn
> >
> >
> > "John W at Sungard HE" wrote:
> >
> > > I'd like to write a report with a data set that calls an Oracle data source
> > > for its result set. I have a package with a procedure that returns a ref
> > > cursor as an out variable. What is the syntax for calling this procedure
> > > from the Report Designer? Does the out variable with the cursor need to be
> > > mapped to anything? Are input parameters on the procedure anything other
> > > than regular @.parameter references?
> > >
> > > Thanks!|||John,
I'm a MS SQL Server guy myself, so I'm limited on the Oracle. I have a
Oracle DBA that makes tables for me when I define my sql statement. I've not
had the need to do any processing, I'm been only needing simple selects with
groupings.
>What about the output
> parameter that accepts the cursor back from the stored procedure?
I think you can do with out it, the system may just 'swallow' the return
value (ie a 0 or 1) indicating a success or failure.
>Does it matter where the output parameter is placed in the call
> list, i.e. does it need to appear either first or last?
Yes, parameter order does matter when your calling stored procs, you need to
match the order and the data type.
I hope this helps,
rwiethorn
"John W at Sungard HE" wrote:
> Thanks for the response - the bit with the parameters defintely helps. Do
> you have an example that references a stored procedure instead of a select
> statement? For example, do I have to wrap the procedure call in a PL/SQL
> block like:
> BEGIN
> CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> END
> or is it sufficient to call the procedure without the block like:
> CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> And is the above parameter syntax going to work? What about the output
> parameter that accepts the cursor back from the stored procedure? Is that
> just set up as a report parameter like any other, even though it's an output
> parameter? Does it matter where the output parameter is placed in the call
> list, i.e. does it need to appear either first or last?
> Thanks!
> JW
> "rwiethorn" wrote:
> > John,
> > The sql syntax is identical to the that used for MS SQL Server, except for
> > the @.. Switch it to a : (colon)
> > and the parameters will work just fine, example:
> > Select Blah
> > From tblBlah
> > where Blah.tblBlah = :param
> >
> > I like to use the IN Keyword. If you set your parameters as multi-select,
> > the parameters are passed in as an string seperated by commas, so your Where
> > clause would look like this:
> > Where Blah.tblBlah IN (:Param)
> >
> > Hope this helps,
> > rwiethorn
> >
> >
> > "John W at Sungard HE" wrote:
> >
> > > I'd like to write a report with a data set that calls an Oracle data source
> > > for its result set. I have a package with a procedure that returns a ref
> > > cursor as an out variable. What is the syntax for calling this procedure
> > > from the Report Designer? Does the out variable with the cursor need to be
> > > mapped to anything? Are input parameters on the procedure anything other
> > > than regular @.parameter references?
> > >
> > > Thanks!|||Sorry, I did not get it. How I can send an OUT parameter (CURSOR) and use it
to generate report?
Thank you
"rwiethorn" wrote:
> John,
> The sql syntax is identical to the that used for MS SQL Server, except for
> the @.. Switch it to a : (colon)
> and the parameters will work just fine, example:
> Select Blah
> From tblBlah
> where Blah.tblBlah = :param
> I like to use the IN Keyword. If you set your parameters as multi-select,
> the parameters are passed in as an string seperated by commas, so your Where
> clause would look like this:
> Where Blah.tblBlah IN (:Param)
> Hope this helps,
> rwiethorn
>
> "John W at Sungard HE" wrote:
> > I'd like to write a report with a data set that calls an Oracle data source
> > for its result set. I have a package with a procedure that returns a ref
> > cursor as an out variable. What is the syntax for calling this procedure
> > from the Report Designer? Does the out variable with the cursor need to be
> > mapped to anything? Are input parameters on the procedure anything other
> > than regular @.parameter references?
> >
> > Thanks!|||Sorry, can I have more details, please. Like, what driver did you use, did
you use parameters Tab to set parameter and so on.
Thank you
"gene.furibondo@.gmail.com" wrote:
> change the command type to stored procedure.
> The syntax then looks like this
> packagename.procedurename
> no need to put any parameters in here.
> John W at Sungard HE wrote:
> > Thanks for the response - the bit with the parameters defintely helps. Do
> > you have an example that references a stored procedure instead of a select
> > statement? For example, do I have to wrap the procedure call in a PL/SQL
> > block like:
> >
> > BEGIN
> > CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> > END
> >
> > or is it sufficient to call the procedure without the block like:
> >
> > CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> >
> > And is the above parameter syntax going to work? What about the output
> > parameter that accepts the cursor back from the stored procedure? Is that
> > just set up as a report parameter like any other, even though it's an output
> > parameter? Does it matter where the output parameter is placed in the call
> > list, i.e. does it need to appear either first or last?
> >
> > Thanks!
> >
> > JW
> >
> > "rwiethorn" wrote:
> >
> > > John,
> > > The sql syntax is identical to the that used for MS SQL Server, except for
> > > the @.. Switch it to a : (colon)
> > > and the parameters will work just fine, example:
> > > Select Blah
> > > From tblBlah
> > > where Blah.tblBlah = :param
> > >
> > > I like to use the IN Keyword. If you set your parameters as multi-select,
> > > the parameters are passed in as an string seperated by commas, so your Where
> > > clause would look like this:
> > > Where Blah.tblBlah IN (:Param)
> > >
> > > Hope this helps,
> > > rwiethorn
> > >
> > >
> > > "John W at Sungard HE" wrote:
> > >
> > > > I'd like to write a report with a data set that calls an Oracle data source
> > > > for its result set. I have a package with a procedure that returns a ref
> > > > cursor as an out variable. What is the syntax for calling this procedure
> > > > from the Report Designer? Does the out variable with the cursor need to be
> > > > mapped to anything? Are input parameters on the procedure anything other
> > > > than regular @.parameter references?
> > > >
> > > > 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 connection using configuration file

I created a package that uses configuration file for db connections. SQL Server connection works fine, but for oracle it fails.

I m putting servername=alias from tnsnames.ora file.

the connection works fine from ide

For an OLE-DB connection the value of the config file property assignment, for connection string, would look something like this-

Data Source=ABC.XYZ;User ID=UserName;Password=secret;Provider=MSDAORA.1;Persist Security Info=True;

ABC.XYZ is the alias from tnsnames.

|||

Thanks Darren...

I exactly have the same line as you suggested. The issue was with InitialCatalog propterty. I took that out from configuration file and it worked fine.

I read somewhere only the servername, userid and password property is enough to make a connection to oracle database.

Thanks

Mahesh

oracle connection manager configuration help

Hi, I dont know if I should post this question here, or on oracle forums, but here goes ...
I made a simple package that performs 2 Excecute SQL Tasks, one task does a select query on a SQL Server DB Table, the other task does a select query on an Oracle DB Table.

The package runs fine in BIDS when debugging, but when I deployed the package to a file system and added it to an sql server agent Job, it all went wrong ...

I tried the ms oledb dataprovider (ole db) for oracle but also the oracleclient data provider (ado.net) but the package always fails.

The error messages in the logfile are these:

event	OnError
computer	ComputerName
operator	NT AUTHORITY\SYSTEM
source	Execute SQL Task PLSQL
sourceid	{B29C4FC9-3345-4657-8680-F770B8C2E136}
executionid	{7312A7EC-2555-4268-BB29-38659507BADD}
starttime	14/04/2006 16:40
endtime	14/04/2006 16:40
datacode	-1073573396
databytes	0x
message	Failed to acquire connection "aaa.bbb.oracleClient". Connection may not be configured correctly or you may not have the right permissions on this connection.

the OnError logrow for the package itself says almost the same.

Now my question is: What do I need to configure to get it all working? I think I need to configure the oracle db no? What should I configure? ...

Take a look at http://www.oracle.com/technology/tech/windows/odpnet/beta_doc/featADO20.htm

the <configuration> ... </configuration> in the Connection String builder section.

|||owkey the oracle client wasn't installed on the server where the packages were deployed and where ssis server is running. BUT the admin is not planning on installing them :s

So that leaves me with another question ... does anyone ever configured a ssis server on an other server than the server that contains the packages? The only thing that we configured was the ssis xml file called MsDtsSrvr.ini.xml, more information can be found here http://msdn2.microsoft.com/en-us/library/ms137789.aspx We also followed the tips on these sites http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=254204&SiteID=1 and http://sqljunkies.com/WebLog/knight_reign/archive/2006/01/05/17769.aspx but still we were unable to get it to work. The MSDB folder never shows any content even if something got deployed to that server.
So what we want to have:
Server1 who runs SSRS, SSIS and has oracle client installed.
Server2 who runs SQL Server, SQL Server Agent, SSAS, and packages are deployed here.
In the config file (of SSIS on Server1) we reference to the MSDB of Server2.
What is going wrong? What additional things need to be configured?

Some extra info: SP1 isn't installed yet because it is prerelease|||

You need SSIS to be installed on the server upon which the package is executing. You will also need the Oracle provider on that machine. If calling the package from SQL Agent on Server 2 then you need that stuff on Server 2. You obviously have SSIS on Server 2 already to have got as far as you did. If your plan was to off load SSIS work onto Server 1, then it needs to execute there.

Why not install SQL on Server 1, and just use the DB for logging and to support SQL Agent. You have licensed both Servers for full SQL Server, so why not use it. You may not wish to use the DB for more than that, but having a strict rule of no DB engine on a server means more hassle that it is worth. If you use a backup tool, then you probably already have a DB instance in one form or another! DB Instances don;t have to be resource hogs if you treat them right, and don't ask them to do large workloads.

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.