Configuration file reused by different packages

I have two packages share same configuration file. There are 4 connections in the first package and 2 connections in the second. Configuration file contains properties for 4 connections. First package opens in VS designer with no error. The problem with second package.

I get error that connection exist in the configuration but not in the package

and

"Could not copy file " Master_Configuration.dtsConfig" to the deployment utility output directory. Master_Configuration.dtsConfig' already exists."

Any help?

For the former error, I'd suggest just using one config per connection. It may seem annoying but over a large project it works quite well and offers flexibility. If you do have common connections in all packages, then you can group them, but even that is not necessary.

Not really used the deployment tool, so cannot comment on the second error.

|||

It is more convenient to use one (master) configuration file. I use dtexec utility to run packages in production. With many config files I would have to dynamically bild /ConfigFile parameter for each file for each package.

|||I would set the configurations at design-time. I think this is a much better option all round. It is easy to change development systems, and ensures you can use the ProtectionLevel DontSaveSensitive option even if you require passwords for connections without any problems. Using indirect configurations means you can handle changing paths for your config files as you move through environments.

Conditional table entries and sums

What I have is 3 columns. The first and second are data from a database, the
third is a conditional difference of the two. That is, column 3 is C1-C2 if
that is > 0, else it is 0.
So I have:
C1 C2 C3
1 2 0
2 1 1
I can get that to work fine, using Iif(). The problem is that I need a total
row at the bottom of the table. Columns 1 and 2 are easy to sum, but how can
I sum the conditional entries of column 3?
Thanks!You can add a calculated field to your dataset. Check out RS Books Online
for instructions. (In the report designer, right-click in the Fields pane.)
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"RPH" <RPH@.discussions.microsoft.com> wrote in message
news:FC250591-C2B6-4399-AAC4-4515217CEAFA@.microsoft.com...
> What I have is 3 columns. The first and second are data from a database,
> the
> third is a conditional difference of the two. That is, column 3 is C1-C2
> if
> that is > 0, else it is 0.
> So I have:
> C1 C2 C3
> 1 2 0
> 2 1 1
> I can get that to work fine, using Iif(). The problem is that I need a
> total
> row at the bottom of the table. Columns 1 and 2 are easy to sum, but how
> can
> I sum the conditional entries of column 3?
> Thanks!

Conditional Join

What I have are two different tables that I am going to join. Everything in
the first table should be pulled. The second table should only pull the
information when the field ACTION equals the word "ORDERED". Is there a way
to join two tables on just a literal
Table A
Tran Date_ORD Date_Rec ID Rate
4756 10/23/05 99/99/99 J234 1.26
7364 10/23/05 10/26/05 H342 3.23
9834 09/23/04 10 05/04 J234 1.74
8374 08/29/05 09/03/05 K834 2.85
6756 09/21/05 99/99/99 J234 4.26
7263 11/01/05 11/06/05 H342 2.23
1844 10/02/05 10/05/05 J234 3.74
2333 06/27/05 07/01/05 K834 5.85
Table B
Tran Action
4756 ORDERED
7364 RECEIVE
9834 CANCELE
8374 BACKORD
6756 ORDERED
7263 RECEIVE
1844 RECEIVE
Output
4756 10/23/05 10/25/05 J234 1.26 ORDERED
7364 10/23/05 10/26/05 H342 3.23
9834 09/23/04 10 05/04 J234 1.74
8374 08/29/05 09/03/05 K834 2.85
6756 09/21/05 09/24/05 J234 4.26 ORDERED
7263 11/01/05 11/06/05 H342 2.23
1844 10/02/05 10/05/05 J234 3.74
2333 06/27/05 07/01/05 K834 5.85
On Fri, 11 Nov 2005 12:17:02 -0800, Daniell wrote:

>What I have are two different tables that I am going to join. Everything in
>the first table should be pulled. The second table should only pull the
>information when the field ACTION equals the word "ORDERED". Is there a way
>to join two tables on just a literal
>Table A
>Tran Date_ORD Date_Rec ID Rate
>4756 10/23/05 99/99/99 J234 1.26
>7364 10/23/05 10/26/05 H342 3.23
>9834 09/23/04 10 05/04 J234 1.74
>8374 08/29/05 09/03/05 K834 2.85
>6756 09/21/05 99/99/99 J234 4.26
>7263 11/01/05 11/06/05 H342 2.23
>1844 10/02/05 10/05/05 J234 3.74
>2333 06/27/05 07/01/05 K834 5.85
>Table B
>Tran Action
>4756 ORDERED
>7364 RECEIVE
>9834 CANCELE
>8374 BACKORD
>6756 ORDERED
>7263 RECEIVE
>1844 RECEIVE
>Output
>4756 10/23/05 10/25/05 J234 1.26 ORDERED
>7364 10/23/05 10/26/05 H342 3.23
>9834 09/23/04 10 05/04 J234 1.74
>8374 08/29/05 09/03/05 K834 2.85
>6756 09/21/05 09/24/05 J234 4.26 ORDERED
>7263 11/01/05 11/06/05 H342 2.23
>1844 10/02/05 10/05/05 J234 3.74
>2333 06/27/05 07/01/05 K834 5.85
Hi Daniell,
I think that this is what you want:
SELECT a.Tran, a.Date_ORD, a.Date_Rec, a.ID, a.Rate,
COALESCE(b.Action, '') AS Action
FROM TableA AS a
LEFT OUTER JOIN TableB AS b
ON b.Tran = a.Tran
AND b.Action = 'ORDERED'
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Conditional group footer sum

