0

I am using SSDT 2017. There are 3 Dataflow tasks (They are connected using precedence constraints) which loads data from Excel into Database tables. When I run each task individually it is showing as success but when I run the entire package it is showing as completed with out any green tick on the tasks which means the tasks are not getting executed. After I changed the RetainSameConnection Property of Excel to True, all the tasks are getting completed successfully.

I have not seen this behavior in the earlier versions. Is this a new change in 2017 version or am I missing anything ?

Hadi
  • 36,233
  • 13
  • 65
  • 124
Srikar mogaliraju
  • 215
  • 1
  • 10
  • The only times I've seen SSIS not execute steps in this way was because of a flow control issue or something like file access permissions. What if you put all 3 into a single sequence container? Do you have the same issue? – Jacob H Feb 25 '19 at 14:42
  • I am sure that there is no problem in the control flow or the access permission. The only thing I have modified is the RetainSameConnection property. – Srikar mogaliraju Feb 25 '19 at 15:35
  • 1
    The data flows run in parallel or they are connected using precednece constraints? – Hadi Feb 25 '19 at 21:42
  • 1
    @Hadi They are connected using precedence constraints. – Srikar mogaliraju Feb 26 '19 at 06:54
  • @Srikarmogaliraju what happens if you set the second and third data flow task `Delay Validation` property to true?? – Hadi Feb 26 '19 at 22:33
  • Are you sure the package is not executing? The execution is conducted outside of Visual Studio. The visual indication of what tasks have completed is asynchronously updated from the messages received from that process. As IS has gotten more mature, the consistency of the UI has improved... but it is still possible it is simply not reporting accurately. Do your outputs contain expected results? – Todd McDermid Feb 26 '19 at 23:59
  • @Srikarmogaliraju anything new considering this issue? have you tried the answer provided? it is good to leave a comment on the answer provided if it didn't solved the issue, then you can elaborate more the answerer, if it solved the issue or gives some insights why not up voting or accepting??!! – Yahfoufi Mar 11 '19 at 11:35
  • @Yahfoufi I haven't tried the above answers yet. I will try those and add the comments. – Srikar mogaliraju Mar 11 '19 at 13:08
  • @Srikarmogaliraju after googling it looks like that this is one of the reasons that `ReatinSameConnection` property is found *(prevent blocking multiple access to excel file)*. So your situation is normal. In addition, the provided answer clarifies why connection are blocked. – Yahfoufi Mar 14 '19 at 15:20

1 Answers1

1

Trying to figure out the issue

Based on the question and comments you are using 3 Data flow Task that contains 3 Excel source components that use the same connection manager.

At the start of the package execution in the Validation phase, each one of this components try must acquire the connection from the connection manager to read the metadata and try to keep this connection open until it must be executed and since there are many files that try to open the same connection it will cause a problem.

When using RetainSameConnection the package run the acquireconnection method once and use it multiple time, then the issue is solved.

Something to try

Try changing the Delay Validation property to True, and ValidateExternalMetadata property to False for each Excel Source on all Data Flow Tasks, it may solve the issue if the connection is only acquired for validation purposes. If it doesn't works then it means that the acquire connection method is called to lock the file for read even if the data flow task execution is not started yet.

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • Changing DelayValidation and ValidateExternalMetadata is not helping. Changing the RetainSameConnection property to true is resolving this issue. – Srikar mogaliraju Mar 11 '19 at 17:50
  • @Srikarmogaliraju then as i mentioned `If it doesn't works then it means that the acquire connection method is called to lock the file for read even if the data flow task execution is not started yet.` – Hadi Mar 11 '19 at 18:11
  • @Srikarmogaliraju there is no other solutions, i tried to give you an explanation why excel connection manager is acting like this.And i think this is one of the reason of the `RetainSameConnection`property existence. Good luck – Hadi Mar 11 '19 at 18:13
  • Thanks for your time @Hadi . will update if i get to know more about it. – Srikar mogaliraju Mar 11 '19 at 18:30