how to manage the primary key and foreign key in ADO NET Destination for mysql
hi, sorry for my english I have a mysql database that has the same structure as the database sql server 2008 (the same tables, same fields ,...). I create a linked server (MYSQL_LINKED) in sql server. I created an SSIS package as in this tutorial : http://dbperf.wordpress.com/2010/07/23/sql-server-integration-services-2008-ssis-and-mysql/#comment-198 the problem in the tutorial is that it asks me to recreate the table over there not handle primary key and foreign keys. possible to give me a more concrete example that manages a database and not only a single table. thanks
November 10th, 2010 5:06am

I'm not sure about your question , could you explain what you want to do with PK-FK s?http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
November 10th, 2010 5:13am

ok the problem when I double click on ADO NET Destination (mysql). I get a script that represents the selected table in ADO NET Destination (sqlserver). I want to know how to manage the primary key. because the script proposed by ADO there is no primary key. just the table fields selected thanks
November 10th, 2010 5:23am

do you want insert auto-increment value in the primary key?http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
November 10th, 2010 5:37am

no it's not that. I explained again. put yourself in my place. I have a mysql database structure That has The Same As The database sql server 2008 (The Same table, same fields ,...). I make changes, insertions and deletions in the database SQL Server . I want whenever I make updates in sql server, these updates will be made in the mysql database. What should I do?. propose a solution. thanks
November 10th, 2010 5:53am

you should just select updated data from sql server table and then with execute sql task ( in control flow ) update appropriate data row in the mysql table. you can pass input parameters to execute sql task too. does it make sense to you ? let me know where you need more explanation?http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
November 10th, 2010 6:34am

I can not understand is that http://dbperf.files.wordpress.com/2010/07/ssis16.jpg why ADO NET destination proposes to create a table?
November 10th, 2010 6:35am

I can not understand is that http://dbperf.files.wordpress.com/2010/07/ssis16.jpg why ADO NET destination proposes to create a table? you should not create table, you can select an available table from list of table names in ado.net destination.http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
November 10th, 2010 6:47am

Thank you Reza Raad. I managed to run the ADO NET destination This must be done: mysql> SET GLOBAL sql_mode = 'ANSI' I have one problem is how to tell SSIS to do (update) when there are changes. must make trigger with c #?
November 10th, 2010 7:25am

I have one problem is how to tell SSIS to do (update) when there are changes. must make trigger with c #? after creating SSIS package, you should deploy it on server. then you can run SSIS package from a .NET application or by a sql server job but simplest way which works from any application is that you can run it with a command line of DTEXEC. for samples of DTEXEC command lines look here: http://msdn.microsoft.com/en-us/library/ms162810.aspxhttp://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
November 10th, 2010 12:21pm

HI, thank you for your reply. what I want is that whenever there is a change in the database sql server there must 'is the same change in mysql. I must to use the lookup? thanks
November 11th, 2010 3:07am

I don't recommend this way! this will be resource consuming. and also you should be aware of parallelism of running ssis packages when two table data changes simoultanously. I recommend to Schedule Synchronization process. you can deploy ssis package, create a sql server job and schedule job daily or hourly. in this way you can set schedule on times which load on server is low, and also you don't need to be worry about conflict on parallelism because only one package will run at a time.http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
November 11th, 2010 3:17am

I do not understand. me when I run my Package.dtsx for the first time. there 's no problem. the second time running that there has a violation of primary key . I juste want to know how to tell SSIS to update or just delete or insert a new data because I make changes in sql server so the same change must exist in mysql look at this link:http://www.insideit.fr/post/2010/06/17/Lookup-simple-sur-SSIS
November 11th, 2010 3:26am

your question has two side: one side is about how an when run the ssis package, my previous question was about this step. with sql server agent job you can schedule it. another side is problems inside the data flow , for UPSERT( Update /Insert ) problem, you can use Lookup Transform for UPSERT purpose, but in your case lookup table is mysql table which you connect to it with ado.net connections. Lookup transform has a limitation that you can only use oledb connections or cache connections(in SSIS 2008). so If you use SSIS 2008, you can do it with two data flow task one after another, in first one, use ado.net data source for mysql table, and fill data in cache transform. in second one , use a source for sql server table, then add a lookup transform which poiniting to the cache file from previous data flow. in lookup transform editor, set redirect no match rows to no match output. then connect no match output to ado.net destination - this will do INSERT and connect match outputs to a recordset destination which you should set an object type variable to store data on it. after second data flow, add a foreach loop container, and use enumerator as ado enumerator, and set object type variable from previous data flow task there. now add an execute sql task inside foreach loop container and write UPDATE command in the sqlstatement property with ado.net connection, you can set input parameters for it . this seems long, but if you use ado.net connection for mysql, you should pass this long way, or script all process of update.http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
November 11th, 2010 3:35am

