About Execution Timeout when registering large records with "Import Data..." in Sql Server Management Studio.

Question

I'm using SQL Server 2017 Standard.
In SQL Server Management Studio, I would like to know about the error that occurred when registering data from "RIGHT-click on DB name -> Tasks -> Import Data...".

The specified data source has an input of "Flat File Source" and the output is ".Net Framework Data Provider for SqlServer".
A flat file is a CSV file with about 3 million records. Register this in the SQL Server table.
For output data sources, I specify this because it's a table that requires parameters to be set to "Column Encryption Setting : Enabled".
(There is one column in the table encrypted by Allways Encrypted.)

Initially, I registered on an empty table and completed successfully.
Then, when I try to register a file of the same volume again, an error occurs in the middle of the registration process.
At this time, the data was registered until the middle of the second file.

When previously tested, the file that caused the error was able to register the same empty table.
Therefore, I think that there is no problem with the file itself.

The error was a message similar to the following:

Error 0xc020844b: Data Flow Task 1: An exception has occurred during data insertion, the message returned from the provider is: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination - _TEST_TABLE" (118) failed with error code 0xC020844B while processing input "Destination Input" (121). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
Error 0xc02020c4: Data Flow Task 1: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Source - _TEST_TABLE_csv returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

This error doesn't always seem to occur. In some cases, it was successful, but repeated tests often result in errors.
It has also been confirmed that there is enough disk space left on the server itself.

The message says timeout, so I set up the management studio to "Tools -> Options -> Query Execution -> Execution time-out: 0 seconds (no time-out)".
In addition, I changed the "remote query timeout" setting in the SQL Server configuration.
The "remote query timeout" set it to 0 seconds (no time-out) and I ran the command "RECONFIGURE".
I also searched for "sys.configurations" and confirmed that the setting change of "remote query timeout (s)" was reflected.

However, even when tested in this state, a execution timeout error occurred.

Another test was that some resources might have been under load, so I wiped out the index of the table once and tested it.
Originally there were five indexes, but if I erase the index, strangely the same test case will finish normally.
(Of course, since the registration process without an index is fast, there is a possibility that the probability of occurrence is reduced by shortening the time required.)

Is there a possibility that I'm stuck in some timeout other than the timeout setting above?
Or can something other than a timeout cause the error to occur?

I'd appreciate it if someone could tell me how to solve this problem.

Answer 1

and the output is ".Net Framework Data Provider for SqlServer".

The Import/Export Wizard is in unmanaged code written, .NET data provider is managed code; works together, but not that well.
Try it with "SQL Server Native Client" latest version instead.

In addition, I changed the "remote query timeout"

That setting is for queries over linked server and has no effect on client tools like the Import/Export Wizard.

Answer 2

Hi @knt1N ,

Import data to a SQL Server database containing columns protected with Always Encrypted. To ensure the SQL Server Import and Export Wizard can encrypt data, you need to enable Always Encrypted for the destination database connection and you need to have access to the keys protecting the data in the destination database columns.

If you using .NET Framework Data Provider for SQL Server, please make sure the machine on which the wizard runs uses .NET Framework 4.6.1 or later. And enable Always Encrypted for a connection, set Column Encryption Setting to Enabled in the connection properties.

To encrypt data stored in a SQL Server destination database, you need the VIEW ANY COLUMN MASTER KEY DEFINITION and VIEW ANY COLUMN ENCRYPTION KEY DEFINITION permissions in the source database. You also need key store permissions to access and use your column master key.

Refer to MS document Migrate data to or from columns using Always Encrypted with SQL Server Import and Export Wizard to get more.