April 26, 2011 at 9:55 am
I was attempting to use SSIS to use MySQL as a data source. I originally had created the package in 2008, but the server it needs to go on is 2005. The package I created for 2008 works fine. The package for 2005 is getting a time out error.
I am using the DataReader Source component and the the data source as ADO.Net. I tried changing to the SQL timeout to 600 seconds, but it did make a difference. The 'ODBC' that I am using is unusual in the fact that it actually hitting a web site to pull the data.
Any suggestions would be appreciated. Thanks ahead of time.
Error: 0xC0047062 at Data Flow Task, SN Data Source [31]: System.Data.Odbc.OdbcException: ERROR [HYT00] [DataDirect][ODBC OpenAccess SDK driver][OpenAccess SDK SQL Engine]Query Timeout expired[10246]
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(IDTSManagedComponentWrapper90 wrapper)
Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "SN Data Source" (31) failed the pre-execute phase and returned error code 0x80131937.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "OLE DB Destination" (149)" wrote 0 rows.
Task failed: Data Flow Task
April 26, 2011 at 1:54 pm
I don't know what you mean by "hitting a web site" to get the data, and/or if it's related to the OpenAccess DataDirect driver you're using, but I have created many packages that successfully retrieve data from MySQL 5.x databases using the MySQL ODBC driver downloadable from the MySQL website.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 26, 2011 at 2:58 pm
The data is offsite and the vendor has supplied an ODBC that will get the data. I do not understand all the in's and out's on how the ODBC works, but in configuring the ODBC, we have to provide the website for the application that we use (i.e., https://server.service-now.com).
April 26, 2011 at 4:22 pm
I tried changing to the SQL timeout to 600
AFAIK the CommandTimeout property of the DataReader Source is what you want to change. It's on the "Component Properties" tab. Is that what you meant? 0 = wait indefinitely.
One other thing to check, when you setup the ADO.NET Connection Object, did you click the "Test Connection" button? Was it successful?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 27, 2011 at 9:01 am
The time out was originally set to zero. And I did test the connection and it was successful. I am hoping to get the DB moved to an 2008 instance since I know that the 2008 version of my ETL process works.
Thanks.
April 27, 2011 at 9:26 am
I just noticed that there is a command timeout property for the DataReader source component. I changed that to zero and it works now.
April 27, 2011 at 9:32 am
I thought that's what I said 😀 Glad you found it!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 6, 2011 at 11:37 am
I can get the package that transports data from sql server to mysql to run from BIDS, but when I run as a sql server agent job I get the following errors, but not on every execution:
Message
Executed as user: US\SQLDBAdmin. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 3:55:00 PM Error: 2011-06-03 15:55:01.16 Code: 0xC00291EC Source: Execute SQL Task 1 Execute SQL Task Description: Failed to acquire connection "mysqli01_test_connection". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:55:00 PM Finished: 3:55:01 PM Elapsed: 0.188 seconds. The package execution failed. The step failed.
Any ideas, I am stumped?
June 6, 2011 at 11:46 am
Just a stab in the dark:
MySQL requires that each login be associated with an IP address or range. Is your server coming from an IP that is in the range the login you're using allows?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 6, 2011 at 12:00 pm
Yes, I checked the ip/host and it is correct. I was just running some tests and if I am logged into the myssql server with the sqldbadmin account, the job runs, but if I am not logged in, it doesn't run. I set up a proxy account for sqldbadmin and am using that to run the job.
Message
Executed as user: US\SQLDBAdmin. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 1:55:00 PM Error: 2011-06-06 13:55:00.61 Code: 0xC0208452 Source: Data Flow Task ADO NET Destination [115] Description: ADO NET Destination has failed to acquire the connection {8F4B12C9-4096-47D3-B842-6771F6EE4374}. The connection may have been corrupted. End Error Error: 2011-06-06 13:55:00.61 Code: 0xC0047017 Source: Data Flow Task SSIS.Pipeline Description: component "ADO NET Destination" (115) failed validation and returned error code 0xC0208452. End Error Error: 2011-06-06 13:55:00.61 Code: 0xC004700C Source: Data Flow Task SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2011-06-06 13:55:00.61 Code: 0xC0024107 Source: Data Flow Task Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 1:55:00 PM Finished: 1:55:00 PM Elapsed: 0.422 seconds. The package execution failed. The step failed.
March 20, 2012 at 6:27 am
Do we require to open the 443 port for Service Non instance for ODBC connection with service now?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply