Best Practices Question - how do you execute multiple packages?

I have 200+ plus packages that need to be flexible in how they are run. For example, an end user may choose to run packages 1,2,3 and the next end user may choose to run packages 2,3,7, etc. Prior ro running a package, I set an "instance id" inside the group of packages so I can tie them all together in the logfile - I know that packages 1,2,3 were all run as group and that's distinct from packages 2,3,7 that were run in a differnt group.

Initially I embarked on a scenario where I had a queue table that loaded up the packages to be run and then had a little c# app that read the queue, generated the "instance id" and ran all the packages (either thru dtexec.exe or the Microsoft.SqlServer.DTS.Runtime). But now I wonder if using a master package that uses the Execute Package Task is the way to go. My 200+ packages are all independent and run based on a single config file and it seems as though going the parent package route will destroy some of that independence because I'll now be relying on parent package variables.

Any comments or suggestions?

Sounds like you have a great solution that works for you. If you decided to use a parent package to execute the child packages, it would be easy enough to drive with a ForEach loop and a simple file input or even a script task. A lot about whether this is the right choice for you depends on some things you haven't told us. For example, what is the long term plan for your current solution, do you plan on enhancing the current solution etc. Also, if you use a parent package, you're not forced to use parent package configurations. You can still use the same configuration scheme you're using currently.

From the information you've given, I'd say that it sounds like a good solution.

|||

Thanks for the response, Kirk. My 200+ packages use the config file in an indirect manner. When I design my packages, I don't step thru the Configuration Wizard and create a direct configuration, I just make sure to always name my objects the same and then I just apply my global config file with the /CONFIGFILE "c:\wherever\conf.dtsConfig". However, the Execute Package Task doesn't have any properties for specifying configurations. Ideally, I'd like a master package that read a queue table and that table would have the path to a package and a path to a config file and feed that to the Execute Package Task. Also, it would be nice if the Execute Package Task had a property like /SET from dtexec.exe so you wouldn't have to have child packages "pulling" variables/data from a master package because you have to design child packages with an awareness that they are executing in a larger context. Being able to apply a change from a master package to a child package would be preferrabe.

Benckmark. Inserting records.

How may inserts can SQL execute in a second?
The table that's inserting into has 3 fields (numeric, char(15) and
datetime)
and no other kind of SQL statements are running against it.
Hardware: quad proc, 2GB RAM, RAID.
TIA,
Nicthis is one of those 'it depends' issues...
depedning on the speed of your disks, number of indexes, other users on the
system, blocking, etc...
you could easily do hundreds or a few thousands per second on high end
hardware.
On 2 procs... I'd be thinking more in the range of hundreds... but only
testing will know for sure.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"BN" <nc@.abc.com> wrote in message
news:e9hUkZcXDHA.652@.TK2MSFTNGP10.phx.gbl...
> How may inserts can SQL execute in a second?
> The table that's inserting into has 3 fields (numeric, char(15) and
> datetime)
> and no other kind of SQL statements are running against it.
> Hardware: quad proc, 2GB RAM, RAID.
> TIA,
> Nic
>|||First, paralellism is the key to optimizing data loading performance.
Create your insert routine so that it can easily be partitioned and
"paralellized".
Depending on your application, you might also consider using bulk insert,
bcp, or DTS - it should be possible to achieve 10's of 1000's of rows per
second with a table that narrow. I would estimate with a midrange server
you could easily go 50K/sec with bulk insert into an empty heap with only a
couple streams.
----
The views expressed here are my own
and not of my employer.
----
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:esoc4fcXDHA.3924@.tk2msftngp13.phx.gbl...
> this is one of those 'it depends' issues...
> depedning on the speed of your disks, number of indexes, other users on
the
> system, blocking, etc...
> you could easily do hundreds or a few thousands per second on high end
> hardware.
> On 2 procs... I'd be thinking more in the range of hundreds... but only
> testing will know for sure.
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "BN" <nc@.abc.com> wrote in message
> news:e9hUkZcXDHA.652@.TK2MSFTNGP10.phx.gbl...
> > How may inserts can SQL execute in a second?
> >
> > The table that's inserting into has 3 fields (numeric, char(15) and
> > datetime)
> > and no other kind of SQL statements are running against it.
> >
> > Hardware: quad proc, 2GB RAM, RAID.
> >
> > TIA,
> >
> > Nic
> >
> >
>|||on a 2x2.4, using individual stored proc calls per single
line insert, i can get > 7K/sec using 10 separate threads
by consolidating more than 1 single row insert statement
into each stored procedure, >18k single row inserts/sec is
possible,
>30k rows/sec on multi-row inserts,
if you are a doing more than one single row insert in a
single stored proc., try using BEGIN/COMMIT TRAN even if
it is not required, this consolidates the transaction log
writes
go to the next sql server magazine connections conference
for more info, brian is there as well
www.sqlconnections.com
>--Original Message--
>How may inserts can SQL execute in a second?
>The table that's inserting into has 3 fields (numeric,
char(15) and
>datetime)
>and no other kind of SQL statements are running against
it.
>Hardware: quad proc, 2GB RAM, RAID.
>TIA,
>Nic
>
>.
>

