1

Topic: SSIS to restrict cycle iterations on runtime

All greetings. Prompt please.
There is a packet, in one cycle the set of actions over the data (Extraction, transformation and an insertion of the data) is fulfilled. It is necessary for me to restrict performance of each iteration on time i.e. if cycle iteration exceeded certain value in seconds performance is necessary to interrupt and to pass to a following step of a cycle.
Tried to make it imitating an error in component SriptTask, iteration stops only after completion of any container (if it was launched before exception fixing in SriptTask), I do not know, how to me to interrupt all active actions forcedly. It is necessary to enumerate only them in the same SriptTask and to check on activity, if it is active forcedly to stop. Can eat more correct decision?

2

Re: SSIS to restrict cycle iterations on runtime

IDVT wrote:

Tried to make it imitating an error in component SriptTask

There are limitations in using the Script Task having no control over other tasks after its execution turn
Periodically such task floats. While the standard idea only the second sql agent job to supervise time of the first agent job with packet SSIS inside and to extinguish on limit excess.
The order to Redmond for a limit of time like as is, but things are right where they started .

3

Re: SSIS to restrict cycle iterations on runtime

Andy_OLAP wrote:

the Order to Redmond for a limit of time like as is, but things are right where they started .

not, the order is not present.
From Redmond write in these arguings: "if it is necessary - order in a connection". Probably, nobody ordered.

4

Re: SSIS to restrict cycle iterations on runtime

alexeyvg wrote:

it is passed...
Not, the order is not present.
From Redmond write in these arguings: "if it is necessary - order in a connection". Probably, nobody ordered.

I distracted on a problem of fastening of a new engine in postgresql and ceased to kick these lazy Hindus. Thanks for the information.

5

Re: SSIS to restrict cycle iterations on runtime

IDVT;
To refuse a cycle in  and to pass to the exterior
Further as to you already advised to extinguish a packet and to start the new

6

Re: SSIS to restrict cycle iterations on runtime

