ado.net destination editor + no-view
-
Thursday, February 07, 2013 4:54 AM
Hi, I am unable to select view from ADO.net Destination editor in SSIS. Though i can select table, I am unable to map that as well. Any idea???
i am using odbc connection, configured connection manager for ado.net source and destination. and its connecting to respective db. my scenario is to insert frm mssql to mysql.
All Replies
-
Thursday, February 07, 2013 5:05 AM
You can't use view as destination either with oledb or ado.net connections.
VIEW is a virtual table, defined by a query, that does not exist until it is invoked by name in an SQL statement.
Understand the difference betweena ado.net and oledb and use accordingly.
http://www.sqlservercentral.com/Forums/Topic1262057-1292-1.aspx
Regards,Eshwar.
Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.
-
Thursday, February 07, 2013 5:31 AMThanks fr the reply. Bt my issue is not solved, I am using ADO.Net connection with odbc data provider. In ADO.net destination editor, I could select the connection manager and the corresponding table that is required, but I am unable to map it. i.e I am unable to map from a column of the table in mssql to a column of the table in mysql. And therefore I am unable to insert to table that has more than one column now. Could you please suggest what could be the issue here..?
-
Thursday, February 07, 2013 5:34 AM
You mean in the dataflowtask you are not able to map the source and destination? Can you give us more details if possible screenshot of the dataflowtask.
Regards,Eshwar.
Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.
-
Thursday, February 07, 2013 5:59 AM
I am afraid I am not able to upload screenshot until they verify my account. In data flow task, I am able to map source and destination. but in ADO.Net destination editor, under 'mappings' tab columns are not getting mapped.
Scenario: To insert data from mssql to mysql
Solution found: Create ssis package
Issue: While creating ssis package in microsoft business dev studio, I am not able to map two tables. I have configured two connection managers. ADO.Net source editor is going good. But in ADO.Net Destination editor, I am unable to click "ok" once I enter the table name (of mysql).
i.e. When I select connection manager and table in ado.net destination editor, it throws an error as below:
TITLE: Microsoft Visual Studio
------------------------------
Error at Data Flow Task [ADO NET Destination [16]]: Failed to get properties of external columns. The table name you entered may not exist, or you do not have SELECT permission on the table object and an alternative attempt to get column properties through connection has failed. Detailed error messages are:
ERROR [HY010] [MySQL][ODBC 5.2(w) Driver][mysqld-5.1.66-log]
ERROR [HY010] [MySQL][ODBC 5.2(w) Driver][mysqld-5.1.66-log]
ERROR [HY010] [MySQL][ODBC 5.2(w) Driver][mysqld-5.1.66-log]
Object reference not set to an instance of an object.
------------------------------
ADDITIONAL INFORMATION:
Pipeline component has returned HRESULT error code 0xC0208457 from a method call. (Microsoft.SqlServer.DTSPipelineWrap)
-
Thursday, February 07, 2013 6:07 AM
ok check if this resolves the issue the
Go to propeties of the ADO.NET destination component and set "ValidateExternalMetadata" to FALSE if it TRUE.
Regards,Eshwar.
Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.
-
Thursday, February 07, 2013 6:22 AM
ValidateExternalMetadata was set to False, but after changing it to true, did not solve the issue..
I suspect the issue could be either because
1. While configuring the connection manager, I used USER DSN instead of SYSTEM DSN as I was unable to store the password in SYSTEM DSN CONNECTION STRING. But with user dsn , connection is working fine.
2. sql mode in mysql is blank. (silly reason)
If you could help me with this error, would be much appreciated.
Thankyou,
-
Thursday, February 07, 2013 6:32 AM
Check this out for configuring:
http://blogs.msdn.com/b/mattm/archive/2008/03/03/connecting-to-mysql-from-ssis.aspx
Regards,Eshwar.
Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.
- Edited by Eswararao C Thursday, February 07, 2013 6:33 AM
-
Friday, February 08, 2013 2:32 AMThank you, Is it necessary to install ado.net connector and see? I am using ODBC Connector 5.2..
-
Wednesday, February 13, 2013 7:57 AMModerator
Hi KirtiJayd,
Both the ODBC and ADO.Net drivers can connect to mysql. Please verify if the MySQL database and table name are in quotes, in that case, please remove the quotes.
Otherwise, Business Intelligence Developement Studio works only in 32bit mode, please see if you have used the 32bit ODBC.
Please refer to the similar thread: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/8be9654e-2d0c-4fd0-a67e-3d9949b294e6/
Thanks,
Eileen
Eileen Zhao
TechNet Community Support
- Edited by Eileen ZhaoMicrosoft Contingent Staff, Moderator Wednesday, February 13, 2013 7:58 AM
-
Thursday, February 14, 2013 10:03 PMHi, The table is not in quotes, and I can see that ODBC is in 32bit mode from the configuration file..
-
Thursday, February 14, 2013 10:08 PMCan you use ole db destination to load data into mysql?
Thanks, hsbal
-
Friday, February 15, 2013 4:17 AMno. oledb destination does not give me option to select mysql.
.png)