Beginner @IDENTITY question

Hello,
When I execute the following code on my localhost the INSERT statement is successfull and my ID is entered. But for some reason when I uploaded to my remote host the exact same database (MS SQL) and code it keeps trying to enter a NULL value into the ID column- which causes an error? I don't understand becuase it is the exact same app and database but at a remote location.

Is @.IDENTITY somehow saved in the session and my remote servers session settings?? are off?

strSql = "insert into Login (UserName,Password) VALUES ('" + UserName.Text.Trim() + "','" + Password.Text.Trim() + "') select @.ID = @.@.IDENTITY";

Thanks in advance for any responses.
-WileyHi Wiley,

First, you should have a ; between the statements:

strSql = "insert into Login (UserName,Password) VALUES ('" + UserName.Text.Trim() + "','" + Password.Text.Trim() + "'); select @.ID = @.@.IDENTITY";

Second, you should probably be using SCOPE_IDENTITY instead of @.@.IDENTITY. The two are similar, but SCOPE_IDENTITY returns values inserted only within the current scope. The problem with @.@.IDENTITY is that you might get the ID value from another insert operation. Check out SQL Server Books Online for a more complete description of the issue.

But that doesn't explain your problem. My best guess is that the ID field isn't defined as an identity field in the database on the remote server. That's the first thing to check.

There is a slight chance that SET IDENTITY_INSERT is off in the database. I don't recall that you can set that as the normal setting, but something in the connection might be setting it. Definitely a longshot.

Third,DON'T USE DYNAMIC SQL THIS WAY!!!!! It opens up SQL injection attacks, particularly for fields that are obviously user input. If you don't know the issues, just ask. You are setting yourself up to have your app hacked and attacked.

Don|||Hello,
Part of my problem was that I created the database locally from a .dat file in a database called Resume. Everthing worked great. When I upoaded the tables to my remote assigned database which is called "mydatabase" not Resume I ran into permission problems becuase locally the owner was dbo and remotely the owner was "mydatabase". Also some of the ID fields were not set to IDENTITY.

I basically have my application working(sort of ) now but I have to allow NULLS on every column that allows them in every table for anything to (sort of ) work -which I DID NOT have to do locally and I can't figure out why, maybe someone can shed some light on that for me.

Also, in reference to SQL injection attacks that you mentioned above , maybe you can point me to a link on ms sql security and explain to me what is wrong with using the dynamic sql statement in the way i did. I definitely don't want to start using asp.net and start off on the wrong foot opening myself up for attacks.

thanks,
-Wiley|||there's a lot of info about sql inject that you can find on the web. some of them are:

http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=533341

http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf

http://www.nextgenss.com/papers/advanced_sql_injection.pdf

http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf

google rocks!|||wrong text on the first link. my mistake.