Configure a Child Package to use the configuration file of a Parent Package?

Hi guys,

Here's the deal. I have a child package, (say, pack01.dtsx), which uses a dtsconfig file for its connection string, which can be called from other packages, but which also can be called by itself.

However I also have another package (say, pack02.dtsx) which uses the same dtsconfig file for its connection string. It calls on pack01.dtsx.

When I use DTEXECUI and run pack01.dtsx, specifying the proper .dtsconfig file, it goes well. But when I try and run pack02.dtsx, an error occurs saying pack01.dtsx connection cannot be established.

How do I pass the connectionstring being used by pack02 to pack01, without having to remove the configuration file setting of pack01? Can a Parent Package configuration and a configuration file try and map to the same property?

Hope someone could help. Thanks.Still no idea on this guys?

Essentially I just want the child package to use and consume the same .dtsConfig file as the parent package. How do I do that?|||You can use "Parent package variable" configuration type in the child, or just set the same configuration file as the option for a configuration in the child. You cannot pass a entire configuration definition between parent and child.|||

DarrenSQLIS wrote:

You can use "Parent package variable" configuration type in the child, or just set the same configuration file as the option for a configuration in the child. You cannot pass a entire configuration definition between parent and child.

This is exactly what I did, really. And besides, when you try to pass a connectionstring through a parent package variable to a child, for some reason or another the password property value gets lost.

Any other ideas?|||

Ok so connection strings are really built from properties, and the password property will be write only, so that kind of makes sense. If you could read a connection string for a configuration to get the password, it would be a security risk, regardless of if this is for a configiuration or not.

Use a variable instead, set the variable from the configuration in the parent. Use a property expression to assign the variable value to the connection. Cannot see anything wrong with that as a solution, apart from the obvious security concerns.

|||

DarrenSQLIS wrote:

Ok so connection strings are really built from properties, and the password property will be write only, so that kind of makes sense. If you could read a connection string for a configuration to get the password, it would be a security risk, regardless of if this is for a configiuration or not.

Use a variable instead, set the variable from the configuration in the parent. Use a property expression to assign the variable value to the connection. Cannot see anything wrong with that as a solution, apart from the obvious security concerns.

I guess I'm compelled to use Windows Authentication for this case, just so I don't need to mess with the password.

Anyway, thanks |||If there is a chance that you can use Windows Auth, then I would strongly recomend it, for this any many other reasons, it just makes life easier. Good luck.

Configuration for Configuration Package? Is this Possible?

Hi Guys,

Here's the current scenario:

I am creating a windows application to manage my ssis packages.

My SSIS packages use package configurations, and by default connect to the "SSIS Configurations" table in sql server.

However, I want it to connect to a view instead of this table, so i create a view "vwSSIS_Configurations" and point the package configuration to use the view in sql server.

The application SHOULD be capable of altering the package configuration's configuration and switch its connection between the default "ssis configurations" table, and the view.

Is this possible? I cannot think of a way which I can have my application switch the package configuration's connection.

Thanks,

kervy

There are a number of ways. I would store the connection string of the connection manager in an XML configuration file.

-Jamie

|||

it's not the connection string of the connection manager, but the connection to the package configuration table,

currently my package configurations are looking in the "SSIS Configurations" table.. but I want my application to let the dtsx switch to another configuration table named "SSIS Configurations2" both have the same schema but different values. is there a "configuration" property for the configuration table connection?

|||

Ah OK. Well I've just looked at the setup screen for SQL Server configs for the first time in my life and seen that you can store the config location in an environment variable. I don't know exactly what it does but have you explored that? BOL should contain details to help you.

-Jamie

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