you know I connected sql server to mysql the first is inserted without any problem. when you talk about (DTExec) that I would do at the end to run the Package.dtsx. for now I gotta do the update and insert, since (mysql) feeds from (sql server). for your solution it seems a little complicated, I do not think I could do without documentation would it be possible to give me something that will help me thanks
November 11th, 2010 3:49am

if your connection to mysql was with OLEDB, this was very simpler to you, just you could use an OLEDB Command after lookup transform to handle Updates. but you can not use ado.net connections with OLEDB Command, this is the reason of why I suggested you that long way. Don't worry about it, just follow my suggestions and tell me where you have problem. I will help you till you get your desired result.http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
November 11th, 2010 7:56am

I work on this tutorial :http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis-design-pattern-incremental-loads.aspx who make the connection between two database (update). I try to do it for mysql sql server I can not. the lookup does not propose me a (Microsoft OLE DB Provider for mysql). what should I do ? thanks
November 11th, 2010 8:07am

because I create a connection (Mysql_Link.root) in SSIS but when I click lookup can not find this connection, or to create it a second time
Free Windows Admin Tool Kit Click here and download it now
November 11th, 2010 8:14am

also know that I created the connection sql server mysql with this tutorial:http://dbperf.wordpress.com/2010/07/23/sql-server-integration-services-2008-ssis-and-mysql/#comment-200 thanks
November 11th, 2010 8:21am

the lookup does not propose me a (Microsoft OLE DB Provider for mysql). what should I do ? This is exactly the reason of why I suggested you that long solution! you can use cache component if you use SSIS 2008, and you can not use lookup if you have SSIS 2005 and your connection type is ado.net. Andy's article is good reference, but you have to changes some portions for lookup and oledb command transforms, because your connection is ado.net. look at this article from SSIS Team blog, about how to face problems with ado.net connections when you work with mysql: http://blogs.msdn.com/b/mattm/archive/2009/03/02/how-do-i-do-update-and-delete-if-i-don-t-have-an-oledb-provider.aspx http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
November 11th, 2010 8:26am

because I create a connection (Mysql_Link.root) in SSIS but when I click lookup can not find this connection, or to create it a second time this is because you can not use ado.net connections in Lookup Transform.http://www.rad.pasfu.com
November 11th, 2010 8:29am

ok I will see your suggestion thanks
Free Windows Admin Tool Kit Click here and download it now
November 11th, 2010 8:36am

I have SSIS 2008
November 11th, 2010 8:37am

I have SSIS 2008 OK, so make first data flow task which read ado.net source ( from mysql table) , and fill result in Cache Transform. then you can use this cache connection manager in second data flow task in lookup connectionhttp://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
November 11th, 2010 8:44am

" but you have to changes some portions for lookup and oledb command transforms, because your connection is ado.net ." my connection is ado.net, because that tutorials offers me ADO.NET but if there has another connection to the lookup works well I am involved
November 11th, 2010 8:45am

my source is sql server. the destination is mysql. I don't undertand this ligne:and fill result in Cache Transform.
Free Windows Admin Tool Kit Click here and download it now
November 11th, 2010 8:55am

OK, I will explain it step by step, First step: add a data flow task to control flow, add an ADO.NET Source which pointing to the MySQL table. then add a Cache Transform and connect data path ( green arrow ) from ado.net source to this cache transform set a new cache connection in cache transform ( check the "use file cache" and set a physical address for cache file, you should also set an index column for cache ) , then map columns there. in this Fist step you will Create a cache file which contains data of mysql table. if you did it right tell me to go through next step.http://www.rad.pasfu.com
November 11th, 2010 9:00am

I will do this tutorial:http://blogs.msdn.com/b/mattm/archive/2008/03/03/connecting-to-mysql-from-ssis.aspx after this i will ask you thanks
Free Windows Admin Tool Kit Click here and download it now
November 11th, 2010 9:02am

OK, I will explain it step by step, First step: add a data flow task to control flow, add an ADO.NET Source which pointing to the MySQL table. then add a Cache Transform and connect data path ( green arrow ) from ado.net source to this cache transform set a new cache connection in cache transform ( check the "use file cache" and set a physical address for cache file, you should also set an index column for cache ) , then map columns there. in this Fist step you will Create a cache file which contains data of mysql table. if you did it right tell me to go through next step. http://www.rad.pasfu.com OK BUT my source is SQL SERVER NOT MYSQL I WILL EXCHANGE
November 11th, 2010 9:08am

