I was trying to migrate some ‘big’ data to Azure today.
Well, not that big – about 20,000 rows.
But no matter how many times I tried, I was getting the following error:
Error: 0xC020844B at Table1, ADO NET Destination : An exception has occurred during data insertion, the message returned from the provider is: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 – An existing connection was forcibly closed by the remote host.)
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
For this data migration I was using SSIS of SQL Server 2008R2.
The error was strange, I did not really ask the remote host to close my connection.
I did some search online and found the following information: “The error message indicates clustered index is required for SQL Database tables.”
Ok, but I just double-checked – my table in Azure had perfectly good clustered index!
Then I remembered that a few weeks back I tested the latencies of Azure, and the results were somewhat comic with a grain of salt – here is my blog post about the connection latency in Azure. In that blog post I established that it takes about 1 second to connect to SQL Azure!
So the logical thing to do was for me to alter the connection timeout settings for the destination connections in my SSIS package. (For some reason the default 30 second timeout seems quite optimistic for SQL Azure )
So here is how the default settings of the container look like:
To fix the error all you have to do is increase the Command Timeout to some more reasonable value. I would suggest 600 seconds.