在预执行时无法从Sqlite向SQL Server读取大量数据 [英] Reading Huge volume of data from Sqlite to SQL Server fails at pre-execute

问题描述

我有一个庞大的(26GB)sqlite数据库，我想通过SSIS导入到SQL Server.

I have a huge (26GB) sqlite database that I want to import to SQL Server with SSIS.

我一切设置正确.一些数据流可以正常工作并正在导入数据.

I have everything setup correctly. Some of the data flows are working correctly and importing the data.

数据流很简单.它们只是由源和目标组成.

Data flows are simple. They just consist of source and destination.

但是当涉及到具有8000万行的表时，数据流将失败，并显示以下无用的消息:

But when it comes to a table that has 80 million rows, data flow fails with this unhelpful message:

代码:0xC0047062
来源:数据流任务来源9-nibrs_bias_motivation [55]
说明:System.Data.Odbc.OdbcException(0x80131937):错误[HY000]未知错误(7)

Code: 0xC0047062
Source: Data Flow Task Source 9 - nibrs_bias_motivation [55]
Description: System.Data.Odbc.OdbcException (0x80131937): ERROR [HY000] unknown error (7)

在System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle，RetCode retcode)中
在System.Data.Odbc.OdbcCommand.ExecuteReaderObject处(CommandBehavior行为，String方法，布尔型NeedReader，Object [] methodArguments，SQL_API odbcApiMethod)
在System.Data.Odbc.OdbcCommand.ExecuteReaderObject处(CommandBehavior行为，String方法，布尔型NeedReader)
在System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior行为)上
在System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior行为)上
在System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader处(CommandBehavior行为)
在Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute()
在Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper100包装器)

at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute()
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper100 wrapper)

在此任务失败之前，内存使用率高达99％，然后任务失败.这使我认为这是一个记忆问题.但是我不知道该怎么解决.

And before this task fails, memory usage goes up to 99%, then the task fails. This made me think its a memory issue. But I don't know how can I solve this.

我尝试在所有数据流任务上将DelayValidation设置为true.没有改变. 我玩了缓冲区大小.没事.

I tried setting DelayValidation to true on all data flow tasks. Nothing changed. I played with the buffer sizes. Nothing.

我该怎么办?

推荐答案

分步指南

由于从大型数据集中读取时会引发错误，因此请尝试按块读取数据，以实现可以执行以下步骤:

Step by Step guide

Since the error is thrown when reading from a large dataset, try reading data by chunks, to achieve that you can follow these steps:

1. 声明2个类型为Int32(@[User::RowCount]和@[User::IncrementValue])的变量
2. 添加执行select Count(*)命令的Execute SQL Task并将结果集存储到变量@[User::RowCount]

1. Declare 2 Variables of type Int32 (@[User::RowCount] and @[User::IncrementValue])
2. Add an Execute SQL Task that execute a select Count(*) command and store the Result Set into the variable @[User::RowCount]

1. 添加具有以下首选项的For循环:

1. 在for循环容器内添加Data flow task
2. 在数据流任务中添加ODBC Source和OLEDB Destination
3. 在ODBC Source中，选择SQL Command选项并编写一个SELECT * FROM TABLE查询*(仅检索元数据`
4. 在源和目标之间映射列
5. 返回Control flow并单击Data flow task并单击 F4 以查看属性窗口

6. 在属性窗口中，转到expression并将以下表达式分配给[ODBC Source].[SQLCommand]属性:(有关更多信息，请参考

1. Inside the for loop container add a Data flow task
2. Inside the dataflow task add an ODBC Source and OLEDB Destination
3. In the ODBC Source select SQL Command option and write a SELECT * FROM TABLE query *(to retrieve metadata only`
4. Map the columns between source and destination
5. Go back to the Control flow and click on the Data flow task and hit F4 to view the properties window

6. In the properties window go to expression and Assign the following expression to [ODBC Source].[SQLCommand] property: (for more info refer to How to pass SSIS variables in ODBC SQLCommand expression?)

"SELECT * FROM MYTABLE ORDER BY ID_COLUMN
LIMIT 500000
OFFSET " + (DT_WSTR,50)@[User::IncrementValue]"

其中MYTABLE是源表名称，而IDCOLUMN是您的主键或标识列.

Where MYTABLE is the source table name, and IDCOLUMN is your primary key or identity column.

控制流屏幕截图

• ODBC源-SQL Server
• 如何在ODBC SQLCommand表达式中传递SSIS变量?/a>
• SQLite限制

• ODBC Source - SQL Server
• How to pass SSIS variables in ODBC SQLCommand expression?
• HOW TO USE SSIS ODBC SOURCE AND DIFFERENCE BETWEEN OLE DB AND ODBC?
• SQLite Limit

这篇关于在预执行时无法从Sqlite向SQL Server读取大量数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！