Network integration for fresh install of 2005

I am working as a consultant on a project to upgrade an Access 2000 db to SQL Server. My client has bought off on everything, and they're ordering a new dedicated box this week. They're getting a rackmount machine running Windows Server and SQL Server 2005.

My expectation was that their IT people would take care of administrative & security issues such as setting the machine up, installing & starting SQL Server and configuring the proper accounts. Suprisingly though, they're going to mount the machine in the rack, turn it on and let me do all of this.

I'm new at SQL Server, but I'm comfortable working with database objects (tables, views, etc...) I'm not so comfortable with the networking or administrative side of things. It took me a couple of hours to be able to connect to the DB on my desktop machine over my home network. You can see why I'm intimidated at the prospect of getting things integrated & running on a pretty large corporate network.

Maybe I'm overblowing this, but this just gives me the willies.

Are my fears justified? Am I going to be ok if I just follow the standard procedures for allowing TCP/IP connections to the server? Am I going to have to deal with firewalls? (I think I know the answer - "it depends on their network.." - that's what I'm scared of.. I don't know anything about their network, and I don't know enough about networking in general to figure things out..)

Are there any other best practices that I should follow for a fresh install?So much depends on their network configuration that only their network people can give you a complete and correct answer to your questions. With that said, the general setup isn't difficult or complicated, the only problems I've ever run into arise when coping with installation specific problems.

Specific points to consider. If you don't know that you or your project need it, don't enable it. This especially applies to network protocols (only enable TCP/IP unless there is compelling reason for another protocol). If you don't need SQL Authentication, only select Windows Authentication.

Their network administrators are giving you a "blank check" to set things up as you'd like. This is idiocy on their part, but you should take advantage of it! Get the box running, in a minimal configuration. Set the box up as a "lone wolf" machine, then only after you are done getting things set up to suit yourself tell them you need a domain administrator login to join the box to their domain (this should put them into an outright panic), then stand back and watch the fun as they backpeddle furiously! At that point, you already have a tested, working configuration... Their only choices are to either a) give you the network equivalent of god-like powers, or b) pick up the ball that you've handed them and very quickly figure out how to run with it.

Note: This is evil, but they have brought it on themselves. The network administrators have already done one of the most stupid things that they are capable of doing, in order to force you to do all of their legwork for them. You are simply doing what they've asked, and will be presenting them with the most awful choice possible, but one that they ought to know is coming because there isn't any alternative.

-PatP|||Sorry I haven't been back for a few days..

I really don't want to do anything to create any friction with them. So far they've been fairly cooperative in allowing an outside consultant (me) to do development work for a department that's under their jurisdiction. They could easily send one email to upper management that would a) make my primary client's life miserable and b) completely cut me out of any work for them in the future.

After talking with one of their network techs yesterday, the server is up & running. It's 2005, running on a Windows Server 2003 Virtual Machine. She's going to be there, so hopefully she can take care of any network issues that arise. From what she said yesterday, they don't have any internal firewalls, so that should simplify things.

I'm going over there in a few hours to establish my login account, create a test database, and verify connectivity between it and an Access installation on an end user's machine.

Any final words of wisdom?

Nested Parent-Child Packages

I have a fairly simple SSIS project that has nested parent-child packages.
I am trying to find the best way to manage the connections strings so as to make the package portable across machines and environments. Currently there is one "master" package which calls 6 child packages. 1 of these child package calls 3 child packages of its own.

For the database connections, I've settled on creating a standardized .dtsConfig file for each server/login. This is a relatively small number (intially 8) that I don't expect to grow much.

I've taken a different approach for the file-system connections used by Execute Package components that call the child packages. For each package that has child packages, I store all the connection strings (paths) to the child packages in a single .dtsConfig file. This works well for the top-level "master" package where I can pass in the .dtsConfig file (that has the paths to the child packages) as a run-time option in the Execute Package Utility.

However, this approach seems to fall apart for the 2nd generation package that in turn call 3rd generation packages because I don't know how to get the .dtsConfig file (with the 3rd generation .dtsx package paths) path to this downstream dtsx package.

Though I'm sure there are others, the only two solutionsI can think of now are
(a)don't nest packages beyond 1 parent/child relationship -- not really an option or
(b)Store the path of .dtsConfig files for each .dtsx package as an environment variable on each machine. This option is unappealing because it would require adding an environment variable for every .dtsx package that has child packages. I don't think it would take long for this to grow into a large number, that would make managing environment variables cumbersome.

So far my experience with SSIS has been that there was a simple solution for each scenario I had. So this hoop jumping I'm going through seems to indicate I am just missing something.

Is there a better way I am just not getting?

In a similar situation, I put all the 3rd level child packages in the same folder. Then I used a parent package configuration to pass a root path variable from the 1st level master to the second level master. I used an expression on the connectionstring property for each file system connection that concatenated the root path with the name of the package.

It worked pretty well for me, but there are some limits with parent package configurations, since they are evaluated later in the process than other types of configurations (hopefully something that will be addressed in Katmai).