Reading Huge volume of data from Sqlite to SQL Ser

问题:

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.

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

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

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)

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.

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

What can I do?

回答1:

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. 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]

3. Add a For Loop with the following preferences:

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

9. 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]"
   

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

Control Flow Screenshot

References

• 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