OK BUT my source is SQL SERVER NOT MYSQL I WILL EXCHANGE Don't do that! I know that your source is sql server. in the First step you should provide data source for lookup transform , and this is why I said that use MySQL source in first data flow. in the second data flow is where you will put sql server as source and mysql as lookup table!http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
November 11th, 2010 9:14am

I put a Data Flow Task. I double click in the Data Flow Task. I could ADO NET Source (source sql server). I connect ADO NET Source to Transformation Cache. I double click in Transformation Cache. and I do not know what i must to do after in this transformation in the cache
November 11th, 2010 9:21am

First : use ado.net source with MYSQL TABLE not sql server table! look at my previous post. doubl click on connection manager, click on New to create new cache connection in cache connection manager editor, check the "Use file cache" checkbox and browse a path to save cache file there, in the columns tab, set PK of mysql table as index ( set index position as 1 ) then OK http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
November 11th, 2010 9:27am

ok when i click on use the cache file and then browse I can not find the file I have to look where?
November 11th, 2010 9:58am

ok when i click on use the cache file and then browse I can not find the file I have to look where? Just go to the directory you want to put cache file there, and type a name for cache file, then click ok. you don't need to select cache file. this only need an address to create cache file there.http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
November 11th, 2010 10:01am

good OK thanks i have put my primary key in position 1 and i clic OK thanks . what is the seconde step please
November 11th, 2010 10:07am

second Step: go back to control flow add another data flow task, connect precedence constraint ( green arrow ) from first data flow task to second data flow task then double click on second data flow task add an OLEDB Source ( setup a new oledb connection to sql server database ) and point it to SQL Server table. then add a Lookup transform, connect green arrow from oledb source to lookup. then double click on lookup, and in lookup transformation editor, set Connection Type as "Cache Connection manager" and set "specify how to handle rows with no match entries" with "redirect no match rows to no match output" then in connection tab, select cache connection manager from the drop down list and in columns tab, map joining fields next steps is like Andy's article you mentioned before till you reach the oledb command , when you reached this transform tell me to explain third step. http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
November 11th, 2010 10:16am

ok I will do it and I will back thanks
November 11th, 2010 10:22am

I don't see this ligne : and set "specify how to handle rows with no match entries" with "redirect no match rows to no match output" where can I change this
Free Windows Admin Tool Kit Click here and download it now
November 11th, 2010 10:30am

I put the lookup with Source OLE DB in the (Data Flow Task sqlserver) but when I double clic in lookup and change what yiu said befor I don't see "specify how to handle rows with no match entries" with "redirect no match rows to no match output"
November 11th, 2010 10:34am

I put the lookup with Source OLE DB in the (Data Flow Task sqlserver) but when I double clic in lookup and change what yiu said befor I don't see "specify how to handle rows with no match entries" with "redirect no match rows to no match output" in this picture , look at last combo box there: ( in general tab) http://beyondrelational.com/cfs-filesystemfile.ashx/__key/CommunityServer.Discussions.Components.Files/241/1586.ssis2.pnghttp://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
November 11th, 2010 2:06pm

I create a post with whole process in pictures, look here: http://rad.pasfu.com/index.php?/archives/31-Update-Insert-UPSERT-MySQL-Table-From-SQL-Server-Table-SSIS.htmlhttp://www.rad.pasfu.com
November 11th, 2010 5:22pm

I create a post with whole process in pictures, look here: http://rad.pasfu.com/index.php?/archives/32-Update-Insert-UPSERT-MySQL-Table-From-SQL-Server-Table-SSIS.html http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
November 12th, 2010 1:09am

hello thank you for the article. you're really nice. I will bother you a little longer. fields (text type) are not recognized in the cache transform. ie in the columns of cache trasform. it brings back all the fields except for this (type text)
November 14th, 2010 4:32am

hello thank you for the article. you're really nice. I will bother you a little longer. fields (text type) are not recognized in the cache transform. ie in the columns of cache trasform. it brings back all the fields except for this (type text)
Free Windows Admin Tool Kit Click here and download it now
November 14th, 2010 4:32am

I hadn't such experience with text fields, but you can convert text field to DT_STR if your character length isn't more than 8000 character.http://www.rad.pasfu.com
November 14th, 2010 4:51am

