SSIS Package will not get all data from MySQL

Hello Experts Exchange
I have a SSIS package that selects data from MySQL, converts the data and then saves the data into a Oracle database.

However the package will not complete correctly, it stops selecting data from MySQL, it will continue to run for about 5 minutes afterwards and then stop.

The error messages I get are;
[ADO NET Source [2]] Error: The ADO NET Source was unable to process the data. Fatal error encountered during data read.


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

Open in new window


The query I am running is this;
SELECT order_itemid, substr(Stk_item.itemref,1,2) as giftaided, t.transid, t.last_action AS sale_date,
D.loc_ref,
cat1.catName as cat1,
ifnull(cat2.catName,'') as cat2,
ifnull(cat3.catName,'') as cat3,
ifnull(cat4.catName,'') as cat4, 

Sl_orders.price_paid, (Sl_orders.price_paid- Sl_orders.net_price) as vat_amt, Sl_orders.unit_cost, Sl_orders.status, t.pin_no as stf_id 
FROM sl_transaction t,
data_locations D, sl_orders Sl_orders, stk_item Stk_item,
stk_products Stk_products 
left outer join stk_cat as cat1 on cat1.catID = cast((mid(category,length(substring_index(category,'.',1))+2,length(substring_index(category,'.',2))-length(substring_index(category,'.',1))-1)) as decimal) 
left outer join stk_cat as cat2 on cat2.catID = cast((mid(category,length(substring_index(category,'.',2))+2,length(substring_index(category,'.',3))-length(substring_index(category,'.',2))-1)) as decimal) 
left outer join stk_cat as cat3 on cat3.catID = cast((mid(category,length(substring_index(category,'.',3))+2,length(substring_index(category,'.',4))-length(substring_index(category,'.',3))-1)) as decimal) 
left outer join stk_cat as cat4 on cat4.catID = cast((mid(category,length(substring_index(category,'.',4))+2,length(substring_index(category,'.',5))-length(substring_index(category,'.',4))-1)) as decimal) 

WHERE D.locID = t.locID 
And D.locID >=1 and  D.locID <=99999
AND Sl_orders.itemID = Stk_item.itemID 
AND Sl_orders.transID = t.transID 
AND Stk_products.prodID = Stk_item.prodID 
AND D.fax = 'LIVE' 
AND Sl_orders.Status <> 'Void'
and t.last_action >= DATE_ADD(CURDATE(), INTERVAL - 26 day)

Open in new window


I don't think it is my query, when I run it in toad it returns all records in about 1 minute.

I have attached a file of how the package looks;

Does anyone know what I should do to get my package to work?

Regards

SQLSearcher
SSIS-Package.jpg
SQLSearcherAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zephyr_hex (Megan)DeveloperCommented:
It looks like this error could be caused by one of several things, which will be difficult for anyone else to troubleshoot.  Have you considered the suggestions given here ?
Vikas GargAssociate Principal EngineerCommented:
Hello,

I think the error is being raised because one of your columns is being truncated when importing it from source system.

You can manage on error event on source and redirect then to error output rather then failing the package
SQLSearcherAuthor Commented:
Hello
I have exported the data from MySQL to a text file and a SQL Server database table and it has worked.

Is the problem, that it is taking to long to insert into Oracle?

The Provider I am using is the Microsoft OLE DB Provider for Oracle, should I be using another one for faster insert?

Regards

SQLSearcher
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

zephyr_hex (Megan)DeveloperCommented:
The error you were receiving does not lead me to believe the issue was delay or the insert run time.  My initial suspect was a data type mismatch, which potentially would have been resolved by exporting to text.
SQLSearcherAuthor Commented:
Hello
I did get the following error message;

The component reported the following warnings:

Warning at {1FDECAF6-5FA3-4E7E-9DB6-428A5F06FF5E} [OLE DB Destination [58]]: Cannot retrieve the column code page info from the OLE DB provider.  If the component supports the "DefaultCodePage" property, the code page from that property will be used.  Change the value of the property if the current string code page values are incorrect.  If the component does not support the property, the code page from the component's locale ID will be used.

Open in new window



When I set the setting AlwaysUseDefaultCoePage to true, I thought it was working correctly.

How do I resolve the issue?

Regards

SQLSearcher
SQLSearcherAuthor Commented:
Hello Expert Exchange
There was a truncation issue, when I resolved that the data was still slow and the package still ended with an error.
 
To get the package to work I change the destination from a OLE DB Destination to an ODBC Destination, which made the data go much quicker and stopped the package from erroring.

Regards

SQLSearcher

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SQLSearcherAuthor Commented:
Found part of the solution by myself, Thank you to all that tried to help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.