3

in a quite big project we recently received the request to execute certain SSIS packages during regular business hours. However, while the package can take as long as it needs during the night, during working hours it is most important to stop execution as soon as the source query exceeds a given time.

I am aware, that there is a property of the OleDB source which can be set, but is there any way to set it programmatically / via expression? There should be a way to parametrize this timeout, no?

Thanks for any help.

Yahfoufi
  • 2,220
  • 1
  • 22
  • 41
Tyron78
  • 4,117
  • 2
  • 17
  • 32
  • You have CommandTimeout under Custom properties on your OLEDB SRC. This is the only place you can do it. Otherwise you need to modifiy your DB settings. – SqlKindaGuy Dec 19 '17 at 10:50

2 Answers2

2

You cannot do it with Expressions – see Microsoft Doc for available properties for a Data Flow. None of OLE DB Source properties is exposed as expression.

You can create another package with fixed OLEDB Source timeouts, and start it when needed. If you really want to parametrize it, you can generate package with BIML providing needed value at generation time; but you do not have a flexibility to change it at runtime.

Baum mit Augen
  • 49,044
  • 25
  • 144
  • 182
Ferdipux
  • 5,116
  • 1
  • 19
  • 33
  • This might work for the SQL Task, but I don't see the corresponding properties / expression possibilities in a DataFlows OleDB Source component... – Tyron78 Dec 19 '17 at 10:38
  • @Tyron78, according to MS Docs, you cannot do it with Expressions. If you really want to expose timeout as a parameter, you can try generating package with BIML specifying timeout value at generation time. – Ferdipux Dec 19 '17 at 11:39
  • @Tyron78 the only way to do this is when generating package using BIML, i think that this is the only answer that you will get – Hadi Dec 19 '17 at 19:42
  • Thanks. Yes, I already had some biml scripts and regenerated the packages. However, I think the modification via expression would be much better. Maybe it will be added at a later point of time. :-D – Tyron78 Dec 20 '17 at 13:35
0

This here is what your looking for.

enter image description here

This allows you to set the time of your query when it should timeout.

SqlKindaGuy
  • 3,501
  • 2
  • 12
  • 29
  • Thanks, but as mentioned in my question: I am aware, that there is a property of the OleDB source which can be set, but is there any way to set it programmatically / via expression? – Tyron78 Dec 19 '17 at 11:09
  • No there isnt :) You have to set it on your DB if you want too mix around with timeouts. – SqlKindaGuy Dec 19 '17 at 13:07