I hadn't such experience with text fields, but you can convert text field to DT_STR if your character length isn't more than 8000 character.http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
November 14th, 2010 4:51am

thank you for the reply when I try to map in the lookup I get this error TITLE: Microsoft Visual Studio ------------------------------ Unable to map the input column, "ACT_NUMERO" in the lookup column, "ACT_NUMERO" because the data types do not match. ------------------------------ BUTTONS: OK ------------------------------ THANKS
November 14th, 2010 5:07am

thank you for the reply when I try to map in the lookup I get this error TITLE: Microsoft Visual Studio ------------------------------ Unable to map the input column, "ACT_NUMERO" in the lookup column, "ACT_NUMERO" because the data types do not match. ------------------------------ BUTTONS: OK ------------------------------ THANKS
Free Windows Admin Tool Kit Click here and download it now
November 14th, 2010 5:07am

when I try to map in the lookup I get this error TITLE: Microsoft Visual Studio ------------------------------ Unable to map the input column, "ACT_NUMERO" in the lookup column, "ACT_NUMERO" because the data types do not match. what is data type of this column in MySQL table and SQL Server table? http://www.rad.pasfu.com
November 14th, 2010 5:19am

when I try to map in the lookup I get this error TITLE: Microsoft Visual Studio ------------------------------ Unable to map the input column, "ACT_NUMERO" in the lookup column, "ACT_NUMERO" because the data types do not match. what is data type of this column in MySQL table and SQL Server table? http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
November 14th, 2010 5:19am

in mysql varchar(10) and in sql server varchar(10)
November 14th, 2010 5:28am

I think that a relationship with DT_WSTR. I do not know if I must to convert the columns of the destination or source
Free Windows Admin Tool Kit Click here and download it now
November 14th, 2010 5:31am

check the datatype of this column in each data stream in Data Flow, I mean this: right click on oledb source and select advanced editor, and then in Input and Output Properties, expand oledb source output, then under output columns, select the ACT_NUMERO and see the datatype in properties window listed there. do this for ado.net source for mysql table in previous data flow task too. and now tell me what is data type of ACT_NUMERO in each sourcehttp://www.rad.pasfu.com
November 14th, 2010 5:34am

check the datatype of this column in each data stream in Data Flow, I mean this: right click on oledb source and select advanced editor, and then in Input and Output Properties, expand oledb source output, then under output columns, select the ACT_NUMERO and see the datatype in properties window listed there. do this for ado.net source for mysql table in previous data flow task too. and now tell me what is data type of ACT_NUMERO in each sourcehttp://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
November 14th, 2010 5:34am

for ADO.NET source is chaîne Unicode [DT_WSTR].lenght 15 and for oledb is chaîne [DT_STR].lenght 10 thanks
November 14th, 2010 5:44am

for ADO.NET source is chaîne Unicode [DT_WSTR].lenght 15 and for oledb is chaîne [DT_STR].lenght 10 thanks
Free Windows Admin Tool Kit Click here and download it now
November 14th, 2010 5:44am

both columns should have equal SSIS data type, both DT_STR or both DT_WSTR if any of columns is different you should change type of columns with data conversion transformation right after the source ( if you want to change sql server column data type, put data conversion right after oledb source in second data flow, if you want to change mysql column data type, put data conversion right after ado.net source in first data flow and also change data type of cache connection manager)http://www.rad.pasfu.com
November 14th, 2010 5:50am

for ADO.NET source is chaîne Unicode [DT_WSTR].lenght 15 and for oledb is chaîne [DT_STR].lenght 10 thanks so , add a data conversion transformation after oledb source and convert ACT_NUMERO to DT_WSTR http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
November 14th, 2010 5:51am

I added a patch (data conversion). I have attached to (Source OLE DB). and I changed the type of (ACT_NUMERO). but I still get the error. is what I need to change the type for all columns.???????????????????????
November 14th, 2010 6:00am

I added a patch (data conversion). I have attached to (Source OLE DB). and I changed the type of (ACT_NUMERO). but I still get the error. is what I need to change the type for all columns.???????????????????????
Free Windows Admin Tool Kit Click here and download it now
November 14th, 2010 6:00am

Just columns which are joining fields between two tables needs to be in equal type. did you set length of columns equal? I mean what length you did set in data conversion transform ? it should be like ado.net column ( I think that was DT_WSTR ( 15) )http://www.rad.pasfu.com
November 14th, 2010 6:04am

Just columns which are joining fields between two tables needs to be in equal type. did you set length of columns equal? I mean what length you did set in data conversion transform ? it should be like ado.net column ( I think that was DT_WSTR ( 15) )http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
November 14th, 2010 6:04am