So in that and business to extinguish  there is no decision, it is a pity that the output is not present.... Means I will be  = (

7

Re: SSIS to restrict cycle iterations on runtime

We use the self-hand-written web application which starts SSIS-packages, and kills them after excess of the runtime set in application

8

Re: SSIS to restrict cycle iterations on runtime

>> which starts SSIS-packages, and kills them after
Similarly, but simply adjacent  which checks a core operating time , with the reference table where restrictions are registered

9

Re: SSIS to restrict cycle iterations on runtime

IDVT wrote:

All greetings. Prompt please.
There is a packet, in one cycle the set of actions over the data (Extraction, transformation and an insertion of the data) is fulfilled. It is necessary for me to restrict performance of each iteration on time i.e. if cycle iteration exceeded certain value in seconds performance is necessary to interrupt and to pass to a following step of a cycle.
Tried to make it imitating an error in component SriptTask, iteration stops only after completion of any container (if it was launched before exception fixing in SriptTask), I do not know, how to me to interrupt all active actions forcedly. It is necessary to enumerate only them in the same SriptTask and to check on activity, if it is active forcedly to stop. Can eat more correct decision?

to itself such decision.
Namely - the timer in working script task which forcedly extinguishes connection with data source.

10

Re: SSIS to restrict cycle iterations on runtime

Something I was absolutely lost and tangled, gain.....
The sense is that: the Parent packet, launches N' an amount of child packets, (I work on a straight line from files DTSX). Added the counter on which achievement, it is necessary for me to kill a flow, without waiting its completions.
Function of loading of file DTSX of a child packet
[spoiler]

static void RunPackage (int NumFlowsExecute
, int NumRepetition
, int NumSession
, object SourceDataList
, string DTSXPackage
, string StageDBName
, string DWHDBName
, ref Boolean ExceptionFlow)
{
Application app = new Application ();
Package pkg = app. LoadPackage (DTSXPackage, null);
Variables vars = pkg. Variables;
vars ["User:: NumFlowsExecute"].Value = NumFlowsExecute;
vars ["User:: SourceDataList"].Value = SourceDataList;
vars ["User:: NumRepetition"].Value = NumRepetition;
vars ["User:: NumSession"].Value = NumSession;
vars ["User:: StageDBName"].Value = StageDBName;
vars ["User:: DWHDBName"].Value = DWHDBName;
pkg. Execute (null, vars, null, null, null);
if (pkg. ExecutionResult. ToString () == "Failure")
{
ExceptionFlow = true;
}
}

[/spoiler]
Formation of tasks (flows)
[spoiler]

var TasksFlow = new List <Thread> ();
.
.
.
for (int i = 0; i <CountRunFlow; i ++)
{
int NumFlowsExecute = i + 1;
var Thread = new Thread (() => RunPackage (NumFlowsExecute
, NumRepetition
, NumSession
, DTableList
, DTSXPackage
, StageDBName
, DWHDBName
, ref ExceptionFlow));
Thread. Start ();
TasksFlow. Add (Thread);
}

[/spoiler]
Check on end of flows, here I cause method Abort () to everyone to active treacle, i.e. not completed which period of performance exceeded admissible (variable TimeOutDTSXPackage)
[spoiler]

while (TasksFlow. Any (t => t. ThreadState == System. Threading. ThreadState. Running))
{
CounterSec ++;
//the flow () is fulfilled longer admissible time ()
if (CounterSec> TimeOutDTSXPackage)
{
for (int i = 0; i <CountRunFlow; i ++)
{
var CurrentThread = TasksFlow. ElementAt (i);
//check of a state of a flow
if (CurrentThread. ThreadState == System. Threading. ThreadState. Running)
{
CurrentThread. Abort ();//if it is active, we complete a flow
}
}
}
Thread. Sleep (1000);
}
Dts. Variables ["User:: ExceptionFlow"].Value = ExceptionFlow;
Dts. TaskResult = (int) ScriptResults. Success;

[/spoiler]
The script, on the given condition remarkably quits a cycle, all further logic works, BUT method Abort () actually does not complete a child packet. For certain I crookedly described logic of its end, there are no skills of operation with C#, especially with flows. Help to kill please all not complete flows in the list "TasksFlow".
Methods of the decision with Dzhobom to me not absolutely approaches, since the amount of flows is set by a variable, today 2, tomorrow 14 etc.

11

Re: SSIS to restrict cycle iterations on runtime

https://stackoverflow.com/questions/122 … ead-in-c11
How do I terminate a thread?

12

Re: SSIS to restrict cycle iterations on runtime

IDVT wrote:

Something I was absolutely lost and tangled, gain.....

I can be mistaken, but, at first, you complete not packet performance, and the local flow which has launched a packet on the server. Secondly, the executable packet cannot be completed forcedly - it is possible only politely to ask to be completed it (it not ).
For this reason for the forced break of a packet I also tore its connection with a source.

13

Re: SSIS to restrict cycle iterations on runtime

IDVT;
And at you SSIS versions 2008 or 2012 +? If 2012 + and packets in mode SSIS Catalog - they can be launched asynchronously, receiving back the execution code. Further - periodically to check the status of a certain copy of execution. If it is necessary - execution can be beaten.
Example - sm http://muxtonmumbles.blogspot.ru/2012/0 … es-in.html
The detail information about namespace - https://msdn.microsoft.com/en-us/librar … vices.aspx
If at you SSIS 2008 - that through jobs if dynamic - it is possible to create job and then it to beat.

14

Re: SSIS to restrict cycle iterations on runtime

IDVT;
This method should solve a task in view without problems. Thread. Join Method (TimeSpan)
https://msdn.microsoft.com/en-us/library/23f7b1ct (v=vs.110).aspx

15

Re: SSIS to restrict cycle iterations on runtime

Ferdipux wrote:

IDVT;
And at you SSIS versions 2008 or 2012 +? If 2012 + and packets in mode SSIS Catalog - they can be launched asynchronously, receiving back the execution code.

I will add that  they just asynchronously and are launched.
Also it is necessary for synchronism to do something similar:

declare @execution_id bigint
exec [SSISDB]. [catalog]. [create_execution]
@folder_name = N'folder1'
,@project_name = N'project1'
,@package_name = package1.dtsx'
,@execution_id = @execution_id output
exec [SSISDB]. [catalog]. [set_execution_parameter_value]
@execution_id,
@object_type=50,
[b] @parameter_name=N'SYNCHRONIZED ', [/b]
@parameter_value=1; - true
exec [SSISDB]. [catalog]. [start_execution] @execution_id
set @output_execution_id = @execution_id

16

Re: SSIS to restrict cycle iterations on runtime

Andy_OLAP;
So it is more beautiful:

declare @execution_id bigint
exec [SSISDB]. [catalog]. [create_execution]
@folder_name = N'folder1'
,@project_name = N'project1'
,@package_name = N'package1.dtsx'
,@execution_id = @execution_id output
exec [SSISDB]. [catalog]. [set_execution_parameter_value]
@execution_id,
@object_type=50,
[b] @parameter_name=N'SYNCHRONIZED ', [/b]
@parameter_value=1; - true
exec [SSISDB]. [catalog]. [start_execution] @execution_id
set @output_execution_id = @execution_id

17

Re: SSIS to restrict cycle iterations on runtime

The version of the server 2016, completely allows to leave from listing under 2008 version, but to rewrite it is a lot of, while is not present for this time. Therefore, the majority of the offered decisions not compatibly with the current project, now a child packet do not tear at all on the server, boots from a local directory. But all it I will rewrite in the near future, and the decision is required now.

a_voronin wrote:

IDVT;
This method should solve a task in view without problems. Thread. Join Method (TimeSpan)
https://msdn.microsoft.com/en-us/library/23f7b1ct (v=vs.110).aspx

Thanks, I will try. But not absolutely I understand usefulness, it is necessary for me to complete roughly a child packet, in the given example check on its end. With it method List perfectly consults. Any (t => t. ThreadState == System. Threading. ThreadState. Running)