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

ORDER BY in views - difference in 2005 vs 2000?

I am no SQL wizard, so if I have a flawed basic understanding please be
blunt so I will understand...
In 2000, we set up a lot of views with ORDER BY to get records in an
expected order. If I had ViewA and it contained an order by for Field1, then
if I did "SELECT * from ViewA" in SQL Server 2000, the records came back
sorted by Field1. In SQL Server 2005, it does not. If I get the definition
of the veiew and submit that as SQL, it works, but that seems like a
rediculous workaround as it costs an extra round trip and bypasses
precompilation. The documentation says "ORDER BY guarantees a sorted result
only for the outermost SELECT statement of a query."
Any help or suggestions are appreciated."Andy Barnhart" <andyDOTbarnhart@.ciincDOTcom> wrote in message
news:OyEX3Vc9FHA.1224@.TK2MSFTNGP12.phx.gbl...
> I am no SQL wizard, so if I have a flawed basic understanding please be
> blunt so I will understand...
> In 2000, we set up a lot of views with ORDER BY to get records in an
> expected order. If I had ViewA and it contained an order by for Field1,
> then if I did "SELECT * from ViewA" in SQL Server 2000, the records came
> back sorted by Field1. In SQL Server 2005, it does not. If I get the
> definition of the veiew and submit that as SQL, it works, but that seems
> like a rediculous workaround as it costs an extra round trip and bypasses
> precompilation. The documentation says "ORDER BY guarantees a sorted
> result only for the outermost SELECT statement of a query."
> Any help or suggestions are appreciated.
There have been a few posts lately on this.
Seems that SQL server has never guaranteed the order when using ORDER BY in
a view.
This works: SELECT TOP 5 ... FROM table ORDER BY
SQL guarantees that the correct TOP 5 will be returned, but does not
guarantee the order.
There was not an issue with SQL 2000 but there is with 2005.
The only suggestion that I can give you is: stop using ORDER BY in views
unless you want something like my example.
You should NEVER has used ORDER BY in views in the way you described...
sorry.
You should create a view with no ordering and then: Select ... from yourView
ORDER BY ...|||Andy Barnhart wrote:
> if I did "SELECT * from ViewA" in SQL Server 2000, the records came back
> sorted by Field1.
Maybe, maybe not. The resulting order of a SELECT statement without an
ORDER BY clause is always undefined - it's dangerous to assume any
predictable ordering.

> The documentation says "ORDER BY guarantees a sorted result
> only for the outermost SELECT statement of a query."
>
That's correct.

> Any help or suggestions are appreciated.
Try:
SELECT * FROM ViewA ORDER BY col
which works equally well in SQL Server 2000 and 2005. :-)
David Portas
SQL Server MVP
--

oracle support - Report Model

If my understanding is correct, Oracle is not yet supported as a data source
for report models in SSRS 2005 (and thus not available for report builder).
Is there a timeframe estimate to when this might be available?I would also like to know. Also if there are any APIs to develop one. Can we
develop own application which can use Oracle database to build report model?
Thank you - Ashok
"chicagoclone" wrote:
> If my understanding is correct, Oracle is not yet supported as a data source
> for report models in SSRS 2005 (and thus not available for report builder).
> Is there a timeframe estimate to when this might be available?|||I burned a MSDN Developer Incident on this one. Basically I was able to ceate
a Report Model by using a Linked Server and creating a view on the linked
server. From there I used the Linked View in the report Modeler, it is a wee
it slow (30-45 seconds for 18,000 rows over a T-1) however until they support
Oracle it's how I am going to apporach it. Hope this helps...
"Ashok" wrote:
> I would also like to know. Also if there are any APIs to develop one. Can we
> develop own application which can use Oracle database to build report model?
> Thank you - Ashok
> "chicagoclone" wrote:
> > If my understanding is correct, Oracle is not yet supported as a data source
> > for report models in SSRS 2005 (and thus not available for report builder).
> > Is there a timeframe estimate to when this might be available?|||Oracle support is scheduled to be added in SP2.
"chicagoclone" wrote:
> If my understanding is correct, Oracle is not yet supported as a data source
> for report models in SSRS 2005 (and thus not available for report builder).
> Is there a timeframe estimate to when this might be available?|||Thank you Bob! This is great news.
"Bob Meyers - MSFT" wrote:
> Oracle support is scheduled to be added in SP2.
> "chicagoclone" wrote:
> > If my understanding is correct, Oracle is not yet supported as a data source
> > for report models in SSRS 2005 (and thus not available for report builder).
> > Is there a timeframe estimate to when this might be available?

Oracle 9i Database Administration in 10 Minutes

The Topics Covered In This Book Are: Understanding the Oracle
Environment; Understanding the Oracle Instance; Understanding the
working of Oracle Instance; Understanding the Oracle Database; Oracle
9i Software Installation; Oracle 9i Database Design using DBCA;
Enabling other computers to access Oracle Server; Oracle Enterprise
Manger Oracle Backup & Recovery -Simple Technique; Oracle Performance
Tuning. Everything in this book is covered in a step by step manner
by
first building reader's concept using analogies, real world examples
and then taking him/her to the steps of design implementation. The
book covers Oracle 9i Database Server. The concepts and most of the
stuff discussed in this book are equally good for other Oracle
versions including 10g. For advance user please check Oracle Database
Administration Concepts & Implementation Made Simple, ISBN:
0977073904.

The link is provided below:

http://www.lulu.com/takveentakveen@.gmail.com wrote:

Quote:

Originally Posted by

The Topics Covered In This Book Are: Understanding the Oracle
Environment;

This book is worthless spam in the Oracle usenet groups. It is
appalling here.

--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org