Failure during copy from blob to sql db using ADF

%3CLINGO-SUB%20id%3D%22lingo-sub-91337%22%20slang%3D%22en-US%22%3EFailure%20during%20copy%20from%20blob%20to%20sql%20db%20using%20ADF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-91337%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20get%20this%20error%20when%20using%20Azure%20Data%20Factory%20for%20copying%20from%20blob%20to%20azure%20SQL%20DB%3A-%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDatabase%20operation%20failed.%20Error%20message%20from%20database%20execution%20%3A%20ExecuteNonQuery%20requires%20an%20open%20and%20available%20Connection.%20The%20connection's%20current%20state%20is%20closed..%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20am%20able%20to%20connect%20to%20my%20DB%20using%20SSMS%20and%20I%20have%20enabled%20firewall%20to%20connect%20to%20Azure%20services.%3C%2FP%3E%0A%3CP%3EHas%20anyone%20faced%20this%20error%20when%20using%20ADF%20%2F%20know%20how%20to%20fix%20it%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-91337%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EADF%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAzure%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAzure%20Data%20Factory%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESQL%20Database%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-129624%22%20slang%3D%22en-US%22%3ERe%3A%20Failure%20during%20copy%20from%20blob%20to%20sql%20db%20using%20ADF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-129624%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20also%20had%20this%20error.%20Checking%20back%2C%20the%20only%20difference%20I%20had%20between%20previous%20successful%20jobs%20was%26nbsp%3Ba%20request%20to%20log%20it%20to%20a%20blob%20storage.%20When%20I%20rerun%20the%20pipeline%20without%20this%20it%20worked%20for%20me%20(or%20actually%20gave%20another%20cryptic%20error%20message%20%22%3CSPAN%3EThe%20service%20has%20encountered%20an%20error%20processing%20your%20request.%20Please%20try%20again.%20Error%20code%204815.%22).%20So%20remove%20the%20request%20for%20logging%20and%20I%20was%20back%20where%20I%20was%20before!%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-92823%22%20slang%3D%22en-US%22%3ERe%3A%20Failure%20during%20copy%20from%20blob%20to%20sql%20db%20using%20ADF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-92823%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20the%20exact%20same%20problem%20...%20it%20was%20working%20fine%20about%20two%20days%20ago%20and%20I%20was%20happily%20running%20pipelines%20from%20CSV%20files%20on%20blob%20to%20Azure%20SQL%20...%20then%20all%20of%20a%20sudden%20they%20begin%20to%20fail%20with%20the%20following%20helpful%20message%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFailed%20execution%3C%2FP%3E%3CP%3E--%3CBR%20%2F%3EDatabase%20operation%20failed.%20Error%20message%20from%20database%20execution%20%3A%20ExecuteNonQuery%20requires%20an%20open%20and%20available%20Connection.%20The%20connection's%20current%20state%20is%20closed..%3CBR%20%2F%3ELess%3CBR%20%2F%3ERun%20ID%3CBR%20%2F%3Exxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx%3CBR%20%2F%3EData%20read%3A%202.67%20MB%3CBR%20%2F%3EData%20written%3A%2053.63%20MB%3CBR%20%2F%3ERows%3A%2070000%3C%2FP%3E%3CP%3E--%26nbsp%3B%3C%2FP%3E%3CP%3ESkipped%20row%20count%3A%20108%3C%2FP%3E%3CP%3E--%3C%2FP%3E%3CP%3EExecution%20location%3A%20West%20US%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI%20wonder%20whether%20this%20is%20only%20occuring%20in%20West%20US%20or%20all%20data%20centres.%3CBR%20%2F%3EIs%20there%20anyway%20in%20which%20to%20increase%20the%20time%20the%20conneciton%20is%20held%20open%20for%2C%20either%20in%20the%20pipeline%20or%20SQL%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-92816%22%20slang%3D%22en-US%22%3ERe%3A%20Failure%20during%20copy%20from%20blob%20to%20sql%20db%20using%20ADF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-92816%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3CBR%20%2F%3E3days%20ago%2C%20I%20was%26nbsp%3Bable%20to%20copy%20data%20from%20BLOB%20CSV%20to%20SQL%20Database%20by%20Azure%20Data%20Factory%20COPY%20%26nbsp%3BPREVIEW%20without%20any%20errors.%20I%20tried%20to%20do%20the%20same%20datas%20and%20process%20again%20today%20and%20got%20the%20same%20error%20message%20with%20you.%20It's%20so%20simple%20schema%20and%20less%20data.%20I%20think%20we%20had%20better%20send%20a%20support%20ticket%20to%20MS%2C%20because%20we%20couldn't%20get%20the%20next%20action%20from%20this%20message...%3CBR%20%2F%3EIn%20addition%2C%20ADF%20COPY%20from%20SQL%20Database%20to%20SQL%20DWH%20was%20successful%20today.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20following%20is%20the%20error%20messge%20I%20got%20from%20ADF%3C%2FP%3E%3CDIV%20class%3D%22subSection%20col%22%3E%3CDIV%20class%3D%22row%20attemptStatus%22%3E%3CDIV%20class%3D%22subSectionHeaderText%22%3E----%3CBR%20%2F%3EFailed%20execution%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22attemptMessage%22%3E%3CSPAN%3EDatabase%20operation%20failed.%20Error%20message%20from%20database%20execution%20%3A%20ExecuteNonQuery%20requires%20an%20open%20and%20available%20Connection.%20The%20connection's%20current%20state%20is%20closed..%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%20class%3D%22messageExpander%22%3ELESS%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22subSection%20col%22%3E%3CDIV%20class%3D%22row%20subSectionHeader%22%3E%3CDIV%20class%3D%22subSectionHeaderText%22%3ERun%20ID%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Ece48e8c3-3c5d-4e3e-b841-9b8d5a6abc12_636370560000000000_636371424000000000_OutputDataset-55f%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22adf-attemptProperties%22%3E%3CSPAN%3EData%20read%3A%201.25%20MB%3CBR%20%2F%3E%3C%2FSPAN%3E%3CSPAN%3EData%20written%3A%20380.38%20KB%3CBR%20%2F%3E%3C%2FSPAN%3E%3CSPAN%3ERows%3A%206920%3CBR%20%2F%3E-----------------%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%20class%3D%22adf-attemptProperties%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22adf-attemptProperties%22%3E%3CSPAN%3Eregards%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%20class%3D%22adf-attemptProperties%22%3E%3CSPAN%3EShigeru%20Numaguchi%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-92429%22%20slang%3D%22en-US%22%3ERe%3A%20Failure%20during%20copy%20from%20blob%20to%20sql%20db%20using%20ADF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-92429%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20this%20error%20right%20now.%20I%20can%20tell%20you%20that%20I%20have%20successfully%20imported%20other%20files.%20It's%20only%20this%20particular%20file%20with%20the%20issue.%26nbsp%3BThe%20import%20sits%20there%20for%20a%20long%20time%20so%20I%20actually%20think%20the%20connection%20is%20timing%20out.%20The%20log%20says%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EData%20Read%3A%201.18%20MB%20Data%20Written%3A%200.00%20Bytes%20Rows%3A%200%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20difference%20between%20this%20file%20and%20the%20others%20is%20that%20this%20is%20a%20mutli%20column%20file%20(CSV)%20whereas%20other%20files%20as%20a%20single%20column%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Microsoft

