Losing config file between child packages and *$##@!- Passwords!

I have a parent package that uses Execute Package Task against three child packages. They are all setup to use the same configuration file, and the packages are all located in the same folder. Purpose of the configuation is to use the same connection strings for each child package.

I removed the path from the config. file specification to facilitate deployment. During debugging, the child packages cannot find the config file when they are run via the parent unless the full path is specified. If run standalone, a child package can find the config. file without the path. As I said, all dtsx filaes and the config file is in the same folder.

Also, the password-removal issue is killing me in development. Everytime I touch the config. file, it removes the password from the connection string. I have "protection level" set to "don't save sensitive" - I'd appreciate help with this too.

thanks

Mark Challen wrote:

I have a parent package that uses Execute Package Task against three child packages. They are all setup to use the same configuration file, and the packages are all located in the same folder. Purpose of the configuation is to use the same connection strings for each child package.

I removed the path from the config. file specification to facilitate deployment. During debugging, the child packages cannot find the config file when they are run via the parent unless the full path is specified. If run standalone, a child package can find the config. file without the path. As I said, all dtsx filaes and the config file is in the same folder.

Also, the password-removal issue is killing me in development. Everytime I touch the config. file, it removes the password from the connection string. I have "protection level" set to "don't save sensitive" - I'd appreciate help with this too.

thanks

When you have it set to "don't save sensitive" that's what's supposed to happen. It's not saving sensitive information.|||As far as your paths go, you have to use absolute paths. So, you might be better off using an environment variable or something to store the path to the config file.

Relative paths have been requested: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=253232|||

Phil Brammer wrote:

When you have it set to "don't save sensitive" that's what's supposed to happen. It's not saving sensitive information.

Do you happen to know which setting will allow the saving of a password so I can work on the packages in development without having to reset the password everytime I touch the configuration? I can understand removing the password when building the solution for deployment, having it auto-magically disappear while staying in the designer it makes no sense.

|||http://msdn2.microsoft.com/en-us/library/ms141747.aspx|||

Mark Challen wrote:

I removed the path from the config. file specification to facilitate deployment. During debugging, the child packages cannot find the config file when they are run via the parent unless the full path is specified. If run standalone, a child package can find the config. file without the path. As I said, all dtsx filaes and the config file is in the same folder.

In my current project I am using that technique; removing the path of the config file from the package configuration and it has worked fine in both ways when called from the aprent package or standalone....

Config file has to be in same folder than the apckages though.

|||

Thanks for the link Phil. Doesn't answer the question, and I'm getting increasingly frustrated by the lack of control with SSIS. Not to get off on a rant, but I personally would rather be responsible for setting security, not having MS dictate that I encrypt everything.

Anyhow, I have found a workaround - once the config. file is established on the parent I set it's "Read-only" property to true in the file system. A low-tech approach, but it seems to work.