you know the problem is that now. I changed the type (ACT_NUMERO) task (source OLE DB) in (Properties entry and exit). I managed to map. but the component (OLE DB Source) contains an error. erreur is Unable to convert column ACT_NUMERO "because of data types and non-Unicode string unicode
November 14th, 2010 6:12am

you know the problem is that now. I changed the type (ACT_NUMERO) task (source OLE DB) in (Properties entry and exit). I managed to map. but the component (OLE DB Source) contains an error. erreur is Unable to convert column ACT_NUMERO "because of data types and non-Unicode string unicode
Free Windows Admin Tool Kit Click here and download it now
November 14th, 2010 6:12am

do not change type in properties, after oledb source , add a data conversion transformation, then choose ACT_NUMERO from columns and choose target type as DT_WSTR, set length too. then you will have a new column with match data type when you connect output of data conversion to lookup transformhttp://www.rad.pasfu.com
November 14th, 2010 6:23am

I added the (Data conversion) but I still get the error
Free Windows Admin Tool Kit Click here and download it now
November 14th, 2010 7:40am

I used the ADO.NET source (task flow data lookup) and it works it does not give me error. is that I can continue to work with it. I am not obliged to work with OLE DB for SQL Server source??????????????????????????????????????
November 14th, 2010 8:16am

I used the ADO.NET source (task flow data lookup) and it works it does not give me error. is that I can continue to work with it. I am not obliged to work with OLE DB for SQL Server source??????????????????????????????????????
Free Windows Admin Tool Kit Click here and download it now
November 14th, 2010 8:16am

I used the ADO.NET source (task flow data lookup) and it works it does not give me error. is that I can continue to work with it. I am not obliged to work with OLE DB for SQL Server source??????????????????????????????????????
November 14th, 2010 8:16am

I used the ADO.NET source (task flow data lookup) and it works it does not give me error. is that I can continue to work with it. I am not obliged to work with OLE DB for SQL Server source?????????????????????????????????????? If your problem solved with ADO.NET Source for sql server table so continue in this way, there are no major difference in this case .http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
November 14th, 2010 2:01pm

hello, I just tried to work with two tables that you offered me. but I still get errors, no luck. when I click on (ADO DESTINATION) the (task of data flow lookup) and I click preview I get the following error TITLE: Microsoft Visual Studio ------------------------------ ERROR [42000] [MySQL] [ODBC 5.1 Driver] [mysqld-5.0.51b-community-nt] You Have an error in your SQL syntax; Check the manual That corresponds to your MySQL server version for The Right syntax to use near ' "mysqltable" 'at line 1
November 15th, 2010 3:48am

if you used ODBC Connection for MySQL, set tablename as mysqltable exactly without double quote surrounded. http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
November 15th, 2010 3:53am

thanks I can see my table know but I still get an error. in progression tab I get this: [ADO NET Destination [301]] Error: "An exception occurred while inserting data, the message returned by the supplier is: ERROR [42000] [MySQL] [ODBC 5.1 Driver] [mysqld-5.0. 51b-community-nt] You Have an error in your SQL syntax; Check the manual That corresponds to your MySQL server version for The Right syntax to use near '"ID", "FirstName" "LastName") VALUES (2,' Mark ',' knot ')' at line 1 " and this: [SSIS.Pipeline] Error: "Error code DTS_E_PROCESSINPUTFAILED SSIS. ProcessInput component "ADO NET Destination" (301) failed with error code 0xC020844B while processing input "ADO NET Destination Input" (304). The identified component returned an error ProcessInput. This error is specific to the component, is fatal and will cause the task of stopping the flow of data. Error messages can be sent in advance with information indicating the reason for the failure. " my ADO DESTIONATION is in RED
November 15th, 2010 4:01am

in the connection manager panel, double click on your mysql connection and take an screenshot from there also take another screenshot from ado.net destination editor upload these screenshots in a free picture uploading website and let me know links addresses.http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
November 15th, 2010 4:12am

is what I can do a remote desktop connection. I think it would be better. how can I give you my email?
November 15th, 2010 4:13am

mail it to me: a dot raad dot g at gmail dot comhttp://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
November 15th, 2010 4:17am

I'll pass my email on the article. you gave me
November 15th, 2010 4:21am

I just send you my email I do not know if you received it in the article because I failed to send a comment
Free Windows Admin Tool Kit Click here and download it now
November 15th, 2010 4:34am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics