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 PROCEDURE VS. MS SQL SERVER PROCEDURE URGENT!

hi Guys !!
I hope you can help with this one...
I have to make a Procedure that can be able to receive and Array and process
inside itself..
In oracle you can declare a TABLE_TYPE and then you pass and array to it..
But how can i do that in MS SQL SERVER...?
I'll be really thanksfully
greetings
RemiHi
There is no direct equivalent in SQL Server 2000 as your table type can have
different datatypes of each element. You may want to look at passing a
cursor or using a temporary table, alternatively it may be possible to stuff
(overlay) them into a character or binary variable. The following will be
useful reading: http://www.sommarskog.se/arrays-in-sql.html
John
"Remi" <Remi1@.hotmail.com> wrote in message
news:elM0x3M5EHA.2804@.TK2MSFTNGP15.phx.gbl...
> hi Guys !!
> I hope you can help with this one...
> I have to make a Procedure that can be able to receive and Array and
> process
> inside itself..
> In oracle you can declare a TABLE_TYPE and then you pass and array to it..
> But how can i do that in MS SQL SERVER...?
> I'll be really thanksfully
> greetings
> Remi
>|||Try looking into SQL_VARIANT data type.
Sincerely,
Anthony Thomas
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:uv9l2VN5EHA.1192@.tk2msftngp13.phx.gbl...
Hi
There is no direct equivalent in SQL Server 2000 as your table type can have
different datatypes of each element. You may want to look at passing a
cursor or using a temporary table, alternatively it may be possible to stuff
(overlay) them into a character or binary variable. The following will be
useful reading: http://www.sommarskog.se/arrays-in-sql.html
John
"Remi" <Remi1@.hotmail.com> wrote in message
news:elM0x3M5EHA.2804@.TK2MSFTNGP15.phx.gbl...
> hi Guys !!
> I hope you can help with this one...
> I have to make a Procedure that can be able to receive and Array and
> process
> inside itself..
> In oracle you can declare a TABLE_TYPE and then you pass and array to it..
> But how can i do that in MS SQL SERVER...?
> I'll be really thanksfully
> greetings
> Remi
>

ORACLE PROCEDURE VS. MS SQL SERVER PROCEDURE URGENT!

hi Guys !!
I hope you can help with this one...
I have to make a Procedure that can be able to receive and Array and process
inside itself..
In oracle you can declare a TABLE_TYPE and then you pass and array to it..
But how can i do that in MS SQL SERVER...?
I'll be really thanksfully
greetings
Remi
Hi
There is no direct equivalent in SQL Server 2000 as your table type can have
different datatypes of each element. You may want to look at passing a
cursor or using a temporary table, alternatively it may be possible to stuff
(overlay) them into a character or binary variable. The following will be
useful reading: http://www.sommarskog.se/arrays-in-sql.html
John
"Remi" <Remi1@.hotmail.com> wrote in message
news:elM0x3M5EHA.2804@.TK2MSFTNGP15.phx.gbl...
> hi Guys !!
> I hope you can help with this one...
> I have to make a Procedure that can be able to receive and Array and
> process
> inside itself..
> In oracle you can declare a TABLE_TYPE and then you pass and array to it..
> But how can i do that in MS SQL SERVER...?
> I'll be really thanksfully
> greetings
> Remi
>
|||Try looking into SQL_VARIANT data type.
Sincerely,
Anthony Thomas

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:uv9l2VN5EHA.1192@.tk2msftngp13.phx.gbl...
Hi
There is no direct equivalent in SQL Server 2000 as your table type can have
different datatypes of each element. You may want to look at passing a
cursor or using a temporary table, alternatively it may be possible to stuff
(overlay) them into a character or binary variable. The following will be
useful reading: http://www.sommarskog.se/arrays-in-sql.html
John
"Remi" <Remi1@.hotmail.com> wrote in message
news:elM0x3M5EHA.2804@.TK2MSFTNGP15.phx.gbl...
> hi Guys !!
> I hope you can help with this one...
> I have to make a Procedure that can be able to receive and Array and
> process
> inside itself..
> In oracle you can declare a TABLE_TYPE and then you pass and array to it..
> But how can i do that in MS SQL SERVER...?
> I'll be really thanksfully
> greetings
> Remi
>

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