The issue of the requirement for an absolute path for the config. file is going to be a showstopper for me (no way am I setting an environment variable; I don't see the thinking behind that - how do I establish the variable in the first place?). I will probably execute my child packages independently from the command line from my own custom wrapper code, passing in my connection strings on the command line. Too bad, I wanted to use the Configuration feature and I spent a lot of time trying to make it work. This seems to be endemic to SSIS (like checkpointing) - a cool feature at first glance, but try to use it and you hit a brick wall. </Rant>

Rafael Salas wrote:

In my current project I am using that technique; removing the path of the config file from the package configuration and it has worked fine in both ways when called from the aprent package or standalone....

Config file has to be in same folder than the apckages though.

Are you sure the package is successfully locating the config file and overriding the connection strings, or is it defaulting back to your original connection manager settings in the child?

|||

Mark Challen wrote:

The issue of the requirement for an absolute path for the config. file is going to be a showstopper for me (no way am I setting an environment variable; I don't see the thinking behind that - how do I establish the variable in the first place?).

You set up the environment variable by way of the system properties in Windows. Once it's set up, you simply reference it thereby making your configurations portable. This is a good way, in my opinion to handle changes across environments that have different configurations (file paths, etc...)

For me, I use SQL Server based configurations and environment variables. I've pretty much got everything setup so that I never have to make changes to a package when promoting it from development into QA and finally into production. Even though file paths may change (commonly C: drive to D: drive, perhaps), the packages continue to work with little interaction. This helps given that we can't promote to production -- another group does that. (Thank you Sarbanes-Oxley!)|||

Mark Challen wrote:

Are you sure the package is successfully locating the config file and overriding the connection strings, or is it defaulting back to your original connection manager settings in the child?

I am 100% sure that it works; actually it is very easy to see if it is not; each time a configuration does not take place; a warning is generated and can be seen in the progress tab of BIDS or in the logging table/file when logging is enable. I rather prefer using indirect configurations; but I have gotten a client where Env variable are not an option....

loop in stored procedure?

I have a situation where there is one table with parent and child nodes
i.e.
catID, ParentID
i would like to make a query to find out if an entry resides in a certain
category.
for instance, say i have
Root
--Category 1
--Child 1A
--Child 1B
--Child 1C
--Child 1Ca
if i know the CatID for the Child 1Ca node, how can i write a query that is
able to tell me that it's ultimtely a child of the root nde (Category 1)?
Keep in mind that Category 1 is not always the root node in the tree.Rob,
thanks for your help. this is exactly what i needed.
best wishes.
"Rob Farley" wrote:
> Whether you choose a stored procedure or a function depends on how you're
> using it. If you have a piece of C# code which wants to find out if a
> particular item is in a particular category, then use a stored procedure.
If
> you want to be able to use it in queries, then a function. Here's a functi
on
> which returns a bit (either 0 or 1) to indicate if an item is an ancestor
in
> the tree.
> --First create the table and populate it with some samples.
> create table fab_tree (parentid int, childid int)
> insert into fab_tree values (0,1)
> insert into fab_tree values (0,2)
> insert into fab_tree values (0,3)
> insert into fab_tree values (1,10)
> insert into fab_tree values (1,11)
> insert into fab_tree values (1,12)
> insert into fab_tree values (2,20)
> insert into fab_tree values (2,21)
> insert into fab_tree values (2,22)
> insert into fab_tree values (3,30)
> insert into fab_tree values (3,31)
> insert into fab_tree values (3,32)
> insert into fab_tree values (30,300)
> insert into fab_tree values (30,301)
> insert into fab_tree values (30,302)
> insert into fab_tree values (50,500)
> go
> create function [dbo].[isancestor](@.parentid int, @.childid int) returns bit as
> begin
> declare @.res bit
> set @.res = 0
> declare @.node int
> set @.node = @.childid
> --Keep looking until we get to the end, or the parent
> while (@.node not in (0, @.parentid))
> begin
> select @.node = parentid
> from fab_tree
> where childid = @.node
> --If the current child has no parent, then pretend we got to the top of
> the tree
> if (@.@.rowcount = 0)
> set @.node = 0
> end
> --Check the success condition
> if (@.node = @.parentid)
> set @.res = 1
> return @.res
> end
> go
> select 1,10,dbo.isancestor(1,10)
> select 1,20,dbo.isancestor(1,20)
> select 3,300,dbo.isancestor(3,300)
> select 5,500,dbo.isancestor(5,500)
> select 9,700,dbo.isancestor(9,700)
>
> But I'm guessing you probably want something that will find the category o
f
> a particular item. In which case, how about :
> create function dbo.getCategory(@.childid int) returns int as
> begin
> declare @.node int
> set @.node = @.childid
> declare @.nodeparent int
> set @.nodeparent = @.node
> --Keep looking until we get to the top of the tree
> while (@.nodeparent > 0)
> begin
> --We need to now check the parent of the current parent
> set @.node = @.nodeparent
> select @.nodeparent = parentid
> from fab_tree
> where childid = @.node
> --If the current child has no parent, then pretend we got to the top of
> the tree
> if (@.@.rowcount = 0)
> begin
> set @.node = 0
> set @.nodeparent = 0
> end
> end
> return @.node
> end
> go
> select dbo.getcategory(childid), *
> from fab_tree
> That way, you can ask within a query for the category of an item in the tr
ee.
> Hope this helps,
> Rob
>
> "Fabuloussites" wrote:
>