CODEWITHSUNDEEP
mysqlsql-serverssis
django-unchained
django-unchained

Reputation: 844

Incremental Load from MySQL to MS SQL Server

I've tried different approaches for incremental load from MySQL to MS SQL Server. But found below approach optimal:

  1. Get max(UpdateDate) from Destination table and read all the records having UpdateDate > max(UpdateDate) of destination table. (Execute SQL task to get max date from destination table. Storing in a variable)

  2. Load these rows into temp table.

  3. Lookup this temp table with destination table to find new and changed records.

  4. Upsert the destination table.

But I am facing issues in this approach. The SSIS package failing with below error:

"[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on ADO NET Source returned error code 0xC02090F5. 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."

I removed lookup transformation and loaded data directly into the destination table and it worked fine but it inserted all the records from source table. It should have inserted records for which UpdateDate > max(UpdateDate) of destination table.

Please suggest.

Thank you!

Upvotes: 0

Views: 1031

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31785

Instead of doing a Lookup in SSIS, load your step 2 data into a permanent staging table on the destination server. Then steps 3 & 4 will be performed in a stored procedure rather than a dataflow. It will be faster and less error-prone.

Upvotes: 1

Related Questions