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.

owner advice

I'm not an expert so please be gentle if I say anyting incorrect in here.
Just looking for advice. Not any specific problem right now. I'm working on
a db that will be run independently in several offices. The owner of every
object is currently dbo. I move it (by doing backup and restore) between me
and another developer. We're both logging in as sa and don't have any
problems. We also don't use dbo in front of any objects when we reference
them in procedures, views and such (except for when referencing UDF's which
seem to require it). Are there any issues we need to be aware of with
respect to this? Are we ok with everythign being dbo and not qualifying
objects with it? We plan on creating another user (not sa) that the front
end app will use to get to the db. That sql user name and pwd will be coded
into the front end before it's compiled. Our tests seem to inidcate that
this works fine but we just want to be sure.
One reason this came to my attention is that I ran into an issue with
another db that I didnt' work on that got moved. I guess the object (an SP I
think) had been created by some other owner and when I went in to modify it
I couldnt' save it without putting dbo in front of every table in the SP. So
I did that but never really looked into what was going on.
Like I said, just looking for advice on this area. I know some of you out
there can help.
Thanks,
Keith"Keith G Hicks" <krh@.comcast.net> wrote in message
news:%23Fs5ugYpFHA.1412@.TK2MSFTNGP09.phx.gbl...
> I'm not an expert so please be gentle if I say anyting incorrect in here.
> Just looking for advice. Not any specific problem right now. I'm working
> on
> a db that will be run independently in several offices. The owner of every
> object is currently dbo. I move it (by doing backup and restore) between
> me
> and another developer. We're both logging in as sa and don't have any
> problems. We also don't use dbo in front of any objects when we reference
> them in procedures, views and such (except for when referencing UDF's
> which
> seem to require it). Are there any issues we need to be aware of with
> respect to this? Are we ok with everythign being dbo and not qualifying
> objects with it? We plan on creating another user (not sa) that the front
> end app will use to get to the db. That sql user name and pwd will be
> coded
> into the front end before it's compiled. Our tests seem to inidcate that
> this works fine but we just want to be sure.
> One reason this came to my attention is that I ran into an issue with
> another db that I didnt' work on that got moved. I guess the object (an SP
> I
> think) had been created by some other owner and when I went in to modify
> it
> I couldnt' save it without putting dbo in front of every table in the SP.
> So
> I did that but never really looked into what was going on.
> Like I said, just looking for advice on this area. I know some of you out
> there can help.
> Thanks,
> Keith
>
What you're doing there seems OK to me, basically everything is owned by
dbo, and it's all in the same database - as simple as you can get.
One thing that I would suggest that you look at when hooking in your
front-end is perhaps not creating a specific user, but instead allowing
people to login to the database using windows authentication - don't give
the user any access, but instead setup an application role which your
program uses, this way your maximise the security of your DB and
application.
Regards
Colin Dawson
www.cjdawson.com|||Also, if you are not the owner and you are executing a stored procedure, you
WILL want to qualify it with dbo. There is a performance hit when you are
not the owner of a stored procedure and you execute it without qualifying
the owner.
See http://support.microsoft.com/defaul...kb;en-us;243586 (look for
"Best Practice")
See also http://support.microsoft.com/defaul...kb;en-us;263889
(look for "More Information")
Mike
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:u4GNe.92710$G8.85208@.text.news.blueyonder.co.uk...
> "Keith G Hicks" <krh@.comcast.net> wrote in message
> news:%23Fs5ugYpFHA.1412@.TK2MSFTNGP09.phx.gbl...
>
> What you're doing there seems OK to me, basically everything is owned by
> dbo, and it's all in the same database - as simple as you can get.
> One thing that I would suggest that you look at when hooking in your
> front-end is perhaps not creating a specific user, but instead allowing
> people to login to the database using windows authentication - don't give
> the user any access, but instead setup an application role which your
> program uses, this way your maximise the security of your DB and
> application.
> Regards
> Colin Dawson
> www.cjdawson.com
>|||Hi
If you move a database and the SIDs don't match the original logins, then
you will get orphaned users as described in
121120120" target="_blank">http://support.microsoft.com/defaul...r />
121120120
John
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:%23Fs5ugYpFHA.1412@.TK2MSFTNGP09.phx.gbl...
> I'm not an expert so please be gentle if I say anyting incorrect in here.
> Just looking for advice. Not any specific problem right now. I'm working
> on
> a db that will be run independently in several offices. The owner of every
> object is currently dbo. I move it (by doing backup and restore) between
> me
> and another developer. We're both logging in as sa and don't have any
> problems. We also don't use dbo in front of any objects when we reference
> them in procedures, views and such (except for when referencing UDF's
> which
> seem to require it). Are there any issues we need to be aware of with
> respect to this? Are we ok with everythign being dbo and not qualifying
> objects with it? We plan on creating another user (not sa) that the front
> end app will use to get to the db. That sql user name and pwd will be
> coded
> into the front end before it's compiled. Our tests seem to inidcate that
> this works fine but we just want to be sure.
> One reason this came to my attention is that I ran into an issue with
> another db that I didnt' work on that got moved. I guess the object (an SP
> I
> think) had been created by some other owner and when I went in to modify
> it
> I couldnt' save it without putting dbo in front of every table in the SP.
> So
> I did that but never really looked into what was going on.
> Like I said, just looking for advice on this area. I know some of you out
> there can help.
> Thanks,
> Keith
>