Showing posts with label starts. Show all posts
Showing posts with label starts. Show all posts

Thursday, March 22, 2012

delay when running with DTexec.exe

Hi,
when I try to run my package with dtexec.exe, it starts fine but in the process it package calles another subpackage and at the time there is big delay before start processing the subpackage.
the subpackage has been setup so executionoutofprocess pramater has been set to true.
any idea what migth be the problem.
I have to metion even when I run this with in the visual studio still I have a big delay.

cheersWhat is a "big" delay? SSIS has to validate the package and its connections/metadata before executing.|||it takes normally between 30 sec to one min. for it to start the sub-package. is there any way to reduce this delay by pre-validation
Thanks|||Try setting DelayValidation = True on your connection managers, sources and destinations.|||

From another thread on this forum:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=739674&SiteID=1

Usually, executing using DTEXEC /F File.DTSX is the fastest way, but gives the mininum logging. Varios logging options for DTEXEC add functionality but may affect performance, although I've never seen 10x times slowdown because of it.

I have tested myself and I can tell the speed improves but just too litle.

|||Is there any way to do the validation once and then cache it. instead of SSIS vaidating it each time I run as it has got a big overhead.
cheers|||

Kolf wrote:

Is there any way to do the validation once and then cache it. instead of SSIS vaidating it each time I run as it has got a big overhead.
cheers

Validation HAS to happen. And unless you set DelayValidation=TRUE a task will get validated when the package spins up and immediately pror to the task executing. There isn't alot of point in caching the results of the first Validation because that would negate the point of doing the second validation.

Does that answer the question?

-Jamie

Wednesday, March 21, 2012

delay in running the sub-packages

Hi,
when call a sub-package from the parent package with the executeoutofprocess=true I have about 5-10 sec delay before sub-package starts running. ( this is a big delay for me as the sub_package is in a foreach loop)
the sub_package itself it's very simple and contains just one script task with a small script in in it.
even if this delay is for validation I still can't understand why it is taking 5-10 ses.
cheers

Kolf wrote:

Hi,
when call a sub-package from the parent package with the executeoutofprocess=true I have about 5-10 sec delay before sub-package starts running. ( this is a big delay for me as the sub_package is in a foreach loop)

the sub_package itself it's very simple and contains just one script task with a small script in in it.
even if this delay is for validation I still can't understand why it is taking 5-10 ses.
cheers


I have to add that the subpackage has got some variables which is specified in package configurations
|||

Have you tried executeoutofprocess = false? Do you have the same delay?

|||And delayvalidation = true?

Delay before publishing

Hello
Is it possible that the Publisher could wait some time ( about 15 seconds)
before it starts publishing ?
I want it to start publishing when it gets new data, but there can be about
1000 new records in few seconds and I think it would be better to
wait for few seconds then to create new publication after single record is
inserted.
Best Regards
Wojciech Znaniecki
Wojciech ,
there is a difference between creating a publication and synchronizing data
for an existing publication. The publication can be created and as long as
the logreader and distribution agents don't run (no synchronization) there
is no effect on the publisher. Typically the log reader runs continuously,
but you can schedule the distribution agent to run whenever you want. If
this also runs continuously and you want to enforce a delay, you could
increase the POLLINGINTERVAL parameter's value. Other parameters you might
be interested in are -CommitBatchThreshold, CommitBatchSize,
MaxDeliveredTransactions.
HTH,
Paul Ibison
|||Thanks for fast anwser
I've tried to do it but I cant create a publication with stopped
Distribution Agent and Log Reader Agent.
Can I do it with sql script?
for example: my script that create Publication looks like that :
"(...)
exec sp_addpublication @.publication = N'RozkazyReplikacja', @.restricted =
N'false', @.sync_method = N'native', @.repl_freq = N'continuous', @.description
= N'Transactional publication of replikacyjna database from Publisher
WOJTEK-Z\W1.', @.status = N'active', @.allow_push = N'true', @.allow_pull =
N'true', @.allow_anonymous = N'false', @.enabled_for_internet = N'false',
@.independent_agent = N'false', @.immediate_sync = N'false', @.allow_sync_tran
= N'false', @.autogen_sync_procs = N'false', @.retention = 336,
@.allow_queued_tran = N'false', @.snapshot_in_defaultfolder = N'true',
@.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
@.allow_dts = N'false', @.allow_subscription_copy = N'false',
@.add_to_active_directory = N'false', @.logreader_job_name =
N'WOJTEK-Z\W1-replikacyjna-5'
exec sp_addpublication_snapshot @.publication = N'RozkazyReplikacja',
@.frequency_type = 4, @.frequency_interval = 1, @.frequency_relative_interval =
0, @.frequency_recurrence_factor = 1, @.frequency_subday = 4,
@.frequency_subday_interval = 1, @.active_start_date = 0, @.active_end_date =
0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959,
@.snapshot_job_name = N'WOJTEK-Z\W1-replikacyjna-RozkazyReplikacja-7'
GO
(...) "
What should i change to create publication without starting Distribution and
Log Reader Agent ?
Best Regards
Wojciech Znaniecki
|||Wojtek,
using sp_addsubscriber will allow you to set the frequency for the
distribution agent. However, the easeist way to do what you require is to
create the publication without starting the snapshot agent. Then edit the
logreader and distribution agent jobs to run on a schedule.
HTH,
Paul Ibison
|||Can I set logreader and distribution agent to run more then once per minute ?
Or could I create a trigger to start, let logreader and distribution reader
to their job and stop them ?
Best Regards
Wojciech Znaniecki
"Paul Ibison" wrote:

> Wojtek,
> using sp_addsubscriber will allow you to set the frequency for the
> distribution agent. However, the easeist way to do what you require is to
> create the publication without starting the snapshot agent. Then edit the
> logreader and distribution agent jobs to run on a schedule.
> HTH,
> Paul Ibison
>
>
|||Wojciech,
you can use sp_start_job to start the agents if you want to do it
programatically. If you want to schedule it, AFAIR the maximum frequency is
1 minute or you can run continuously. If the latter, the pollinginterval
will potantially increase it in a more granular way. However, the log reader
agent normally runs continuously and the distribution agent may be
controlled in such a way. The log reader agent marks the log as having been
read, and if it is not run very frequently, the log will increase in size
and not be fully truncated during a backup.
HTH,
Paul Ibison