Importing bacpac file with 25000 tables on SQL Server fails with timeout
It turns out that the solutions is as simple as specifying a larger timeout. It does matter which timeout though (there are several timeout switches for SqlPackage.exe and it's neither the connection timeout specifiable in a connection string), it's CommandTimeout
. The below modified command won't fail even with a bacpac containing a large number of tables (notice the last, newly added switch):
"C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\SqlPackage.exe" /Action:Import /SourceFile:"path\to\MyDB.bacpac" /TargetConnectionString:"Data Source=localhost;Initial Catalog=MyDB;Integrated Security=True;" /p:CommandTimeout="0"
"0" means no timeout, so this is only applicable when there is no danger if the operations gets stuck or runs for an unexpectedly long time, like importing the bacpac locally for development like I do.
Related videos on Youtube
Piedone
Zoltán Lehóczky here, a computer science and engineering student from Hungary. I'm the co-founder of Lombiq Technologies Ltd., a company focused on services related to the Orchard content management framework. See my LinkedIn profile.
Updated on September 18, 2022Comments
-
Piedone over 1 year
I'd like to import a bacpac file that was exported from an 500MB DB that however has around 25000 tables. The bacpac itself is around 18MB.
Importing through the UI of SQL Server Management Studio fails with OutOfMemoryException so I'd use SqlPackage.exe the following way:
"C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\SqlPackage.exe" /Action:Import /SourceFile:"path\to\MyDB.bacpac" /TargetConnectionString:"Data Source=localhost;Initial Catalog=MyDB;Integrated Security=True;"
I successfully used this command to do the import with previous (smaller, having fewer tables) versions of the DB, but now it fails with the below output. Can I do anything here?
Importing to database 'MyDB' on server 'localhost'. Creating deployment plan Initializing deployment Verifying deployment plan Analyzing deployment plan Importing package schema and data into database Updating database An unexpected failure occurred: Data plan execution failed with message Unable t o reconnect to database: Timeout expired. The timeout period elapsed prior to c ompletion of the operation or the server is not responding.. Unhandled Exception: Microsoft.SqlServer.Dac.Data.DataException: Data plan execu tion failed with message Unable to reconnect to database: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlReverseEngineer Exception: Unable to reconnect to database: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. - --> System.Data.SqlClient.SqlException: Timeout expired. The timeout period ela psed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception: The wait operation timed out --- End of inner exception stack trace --- at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolea n breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObj ect stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, Run Behavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBe havior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 time out, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameter EncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehav ior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletio nSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehav ior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, S tring method) at System.Data.SqlClient.SqlCommand.ExecuteReader() at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlReverseEngineerImpl.Execute Populators(Tuple`2 connectionTuple, IList`1 populators, Int32 totalPopulatorsCou nt, Int32 startIndex, Boolean progressAlreadyUpdated, ReverseEngineerOption opti on, SqlReverseEngineerRequest request) --- End of inner exception stack trace --- at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlReverseEngineerImpl.Execute PopulatorsInPass(SqlReverseEngineerConnectionContext context, ReverseEngineerOpt ion option, SqlReverseEngineerRequest request, Int32 totalCount, Tuple`2[] popul atorsArray) at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlReverseEngineerImpl.Populat eBatch(SqlReverseEngineerConnectionContext context, SqlSchemaModel model, Revers eEngineerOption option, ErrorManager errorManager, SqlReverseEngineerRequest req uest, SqlImportScope importScope) at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlReverseEngineer.PopulateEle ments(SqlReverseEngineerConnectionContext context, DataSchemaModel model, Revers eEngineerOption option, ErrorManager errorManager, SqlImportScope importScope, S qlReverseEngineerRequest[] requests) at Microsoft.Data.Tools.Schema.Sql.Deployment.Steps.DacBulkCopyStep.GetImport TableMetadata(ICollection`1 tables) at Microsoft.Data.Tools.Schema.Sql.Deployment.Steps.DacBulkCopyStep.Execute(I DbConnection conn, CancellationToken token) at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlPlanDeployment.Execute(Deplo ymentPlanHandle plan, String connectionString, String targetDatabaseName, Boolea n IsAzureTarget, String databaseScriptPath, String masterScriptPath, Cancellatio nToken cancelToken) at Microsoft.SqlServer.Dac.Deployment.PlanExecutor.Execute(DeploymentPlan pla n, SqlConnectionStringBuilder connectionString, Boolean isAzureTarget, LoggingCo ntext loggingContext, CancellationToken cancelToken) --- End of inner exception stack trace --- at Microsoft.SqlServer.Dac.Deployment.PlanExecutor.Execute(DeploymentPlan pla n, SqlConnectionStringBuilder connectionString, Boolean isAzureTarget, LoggingCo ntext loggingContext, CancellationToken cancelToken) at Microsoft.SqlServer.Dac.DeployOperation.UpdateDatabaseData(LoggingContext loggingContext, CancellationToken token) at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass10.<>c__DisplayCl ass12.<CreatePlanExecutionOperation>b__f() at Microsoft.Data.Tools.Schema.Sql.Dac.OperationLogger.Capture(Action action) at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass10.<CreatePlanExe cutionOperation>b__e(Object operation, CancellationToken token) at Microsoft.SqlServer.Dac.Operation.Microsoft.SqlServer.Dac.IOperation.Run(O perationContext context) at Microsoft.SqlServer.Dac.ReportMessageOperation.Microsoft.SqlServer.Dac.IOp eration.Run(OperationContext context) at Microsoft.SqlServer.Dac.DeployOperation.Microsoft.SqlServer.Dac.IOperation .Run(OperationContext context) at Microsoft.SqlServer.Dac.OperationExtension.Execute(IOperation operation, D acLoggingContext loggingContext, CancellationToken cancellationToken) at Microsoft.SqlServer.Dac.DacServices.InternalDeploy(IPackageSource packageS ource, Boolean isDacpac, String targetDatabaseName, DacDeployOptions options, Ca ncellationToken cancellationToken, DacLoggingContext loggingContext) at Microsoft.SqlServer.Dac.DacServices.ImportBacpac(BacPackage package, Strin g targetDatabaseName, DacImportOptions importOptions, Nullable`1 cancellationTok en) at Microsoft.Data.Tools.Schema.CommandLineTool.Program.DoImportBacpacOperatio n(CommandLineArguments parsedArgs) at Microsoft.Data.Tools.Schema.CommandLineTool.Program.PerformAction(CommandL ineArguments parsedArgs) at Microsoft.Data.Tools.Schema.CommandLineTool.Program.Run(String[] args) at Microsoft.Data.Tools.Schema.CommandLineTool.Program.Main(String[] args)
-
Boboyum almost 9 yearsWhat version of SQL Server are you trying to import to? What DacFX version are you using? you can get the latest SSDT/DacFX here: blogs.msdn.com/b/ssdt/archive/2015/06/29/…
-
Piedone over 8 yearsI'm using SQL Server 2014 Express and installed the latest tooling for VS 2013. Unfortunately this still happens.
-