Dynamic packages and maxconcurrentexecutables

I have recently prototyped a system, in which I use meta data to build a package of execute package tasks.

Essentially, in a Script task I do the following:

1) Create a new package in memory.

2) I add variables, logging to this package.

3) To allow for precedences I create a sequence container for any and all execute pacakge tasks that can run in parallel.

4) To help with parameters specific to the execute package task, I add another sequnce container for the individual excute package task.

5) Finally I add the actual execute package task.

6) Save the package to local disk.

7) Execute this pacakge.

I hadn't been setting explicitly setting the MaxConcurrentExecutables, but upon opening the saved copy of the last package I built and executed it has the default -1 setting.

My problem is that while this in-memory package is running it appears to be only running a single executalbe at a time. I'm going to try setting maxconcurrentexecutable to 4 or some other number to see if I get some parallel execution going on.

The real question is "Is there a limitation on using dynamic packages that limits them to only run a single executable at a time?". I haven't found anything in BOL that leads me to believe there is, but It was very obvious that only one executable would run at a time when I test this out.

How are you testing this? If you run the dynamic generated package directly, do multiple executables run?|||Right now my testing involves watching the staging tables that I have. I expect to see them start populating with data somewhat simultaneously.|||Try opening the generated package, and running it directly through BIDS. There are a number of reasons why a package might not run concurrent executables (system resources, etc), but if it works in BIDS, then we can narrow it down to a code issue.

Dynamic filter using a UDF

In BOL it says that you can use a UDF in a dynamic filter, passing in one of
the system UDFs. However, when I try this I get a syntax error, e.g.
SELECT * FROM [dbo].[SiteData]
WHERE SiteId IN (SELECT Value FROM dbo.Split(HOST_NAME(), ','))
where dbo.Split is a UDF that splits a list of values into a table
It doesn't seem to like the brackets after HOST_NAME, but the example shown
in BOL is MYUDF(HOSTNAME())
Any ideas what I've got wrong with the syntax?
TIA
Paul
Paul,
the only way I've been able to resolve a system function to multiple values
is to use a linking table and replicate it as well. Have a look at the
example in the merge section of www.replicationanswers.com where there is a
related example.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul
Thanks for that, I can make it work in my scenario - BTW you also quote the
MYUDF(HOST_NAME()) solution as well further down the page.
Regards
Paul
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%236k7vXQxEHA.3612@.tk2msftngp13.phx.gbl...
> Paul,
> the only way I've been able to resolve a system function to multiple
> values
> is to use a linking table and replicate it as well. Have a look at the
> example in the merge section of www.replicationanswers.com where there is
> a
> related example.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Paul,
I'll edit the page to make clearer, but I've only used
the MYUDF(HOST_NAME()) solution for scalars.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I'll need to check this out in SQL 2005 beta to see if it's fixed or if
anyone can give a reason why ...
SELECT * FROM [dbo].[SiteData] WHERE SiteId IN (SELECT Value FROM
dbo.Split('1, 2', ','))
compile (and runs), but
SELECT * FROM [dbo].[SiteData] WHERE SiteId IN (SELECT Value FROM
dbo.Split(HOST_NAME(), ','))
does not, whereas
SELECT * FROM [dbo].[SiteData] WHERE SiteId = MyUdf(HOST_NAME())
compiles again.
It's nothing to do with replication per se, but how the complier is parsing
the statements.
Thanks
Paul
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:4d0d01c4c575$382daee0$a301280a@.phx.gbl...
> Paul,
> I'll edit the page to make clearer, but I've only used
> the MYUDF(HOST_NAME()) solution for scalars.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||why don't you put the hostname within your function as opposed to supplying
it as an argument.
"Paul Hatcher" <phatcher@.nospam.cix.co.uk> wrote in message
news:OHJ9%238YxEHA.2624@.TK2MSFTNGP11.phx.gbl...
> I'll need to check this out in SQL 2005 beta to see if it's fixed or if
> anyone can give a reason why ...
> SELECT * FROM [dbo].[SiteData] WHERE SiteId IN (SELECT Value FROM
> dbo.Split('1, 2', ','))
> compile (and runs), but
> SELECT * FROM [dbo].[SiteData] WHERE SiteId IN (SELECT Value FROM
> dbo.Split(HOST_NAME(), ','))
> does not, whereas
> SELECT * FROM [dbo].[SiteData] WHERE SiteId = MyUdf(HOST_NAME())
> compiles again.
> It's nothing to do with replication per se, but how the complier is
> parsing the statements.
> Thanks
> Paul
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:4d0d01c4c575$382daee0$a301280a@.phx.gbl...
>
|||Because it says in BOL that you can't use dynamic snapshots if neither of
HOST_NAME/SUSER_NAME() functions are not specified in the row filter. The
scenario is a largish central database with a bunch of subsidiary offices,
some of which are on dial-up, so I'm trying everything to minimise the
amount of time taken to do replication and the amount of data on the wire.
I'll try it anyway and see what happens; the other point is that I've come
across Transact-SQL oddities like this before, and it's a good idea to get
rid of them if at all possible.
Thanks
Paul
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OX8lbLaxEHA.1400@.TK2MSFTNGP11.phx.gbl...
> why don't you put the hostname within your function as opposed to
> supplying it as an argument.
> "Paul Hatcher" <phatcher@.nospam.cix.co.uk> wrote in message
> news:OHJ9%238YxEHA.2624@.TK2MSFTNGP11.phx.gbl...
>