HI,
I am working with a developer who has an interesting problem. They have
data field in a sql table that is of a numeric value a second field that
tells whether or not the previous value is a debit or credit for a general
ledger.
They want to show on a report a sum of the numeric field in a group footer.
We have tried writing a conditional formula(=Iif(FieldB="dr",Sum(FieldA),o))
for a hidden field on the report in the detail row of a table and then using
the Reportitems Syntax to display that fields value in the group footer, but
we get an out of scope error.
Looking for possible suggestions, code sample, or alternatives. Changing
the data in the SQL table is not a possibility.
Thanks!!I recently tried doing something similar to this but I was getting a data
type error. I resolved it by making the following change:
Orig: sum(iif(Fields!Type.Value = 'Dr', Fields!Amount.Value, 0) --Got errors
New: sum(iif(Fields!Type.Value = 'Dr', Fields!Amount.Value,
Fields!Amount.Value*0)
I don't know why mulitplying by 0 gives the correct data type, but simply
putting a 0 in does not, but that is only variation (0, 0.0, 0.00, etc...) I
could find that worked.
"Mark" wrote:
> HI,
> I am working with a developer who has an interesting problem. They have
> data field in a sql table that is of a numeric value a second field that
> tells whether or not the previous value is a debit or credit for a general
> ledger.
> They want to show on a report a sum of the numeric field in a group footer.
> We have tried writing a conditional formula(=Iif(FieldB="dr",Sum(FieldA),o))
> for a hidden field on the report in the detail row of a table and then using
> the Reportitems Syntax to display that fields value in the group footer, but
> we get an out of scope error.
> Looking for possible suggestions, code sample, or alternatives. Changing
> the data in the SQL table is not a possibility.
> Thanks!!|||The reason why multiplying with 0 works is that it preserves the original
datatype of the numeric field (which could be anything like UInt16, Decimal,
etc.).
This should work (the constant value 0.0 is a System.Double at runtime):
=sum(iif(Fields!Type.Value = 'Dr', CDbl(Fields!Amount.Value), 0.0)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
news:9EAB31A6-E7CA-4912-82BC-A678EAE9CA0C@.microsoft.com...
> I recently tried doing something similar to this but I was getting a data
> type error. I resolved it by making the following change:
> Orig: sum(iif(Fields!Type.Value = 'Dr', Fields!Amount.Value, 0) --Got
errors
> New: sum(iif(Fields!Type.Value = 'Dr', Fields!Amount.Value,
> Fields!Amount.Value*0)
> I don't know why mulitplying by 0 gives the correct data type, but simply
> putting a 0 in does not, but that is only variation (0, 0.0, 0.00, etc...)
I
> could find that worked.
> "Mark" wrote:
> > HI,
> >
> > I am working with a developer who has an interesting problem. They have
> > data field in a sql table that is of a numeric value a second field that
> > tells whether or not the previous value is a debit or credit for a
general
> > ledger.
> >
> > They want to show on a report a sum of the numeric field in a group
footer.
> > We have tried writing a conditional
formula(=Iif(FieldB="dr",Sum(FieldA),o))
> > for a hidden field on the report in the detail row of a table and then
using
> > the Reportitems Syntax to display that fields value in the group
footer, but
> > we get an out of scope error.
> >
> > Looking for possible suggestions, code sample, or alternatives.
Changing
> > the data in the SQL table is not a possibility.
> >
> > Thanks!!

Conditional formatting based on second dataset

I am trying to create a report which has conditional formatting.

The primary dataset is a view of objects with several values

eg

object1,0,4,0,1

object2,0,3,1,1

The secondary dataset is the comparison table and just contains the values

eg

0,3,1,1

I'd like to conditionally format the values based on the comparison table but when I create an expression comparing to the second dateset

eg

=iif(Fields!object1.Value <> Fields!comp_object1.Value , "Red", "SkyBlue")

i get

Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

Not sure if there is a way to tell the expression to look for comp_object1 in dataset2, even tho it is uniquely named?

Ideas gratefully received !

Yes, I'm pretty sure you can do what you want.

I'll assume that the fields in dataset2 are named comp_object1, comp_object2, etc. I will also assume that dataset2 has one row -- because if it has multiple rows to match dataset1's rows, you should put these columns in the *same* dataset.

In the table based on the first dataset, you can use expressions like this for column #1 -- and I'm pretty sure the scope is case-sensitive, so you may have to play with what I'm suggesting here

Code Snippet

=iif(Fields!object1.Value <> First(Fields!comp_object1.Value, "dataset2") , "Red", "SkyBlue")

To use the expression builder to do this, notice that on the left side there is a "Datasets" item in the list. If you select it, then the middle list has your datasets listed. Click on dataset2 in the middle list and you will see the appropriate items in your right-side list of possible fields...

>L<

|||Thanks, that worked!|||

hi,

this is works perfactly........ but i have another issue that i want to display 2 different dataset's fields in a single table. i can put 2nd database's field in table but it can put only with aggregate functions like (first, last...etc.) but i want to put it directly.........bcz if i put it with first function i can't get other records............. can u help me?

|||

Basically, except for aggregates that tell it what to display, the processing engine has no way to figure out what record from the other dataset to display in each row <s>.

You can either write a query that combines the data into one dataset, so that you provide the relationship yourself, or you can use a subreport, passing a parameter from the parent dataset that indicates what data to display in the subreport.

Alternatively you can probably do something in code to pull the data from the other dataset (I haven't tried this because I don't really see the point, but it would probably work) .

You can also nest a data region (another table or list or matrix) inside a group row for a table -- and this other data region has its own dataset -- but I gather this isn't what you have in mind.

>L<

|||

hi lisa,

Thx a lot for suggestion..............i have tried to put another data region in table with aggregate function but still its not works and gives error........ do u have any otherway?

|||

Well, I'm not sure I understand exactly what you tried so I can't explain or help <s>.

Tell us about your two datasets.

Tell us what data region you put in, and where exactly you put it, etc.

Tell us what the error was...

>L<

Conditional formatting based on second dataset

I am trying to create a report which has conditional formatting.

The primary dataset is a view of objects with several values

eg

object1,0,4,0,1

object2,0,3,1,1

The secondary dataset is the comparison table and just contains the values

eg

0,3,1,1

I'd like to conditionally format the values based on the comparison table but when I create an expression comparing to the second dateset

eg

=iif(Fields!object1.Value <> Fields!comp_object1.Value , "Red", "SkyBlue")

i get

Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

Not sure if there is a way to tell the expression to look for comp_object1 in dataset2, even tho it is uniquely named?

Ideas gratefully received !

Yes, I'm pretty sure you can do what you want.

I'll assume that the fields in dataset2 are named comp_object1, comp_object2, etc. I will also assume that dataset2 has one row -- because if it has multiple rows to match dataset1's rows, you should put these columns in the *same* dataset.

In the table based on the first dataset, you can use expressions like this for column #1 -- and I'm pretty sure the scope is case-sensitive, so you may have to play with what I'm suggesting here

Code Snippet

=iif(Fields!object1.Value <> First(Fields!comp_object1.Value, "dataset2") , "Red", "SkyBlue")

To use the expression builder to do this, notice that on the left side there is a "Datasets" item in the list. If you select it, then the middle list has your datasets listed. Click on dataset2 in the middle list and you will see the appropriate items in your right-side list of possible fields...

>L<

|||Thanks, that worked!|||

hi,

this is works perfactly........ but i have another issue that i want to display 2 different dataset's fields in a single table. i can put 2nd database's field in table but it can put only with aggregate functions like (first, last...etc.) but i want to put it directly.........bcz if i put it with first function i can't get other records............. can u help me?

|||

Basically, except for aggregates that tell it what to display, the processing engine has no way to figure out what record from the other dataset to display in each row <s>.

You can either write a query that combines the data into one dataset, so that you provide the relationship yourself, or you can use a subreport, passing a parameter from the parent dataset that indicates what data to display in the subreport.

Alternatively you can probably do something in code to pull the data from the other dataset (I haven't tried this because I don't really see the point, but it would probably work) .

You can also nest a data region (another table or list or matrix) inside a group row for a table -- and this other data region has its own dataset -- but I gather this isn't what you have in mind.

>L<

|||

hi lisa,

Thx a lot for suggestion..............i have tried to put another data region in table with aggregate function but still its not works and gives error........ do u have any otherway?

|||

Well, I'm not sure I understand exactly what you tried so I can't explain or help <s>.

Tell us about your two datasets.

Tell us what data region you put in, and where exactly you put it, etc.

Tell us what the error was...

>L<

Concurrent MSDE SQL and SQL 2000/2005

We have two servers that need to be upgraded. One has an app that uses the
MSDE version of SQL. The second uses SQL 2000. Neither has very many users
.
Could we purchase a single new server, and run both versions of SQL on it
concurrently? Would it make a difference if the SQL 2000 is eventually
upgraded to SQL 2005, while the MSDE version stays at 2000 instead of going
to SQL Express?
Thanks!
EmilyYou could run both versions on one server, but it would require having
a named instance, which adds a little bit more hassle. One would be
the default instance and you would connect to it as per normal but the
for the named instance you would have to connect to it as
ComputerName\InstanceName. My recommendation would be to just install
SQL 2000/2005 on your new server and host all databases on the one
instance. Is their any particular reason you want them on seperate
instances?|||Thank you for your response, Paul. My hesitation in using two instances is
simply that I am new to SQL, and new to these applications, and there isn't
anyone here who knows much about them. Having never had SQL training, I am
a
little reluctant to change any of the default settings. However, the
financial people don't want to buy two servers. The vendors are not very
helpful.
"Paul T." wrote:

>
> You could run both versions on one server, but it would require having
> a named instance, which adds a little bit more hassle. One would be
> the default instance and you would connect to it as per normal but the
> for the named instance you would have to connect to it as
> ComputerName\InstanceName. My recommendation would be to just install
> SQL 2000/2005 on your new server and host all databases on the one
> instance. Is their any particular reason you want them on seperate
> instances?
>|||If you are reluctant to change default settings, and you have not had
much SQL training your best bet is to continue using the two servers
and upgrade either as needed. If you wanted them on the same server
you would have to change the default settings of at least one instance
as it would need an instance name. If you moved the msde databases
onto the SQL 2000 server, you would need to at least know how to attach
and detach the databases and ensure that all the logins still have the
proper access. If there is no business reason that these applications
are running on seperate servers then I would look into learning enough
about SQL Server to put the databases on the SQL Server 2000 box
together.|||You could run both versions on one server, but it would require having
a named instance, which adds a little bit more hassle. One would be
the default instance and you would connect to it as per normal but the
for the named instance you would have to connect to it as
ComputerName\InstanceName. My recommendation would be to just install
SQL 2000/2005 on your new server and host all databases on the one
instance. Is their any particular reason you want them on seperate
instances?|||Thank you for your response, Paul. My hesitation in using two instances is
simply that I am new to SQL, and new to these applications, and there isn't
anyone here who knows much about them. Having never had SQL training, I am
a
little reluctant to change any of the default settings. However, the
financial people don't want to buy two servers. The vendors are not very
helpful.
"Paul T." wrote:

>
> You could run both versions on one server, but it would require having
> a named instance, which adds a little bit more hassle. One would be
> the default instance and you would connect to it as per normal but the
> for the named instance you would have to connect to it as
> ComputerName\InstanceName. My recommendation would be to just install
> SQL 2000/2005 on your new server and host all databases on the one
> instance. Is their any particular reason you want them on seperate
> instances?
>|||If you are reluctant to change default settings, and you have not had
much SQL training your best bet is to continue using the two servers
and upgrade either as needed. If you wanted them on the same server
you would have to change the default settings of at least one instance
as it would need an instance name. If you moved the msde databases
onto the SQL 2000 server, you would need to at least know how to attach
and detach the databases and ensure that all the logins still have the
proper access. If there is no business reason that these applications
are running on seperate servers then I would look into learning enough
about SQL Server to put the databases on the SQL Server 2000 box
together.

Concurrent MSDE SQL and SQL 2000/2005

We have two servers that need to be upgraded. One has an app that uses the
MSDE version of SQL. The second uses SQL 2000. Neither has very many users.
Could we purchase a single new server, and run both versions of SQL on it
concurrently? Would it make a difference if the SQL 2000 is eventually
upgraded to SQL 2005, while the MSDE version stays at 2000 instead of going
to SQL Express?
Thanks!
EmilyYou could run both versions on one server, but it would require having
a named instance, which adds a little bit more hassle. One would be
the default instance and you would connect to it as per normal but the
for the named instance you would have to connect to it as
ComputerName\InstanceName. My recommendation would be to just install
SQL 2000/2005 on your new server and host all databases on the one
instance. Is their any particular reason you want them on seperate
instances?|||Thank you for your response, Paul. My hesitation in using two instances is
simply that I am new to SQL, and new to these applications, and there isn't
anyone here who knows much about them. Having never had SQL training, I am a
little reluctant to change any of the default settings. However, the
financial people don't want to buy two servers. The vendors are not very
helpful.
"Paul T." wrote:
>
> You could run both versions on one server, but it would require having
> a named instance, which adds a little bit more hassle. One would be
> the default instance and you would connect to it as per normal but the
> for the named instance you would have to connect to it as
> ComputerName\InstanceName. My recommendation would be to just install
> SQL 2000/2005 on your new server and host all databases on the one
> instance. Is their any particular reason you want them on seperate
> instances?
>|||If you are reluctant to change default settings, and you have not had
much SQL training your best bet is to continue using the two servers
and upgrade either as needed. If you wanted them on the same server
you would have to change the default settings of at least one instance
as it would need an instance name. If you moved the msde databases
onto the SQL 2000 server, you would need to at least know how to attach
and detach the databases and ensure that all the logins still have the
proper access. If there is no business reason that these applications
are running on seperate servers then I would look into learning enough
about SQL Server to put the databases on the SQL Server 2000 box
together.

Concatenating SQL query results on one line.

Guys,
Here is an example.
I have a table with hors_id, hors_name.
Another table has hors_id, owner_name.
That second table contains multiple entries for that hors_id, as there can be multiple owners.
How do I construct a query that would return the following info on one line:
hors_id, hors_name, owner_name(1), owner_name(2), owner_name(3).
The logic for the solution seems as follows:
Query should return distinct id and name for the horse, loop through the owners in the second table, and append the owners to a variable while the hors_id is the same.
Any suggestions of a generic code to implement?
I tried different coding, so far doesn't work.
Thanks.Originally posted by bigfootguy
Guys,
Here is an example.
I have a table with hors_id, hors_name.
Another table has hors_id, owner_name.
That second table contains multiple entries for that hors_id, as there can be multiple owners.
How do I construct a query that would return the following info on one line:
hors_id, hors_name, owner_name(1), owner_name(2), owner_name(3).
The logic for the solution seems as follows:
Query should return distinct id and name for the horse, loop through the owners in the second table, and append the owners to a variable while the hors_id is the same.
Any suggestions of a generic code to implement?
I tried different coding, so far doesn't work.
Thanks.

Hi BigFoot,

Since SQL Server does not support Cross-Tab constructs, you will have to do some more work. I worked out a solution, but for the Customers and Orders table in the NorthWind demo database; so please translate my answer into your problem.

First of all, you have to know, how much Orders you may expect at least. You can query the actual maximum by

SELECT MAX(Num)
FROM (SELECT COUNT(*) AS Num, CustomerID
FROM Orders
GROUP BY CustomerID) T

As much orders you expect, as much views you have to create:

1) CREATE VIEW Order1 AS SELECT MAX(orderid) AS ID, customerid FROM Orders GROUP BY customerid

2) CREATE VIEW Orders2 AS SELECT MAX(orderid) AS ID, orders.CustomerID FROM Orders, Orders1 WHERE orders.CustomerID = Orders1.CustomerID AND Orders.OrderID < Orders1.ID GROUP BY orders.CustomerID

3) CREATE VIEW Orders3 AS SELECT MAX(orderid) AS ID, orders.CustomerID FROM Orders, Orders2 O WHERE orders.CustomerID = O.CustomerID AND Orders.OrderID < O.ID GROUP BY orders.CustomerID

Got the point? Select one or no order by customer per view, excluding the orders already selected in earlier views.

Having created those views, you may select you required result as :

SELECT C.CustomerID, C.CompanyName, O1.ID AS Order1,
O2.ID AS Order2, O3.ID AS Order3
FROM Customers C LEFT OUTER JOIN
(Orders1 O1 LEFT OUTER JOIN
(Orders2 O2 LEFT OUTER JOIN
Orders3 O3 ON O2.CustomerID = O3.CustomerID) ON
O1.CustomerID = O2.CustomerID) ON
C.CustomerID = O1.CustomerID

This works fine if your expected number of orders can be limited. If not, you will have to write a stored procedure returning your recordset.

Cheers :p|||If your hors_owners table has some field for categoring the owners (say, owner_type) for each hors, you can write a cross-tab query that will place each owner_type in its own column. You could use any type description you want, as long as each hors has at most one of each type. For example, "Primary_Owner", "Secondary_Owner", "Investor", or even an ID like "1", "2", "3"... If you search books on-line for "Crosstab", they show a good example of how to write such a query. If you still have problems, post them to the forum.

If you can't create an owner_type field, well that that is a "hors of a different color". (I couldn't resist...). This is one of those rare situations where I would recommend using a cursor, because you won't need to hard-code the number of owners. If you aren't returning hundreds or thousands of hors records, then consider putting the cursor logic in a user-defined function named something like "udf_HorsOwner_String". Your end-query could then be as simple as:

Select *, dbo.udf_HorsOwnerString(hors_id) from tbl_hors

If you need more guidance, post again when you have an idea of what direction you want to take with this.

blindman