Package setup, configurations and testing

Hi,

I'm looing for advice on how to organize my SSIS packages. I'll quickly describe my requiremenst, and then outline how I see the solution.

1. Should work for development in BI Studio. Design time experience should be good, so static schema information should be available in all steps. Packages should run in debug mode inside BI Studio.

2. Should be able to run automated tests (which I gather is difficult. See proposed solution below). Tests should run both on a test machine, in batch mode, and from BI Studio.

3. Should work for deployment.

The packages should be fine-grained. Automated tests will be implemented as SSIS packages that call the packages to be tested via an Execute Package Task. There will be one test package per testcase. A test will look like: [Create Schema] -> [Load test data]->[Execute Package to be tested]->[Verify expected results]->[Teardown].

There will be one master test package that executes all the tests.

For this I need a) some way to vary the connections for the packages depending on whether they are run from a testcase or from the normal control flow. I'm considering making the connection strings configurations based on a parent package variable. Other configuration types strike me as not suitable since developers will want to run packages both as part of normal flow and as testcases on their machines. Is this a viable strategy? Any drawbacks?

b) need to organize testcases some way. I tried putting tests in a subfolder of the project to minimize the clutter, but BI studio shows them at the root node. I tried putting a new project (for containing the tests) in the same folder, and adding the packages (via add existing item), but BI Studio insists of adding copies (!) of the package files, rather than reusing the existing files. Any suggestions?

I welcome any feedback, suggestions of alternate strategies, etc.

/Anders

AndersI wrote:

For this I need a) some way to vary the connections for the packages depending on whether they are run from a testcase or from the normal control flow. I'm considering making the connection strings configurations based on a parent package variable. Other configuration types strike me as not suitable since developers will want to run packages both as part of normal flow and as testcases on their machines. Is this a viable strategy? Any drawbacks?

One possible drawback is that when the developers run in BIDS (not the test case), the packages will use the connection strings coded in the packages. I like using configurations for my packages even in development.

Another approach might be to use another type of configuration for the packages in dev, but when you run them from the test pacakges, use Execute Process instead of Execute Package. You can call DTEXEC from Execute Process, and override the connection strings by using the /CONN switch, which should take precedence over the configurations.

AndersI wrote:

b) need to organize testcases some way. I tried putting tests in a subfolder of the project to minimize the clutter, but BI studio shows them at the root node. I tried putting a new project (for containing the tests) in the same folder, and adding the packages (via add existing item), but BI Studio insists of adding copies (!) of the package files, rather than reusing the existing files. Any suggestions?

Can't you just put the test case packages in one project, and leave the real ones in another project?

|||

Thanks!

I'm using configurations for the master packages to set the connection variables, and then passing them down to child packages via parent package variable configurations.

The reason I want the packages-to-be-tested in the same project as the testcase packages is so that you can run them in the BI studio debugger. If the package called via Execute Package is not in the same project, then the debugger won't display it. [Edit] Scratch that, it works. Must have been a user error on my part... [/Edit]

The Execute Package task uses a connection with an absolute path to refer to the called package, right? What is the recommended practice for handling this in a development team where developers may have the files checked out to different locations on their machines? I'm considering adding a "PackagePath" variable (also stored in the configuration file) to the master packages, and using expressions on the form @.[User:ackagePath] + "ChildPackage.dtsx" for the connectionstring on the file connection manager. Has anyone else tried this?

/Anders

|||

AndersI wrote:

The Execute Package task uses a connection with an absolute path to refer to the called package, right? What is the recommended practice for handling this in a development team where developers may have the files checked out to different locations on their machines? I'm considering adding a "PackagePath" variable (also stored in the configuration file) to the master packages, and using expressions on the form @.[User:ackagePath] + "ChildPackage.dtsx" for the connectionstring on the file connection manager. Has anyone else tried this?

Yes, I've done exactly that. It works well.