Dynamic Deluxe!

I have a software system that needs to give the user to develop basically an
if statement. So IF PERSON_AGE > 15 AND MONTHLY_PURCHASE_AMT > 1000 THEN
GIVE THEM 1500 POINTS.
Here is the deal. I need to do it both during a back end process and
dynamically when a person comes to the counter. These marketing programs ar
e
definable by the instituion so we can't build the query ahead of time.
Anythoughts on a good way to do this. Keep in mind that there might be
multiple marketing programs active and the person qualifies for more then on
e
all should be able to display to the user. I would like to use a function so
that I can return a select statement with the messages to be presented to th
e
user.
Thanks for any thoughts.
Sammy DIt depends how complex your promotional formula needs to get but for
range-based criteria you could do something like this:
CREATE TABLE Promotions (promotion_code CHAR(10) PRIMARY KEY,
points_award INTEGER NOT NULL, age_min INTEGER NOT NULL, age_max
INTEGER NOT NULL, purchase_amt_min NUMERIC(10,2) NOT NULL,
purchase_amt_max NUMERIC(10,2) NOT NULL, ...)
Example. Promotions applicable to customer 1234:
SELECT P.promotion_code, P.points_award
FROM X, Promotions AS P
WHERE X.age BETWEEN P.age_min AND P.age_max
AND X.purchase_amt BETWEEN P.purchase_amt_min AND P.purchase_amt_max
AND customer = 1234
Where a particular attribute isn't relevant to the promotion just set
those values to be the min and max for the datatype.
David Portas
SQL Server MVP
--|||You might consider using a decision table program and not a database at
all.

Dynamic connect...?

I am trying to implement a web application user login system where every user is an Oracle user, so I can avoid having tables containing passwords and what not. In fact, having passwords in a table is not an option, even if they're encrypted. Anyway, I'm trying to set it up so that the login page is under a DAD that logs in as a user with rights to the login package only. Then, once the user has typed in their name and password and submitted, I want to then log them in as their user that has already been created in Oracle.

The first part is easy enough, but I have tried unsuccessfully to find some way to use dynamic sql to change users, such as EXECUTE IMMEDIATE 'CONNECT user/pass@.db'; and concatenating the appropriate values, but nothing seems to work.

I'm trying to avoid the basic authentication dialog box, as well as avoiding storing passwords in tables. I have looked into the custom authorization stuff provided by owa_custom, but I can't see any way to implement it with Oracle users. Any help on this would be greatly appreciated. Thanks!"connect" is a SQL*Plus command and hence cannot be executed dynamically. Other examples would be "show user", "desc table" etc. Only sql commands can be executed using dynamic sql.

But if you have the uid & pwd, can you not connect from your web application to see if the user is a valid database user or not ?|||Yeah, that is an option. I'm trying to avoid using JDBC or anything like that. However, if need be, I suppose that is something I can try. If there are any other ways to connect from the app, I would be interested in hearing about them. My experience with web application login systems is extremely limited, so any kind of help is greatly appreciated.