Hello,

 

I get this error when using Azure Data Factory for copying from blob to azure SQL DB:- 

Database operation failed. Error message from database execution : ExecuteNonQuery requires an open and available Connection. The connection's current state is closed..

 

I am able to connect to my DB using SSMS and I have enabled firewall to connect to Azure services.

Has anyone faced this error when using ADF / know how to fix it?

 

Thanks in advance.

4 Replies
Highlighted

I have this error right now. I can tell you that I have successfully imported other files. It's only this particular file with the issue. The import sits there for a long time so I actually think the connection is timing out. The log says:

 

Data Read: 1.18 MB Data Written: 0.00 Bytes Rows: 0

 

The difference between this file and the others is that this is a mutli column file (CSV) whereas other files as a single column

Highlighted

Hello,
3days ago, I was able to copy data from BLOB CSV to SQL Database by Azure Data Factory COPY  PREVIEW without any errors. I tried to do the same datas and process again today and got the same error message with you. It's so simple schema and less data. I think we had better send a support ticket to MS, because we couldn't get the next action from this message...
In addition, ADF COPY from SQL Database to SQL DWH was successful today.

 

The following is the error messge I got from ADF

----
Failed execution
Database operation failed. Error message from database execution : ExecuteNonQuery requires an open and available Connection. The connection's current state is closed..
LESS
Run ID
ce48e8c3-3c5d-4e3e-b841-9b8d5a6abc12_636370560000000000_636371424000000000_OutputDataset-55f
Data read: 1.25 MB
Data written: 380.38 KB
Rows: 6920
-----------------
 
regards,
Shigeru Numaguchi
Highlighted

I have the exact same problem ... it was working fine about two days ago and I was happily running pipelines from CSV files on blob to Azure SQL ... then all of a sudden they begin to fail with the following helpful message:

 

Failed execution

--
Database operation failed. Error message from database execution : ExecuteNonQuery requires an open and available Connection. The connection's current state is closed..
Less
Run ID
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Data read: 2.67 MB
Data written: 53.63 MB
Rows: 70000

-- 

Skipped row count: 108

--

Execution location: West US


I wonder whether this is only occuring in West US or all data centres.
Is there anyway in which to increase the time the conneciton is held open for, either in the pipeline or SQL?

 

Thanks

Highlighted

Hi,

 

I also had this error. Checking back, the only difference I had between previous successful jobs was a request to log it to a blob storage. When I rerun the pipeline without this it worked for me (or actually gave another cryptic error message "The service has encountered an error processing your request. Please try again. Error code 4815."). So remove the request for logging and I was back where I was before!