Reading Huge volume of data from Sqlite to SQL Server fails at pre-execute

▲
2
▼

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?

sql-server
sqlite
ssis
etl
sqlite-odbc
asked on Stack Overflow Jan 13, 2019 by Can Vural • edited Jan 13, 2019 by Hadi

1 Answer

▲
3
▼

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]

enter image description here

enter image description here

  1. Add a For Loop with the following preferences:

enter image description here

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

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

Control Flow Screenshot

enter image description here

References

answered on Stack Overflow Jan 13, 2019 by Hadi

User contributions licensed under CC BY-SA 3.0