CODEWITHSUNDEEP
sql-serverexcelssisetlssis-2017
Srikar mogaliraju
Srikar mogaliraju

Reputation: 215

SSIS Package- Retain Same Connection Property in Excel Connection

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 ?

Upvotes: 0

Views: 1722

Answers (1)

Hadi
Hadi

Reputation: 37313

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.

Upvotes: 1

Related Questions