mysql query timeout upped but job still fails about same number of minutes in
-
hi, i changed my mysql based linked server's timeout to N'600' instead of N'0' but my job (sqlagent->ssis) goes down at about the same number of minutes in as when i have it at n'0'. the error i get is shown below.
i'm using an odbc driver (mysql) installed on my sql server to contact a mysql instance at one of our divisions. our division peers have authored a very complex (lotsa joins, used to work before they added data from 2 more erps) query. Before we talk them thru perhaps a denormalization tuning exercise, i wanted to see anyway if for the time being i can read all of the data for one erp.
we believe the restriction shown below has nothing to do with restrictions on their "server" side. does the community have any ideas?
- This topic was modified 3 weeks, 5 days ago by stan.
-
It always makes me wonder why someone would use SSIS to call code in SQL Server that references a linked server to a third-party database system. That just adds complexity where it isn't needed.
Why not connect directly to MySQL from SSIS using an ADO Source, transform the data (as needed, if needed) and then use an OLEDB Destination to push the data into SQL Server?
A distinct disadvantage to using a linked server is the fact that the query will be contained in a single transaction. In SSIS - using a direct connection to MySQL - the pipeline can push the data to SQL Server as each row is retrieved. You can then configure your OLEDB Destination to work in batches to reduce impact on the transaction log and improve performance.
As to your specific issue - it looks like you are not using the correct MySQL driver. It appears you are using the SQL Server native driver - maybe as a wrapper to the MySQL driver? Not sure but that definitely doesn't look like a connection to MySQL.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
by using SSIS you need to also worry about the SSIS execution timeout itself on top of the remote server one
and you also have to worry about the serverwide remote query settings
-
Specify the Columns your want avoid using Select *. Do you need all records returned? maybe use where clause to limit records.
in SSIS adjust CommandTimeOut set it to 0(zero) for unlimited timeout and see if it runs.
-
thx all. going thru ssis via lined server is consistent with the architecture we use on everybody else (25 other erps, im just the messenger).
but as a last resort, later, i'll try what jeffrey recommends.
yes i need all the cols.
what im going to try right now is a select right from t-sql , using openquery and removing anything that could possibly create cross server join behavior. i'll try to determine prior what at serverwide remote query settings means. i dont want to introduce risk for the other 25 erps just because one has written a terrible mysql view that now mushes 3 erps into the underlying tables with no vision to good performance etc.
-
i tried an ado .net source. Not saying i did it right but i picked odbc driver from the list of driver choices. ssis definitely saw my dsn but trying different things from there including dropping down the tables or entering the query myself as a command definitely froze ssis.
-
i finally got this darn thing to run in chunks of dates. right from openquery. not ideal but since they have an index on date, i tried it and got it to run in 2 waves over about 3 yrs. i was reluctant to extend any timeout params (in other software layers) other than the linked server set up specifically for this erp data source. i felt i'd be putting the other 25 feeds at a potential risk.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply