SSIS 2005 package with mysql as destination
-
Hi All,
Can you please tell me if any one has done an ssis package with MYSQL as destination,
sql is the source and mysql is the destination, what destination can be used in 2005 as there is no ado.net destination .
Any help is much appreciated
Thanks,
subash
-
IIRC MySQL does not expose metadata in compliance with what the SSIS OLE DB destination requires. You could look into a third party OLE DB driver, or look into upgrading to SSIS 2008.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
subhashmarti (6/27/2012)
Hi All,Can you please tell me if any one has done an ssis package with MYSQL as destination,
sql is the source and mysql is the destination, what destination can be used in 2005 as there is no ado.net destination .
Any help is much appreciated
Thanks,
subash
Hi Subash,
SQL 2005 doesn't include support for ODBC as a destination and that is what you need. I would recommend you don't waste time looking at OLEDB MySQL drivers. The best MySQL drivers are the ODBC drivers. To use MySQL as destination you will need third-party component. COZYROC provides commercial ODBC Destination component, which has been successfully tested with MySQL and it works great.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
CozyRoc (6/28/2012)
I would recommend you don't waste time looking at OLEDB MySQL drivers.Why not? Do you know of anything wrong with this one? MySQL OLE DB Provider
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
opc.three (6/28/2012)
CozyRoc (6/28/2012)
I would recommend you don't waste time looking at OLEDB MySQL drivers.Why not? Do you know of anything wrong with this one? MySQL OLE DB Provider
- Is it supported by MySQL corp ?
- Is it free ?
- Does it support bulk-load?
Also OLEDB is now considered obsolete technology and will be removed in the future releases of SQL Server. So OLEDB is not recommended going forward.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
CozyRoc (6/28/2012)
- Is it supported by MySQL corp ?Is the ODBC CozyRoc Destination supported by MySQL or Microsoft?
- Is it free ?
Is the ODBC CozyRoc Destination free?
- Does it support bulk-load?
Not sure. It doesn't look like it.
Also OLEDB is now considered obsolete technology and will be removed in the future releases of SQL Server. So OLEDB is not recommended going forward.
The deprecation announcement I think you're referring to has to do with SQL Server OLE DB, and it does not take effect until the next version of SQL Server released, if even then. The roadmap is nowhere at the moment. I would also point out that VB6 has been on the chopping block for years, but Microsoft bent recently and decided to continue support for it in future OS's because of the continued use of VB6 and breadth of the installed codebase. OLE DB is not going anywhere anytime soon, certainly not from the Windows stack. I personally wouldn't do new development with OLE DB if the tooling surrounding SQL Server were in line with the recent deprecation announcement (SSIS 2005 included) but it's not and it won't be for quite some time.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Is the ODBC CozyRoc Destination supported by MySQL or Microsoft?
No, it's not. But you see someone from COZYROC posting here to this person's question, right? I don't see anyone from cherry city posting or even caring about whether their software works properly with SSIS. The difference is WE CARE.
Is the ODBC CozyRoc Destination free?
No, it's not. My point is the solution you offer doesn't offer any benefit in this sense.
Not sure. It doesn't look like it.
I can guarantee you it doesn't support it. Not because it is impossible, but because OLEDB is complicated and if even Oracle and IBM are not willing to support it, then you probably understand none else will. We have tested COZYROC ODBC Destination component with MySQL and it is 30x faster compared to regular ODBC or OLE DB.
The deprecation announcement I think you're referring to has to do with SQL Server OLE DB, and it does not take effect until the next version of SQL Server released, if even then. The roadmap is nowhere at the moment. I would also point out that VB6 has been on the chopping block for years, but Microsoft bent recently and decided to continue support for it in future OS's because of the continued use of VB6 and breadth of the installed codebase. OLE DB is not going anywhere anytime soon, certainly not from the Windows stack. I personally wouldn't do new development with OLE DB if the tooling surrounding SQL Server were in line with the recent deprecation announcement (SSIS 2005 included) but it's not and it won't be for quite some time.
Is there a newer version of VB6 around? I don't think so. Therefore it is a dead-end. You offer the same argument as the organizations still using COBOL for development. Yes, it will be probably around for many years to come, but who cares? The technical people and the younger generation have moved to different platforms long time ago. You sound a bit nostalgic, but you have to accept the realities. OLEDB is dead-end. Period.
If you are interested to hear the same from the horses-mouth , I would recommend this post[/url]. It was written by one of the original architects of OLEDB. If he says it is gone, it is gone.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
There was a new version of VB6. It was called VB.net, i.e. Visual Basic 7.0, and legions of VB6 developers and the companies they work for have since rejected it. New projects are being funded in VB6, and COBOL too by the way, every day. It's just one of those sides of the industry lots of software vendors don't like to talk about all that much 😉
And no, I am not nostalgic about technology in general. Quite the contrary actually, but I do know my history. I very much like and try to embrace new technology. However, when it comes to OLE DB I am a realist. I have been developing a lot of SSIS lately, and a lot for SSIS 2005. I am sure you're aware what that means in terms of my chances for using an ODBC Destination with built-in SSIS components in place of OLE DB. Regardless of what the creator of OLE DB said (I did not read the article you linked to) it has a life of its own, it has a place in this world and will continue to for a very long time to come. Your website looks nice, and I can tell you care from your posts here and on other threads too so don't take me wrong, but bashing other technologies with comments like "wasting time" is less than endearing.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Oh, boy. Saying VB.NET is the new version of VB6 is like saying VB Script is VB6-light. VB.NET is very different language and comparing to VB6 or even implying they are somehow related doesn't make much sense. VB.NET is actually closer to C# with different keywords here and there.
Now you say you like trying new technology, but it doesn't seem you have tried any COZYROC components. If you have tried, you would have known OLEDB is not that important because we did implement replacement/enhanced components which support other technologies other than OLEDB. And COZYROC library supports SQL 2005, 2008, 2008R2 and now 2012.
Btw when I said it is a waste of time trying the OLEDB drivers, I was actually referring to the OLEDB drivers provided by MySQL . They are very low-quality and doesn't work properly. Perhaps the third-party driver you have stated works better... I don't know . I haven't tried it myself.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
CozyRoc (6/28/2012)
Oh, boy. Saying VB.NET is the new version of VB6 is like saying VB Script is VB6-light. VB.NET is very different language and comparing to VB6 or even implying they are somehow related doesn't make much sense. VB.NET is actually closer to C# with different keywords here and there.That's exactly the point. VB.net was rejected by large swaths of the VB6 community. Here is a a great read from the latest MSDN magazine on the topic: The Silent Majority: Why Visual Basic 6 Still Thrives by David Platt
Now you say you like trying new technology, but it doesn't seem you have tried any COZYROC components. If you have tried, you would have known OLEDB is not that important because we did implement replacement/enhanced components which support other technologies other than OLEDB. And COZYROC library supports SQL 2005, 2008, 2008R2 and now 2012.
Wonderful. I may try them some day, but for now it would only be for the sake of it. At present I spend most of my free time learning the new features in SSIS 2012. I cannot say I have found a pressing need to venture outside the built-in SSIS components, whether it be in SSIS 2005, 2008/R2 or 2012.
Btw when I said it is a waste of time trying the OLEDB drivers, I was actually referring to the OLEDB drivers provided by MySQL . They are very low-quality and doesn't work properly. Perhaps the third-party driver you have stated works better... I don't know . I haven't tried it myself.
I wouldn't know either, I have never used it, nor do I intend to. Their site looks crappy compared to yours. I mostly took umbrage at your dismissive tone towards OLE DB in general. When I had to retrieve and load data into MySQL 5 using SSIS 2008 I do not even remember what I used it has been so long, but I can tell you I did it without using any third-party components.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Interesting article. I agree VB6 is a great solution. And that's why I said there is no newer version. VB.NET we both agree is a different beast.
Regarding COZYROC's components, it is puzzling why you wouldn't at least out of curiosity check what is available? It is free to test and play inside Visual Studio (BIDS). No license key is required. Perhaps you might discover there is a better solution for what you are doing. There is no way to know, without trying it first. Many of the components are general-purpose components, which are not industry-specific. I hope you don't suffer from MS-only syndrome 😉
I find it even more puzzling why you would suggest OLEDB drivers you haven't tried yourself at all?!? I expected at least you have tried them and you had counterpoint . Btw SQL 2008 contains ADO.NET Destination component, which supports ODBC drivers. However because it is ADO.NET it doesn't know anything about ODBC bulk-load API. So you are still better off using COZYROC ODBC Destination component because it supports both the standard and bulk-load API.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
CozyRoc (6/28/2012)
Regarding COZYROC's components, it is puzzling why you wouldn't at least out of curiosity check what is available? It is free to test and play inside Visual Studio (BIDS). No license key is required. Perhaps you might discover there is a better solution for what you are doing. There is no way to know, without trying it first. Many of the components are general-purpose components, which are not industry-specific. I hope you don't suffer from MS-only syndrome 😉I suffer from whatever my employers and clients suffer from in terms of the shop's policies. In most shops this amounts to 'no third party components unless they pass a Congressional hearing' making them not worth the trouble. So, I typically go for the amenable, included at no cost, portable solutions afforded by the built-in components. Others may be having a different experience out there.
I find it even more puzzling why you would suggest OLEDB drivers you haven't tried yourself at all?!? I expected at least you have tried them and you had counterpoint.
I didn't suggest them. I asked you why you wouldnt waste time looking for one, any one, and what you thought of the first one that popped up for me after googling.
Btw SQL 2008 contains ADO.NET Destination component, which supports ODBC drivers. However because it is ADO.NET it doesn't know anything about ODBC bulk-load API. So you are still better off using COZYROC ODBC Destination component because it supports both the standard and bulk-load API.
In terms of SSIS 2005, if performance were of great concern when loading into MySQL I might look into using a native MySQL tool like LOAD DATA INFILE after writing a file to disk, or I would write a Script Component in my Data Flow that worked with one of the native MySQL drivers to insert rows directly, maybe in batches to improve performance. An OLE DB driver that would work with the OLE DB Destination would be something to explore as well, or I could resort to using a Script Component. Lots of built in options to explore.
In terms of SSIS 2008, your point about ODBC via ADO.NET in SSIS 2008 is why I continue to use OLE DB in SSIS 2008 😀 If performance were of no great concern ODBC via ADO.NET could be just fine, else the other options stated for 2005 would be on the table.
In terms of SSIS 2012 an ODBC Destination was added, and it supports bulk insert as long as the driver implements the interface. SNAC obviously will. I cannot find if the MySQL ODBC driver does or not. How does Cozyroc ODBC Destination implement bulk loads to MySQL? Does MySQL implement an ODBC compliant bulk load interface, or are you abstracting that in your component and queuing up n inserts to ship to the connection in a single batch?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
It is up to YOU to convince your clients to use third-party components when helpful/needed. Using such functionality can actually optimize your development and reduce the total cost for your clients. COZYROC wouldn't exist a day longer if there was no value provided. We don't have connections inside the organizations to help us sell them. Our customers have tried the product first and decided to purchase because they like what they see. Not because their boss told them to, it is the other way around. They go to their bosses and ask them to make the purchase for them.
You asked me what do I think about the first such result you came up with. This is bizarre. Why would you think I should know everything people find on the internet? I can only stand behind the product my company provides to SSIS users. I wouldn't suggest something, if I'm not sure it works. If you are offering solutions to people on the forum, you'd better test them first. Otherwise what's the point? Everyone who sees this discussion would think the OLEDB driver you suggest works (and it might possibly work).
Writing code is not exactly "built-in option". It takes longer to get it right and now you need a programmer to maintain it. You see all options, but NO third-party components. I guess your clients like to pay more. You'd save cost by not using third-party, but the clients end up paying more for your custom development services and contraptions.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
CozyRoc (6/29/2012)
It is up to YOU to convince your clients to use third-party components when helpful/needed.Convince? No. Convincing is your job, not mine 🙂 My job is to evaluate all options and present the one I think is best for the task at hand, for the situation at hand, for the shop at large.
You asked me what do I think about the first such result you came up with. This is bizarre. Why would you think I should know everything people find on the internet? I can only stand behind the product my company provides to SSIS users. I wouldn't suggest something, if I'm not sure it works. If you are offering solutions to people on the forum, you'd better test them first. Otherwise what's the point? Everyone who sees this discussion would think the OLEDB driver you suggest works (and it might possibly work).
I said it once, but just to reiterate since you continue to imply that I suggested something specific: I did not suggest anyone use a specific OLE DB driver, period. I asked you what you thought of one. And as for bizarre, I have the same sentiment towards your disposition. It's not like I asked you what you thought about a Snap-On ratcheting box wrench, I asked you about a piece of software that is in direct competition with one of the products you are touting. If you have not demoed your competition's software and cannot compare and contrast it with your own product on a technical level, that makes me wonder. Speaking of that, you never answered my question about how your ODBC Destination Component bulk loads into MySQL.
Writing code is not exactly "built-in option".
The C# Script Task is a built-in option. I know what you're getting at, but I do not agree with your assertion, at all.
It takes longer to get it right and now you need a programmer to maintain it.
Are you sure it takes longer? Are you factoring in the time it takes for the request to make it through "architectural review"? What about the time it takes for the department head to approve the expenditure? What about the time it takes to generate a purchase order? Also, are you factoring in the time it takes to install it on all workstations and servers where it will be required, and to maintain those installations as updates are released and new servers are provisioned? Within an enterprise all the tasks I mentioned are likely carried out by people other than those on the development team, so you can also add a Project Manager's time to the equation as they'll be needed to coordinate all the moving parts. If that coordination task is left to the developer then guess what that developer is doing besides reviewing requirements, designing, writing, unit testing, performance testing and stress testing their software? Also factor in people's time that is wasted going over the status of these tasks on conference calls or in scrum meetings while this coordination takes place, all the while not talking about the project at hand.
Now, sometimes all of that mess might be worth. In a smaller shop where developers have end-to-end control I could see it. At the end of the day however I have to consider all of that when making a decision, not just how much easier it will make developing code, which by the way is actually a very small part of the process of producing software.
All of this is not to mention the fact that bugs in third party components sometimes make it into shipped software, and working around that is waiting for the vendor to get back to you. With built-in components there are bugs as well, but given the number of people using them the likelihood of running into one that has not been addressed in a 7 year old, 4 year old, or even 5 month old product like SQL Server are much smaller. I'll usually take my chances with built-in components.
Your comment about contraption was a bit presumptuous by the way. Are you saying you can build a better mousetrap? 😉
You see all options, but NO third-party components. I guess your clients like to pay more. You'd save cost by not using third-party, but the clients end up paying more for your custom development services and contraptions.
I do what I think is right for my employer and my clients. It will most certainly depend on the situation but in the times (~10) when I have felt a third-party component might help my situation I weighed all the options and found a suitable way to do things without venturing outside the included tools within SSIS.
Like I said, others may be having a different experience out there.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Convince? No. Convincing is your job, not mine My job is to evaluate all options and present the one I think is best for the task at hand, for the situation at hand, for the shop at large.
You are not capable at evaluating the options, my friend. Someone who hasn't found the time to check what COZYROC provides for the past 5 years is bad professional in my opinion.
I said it once, but just to reiterate since you continue to imply that I suggested something specific: I did not suggest anyone use a specific OLE DB driver, period. I asked you what you thought of one. And as for bizarre, I have the same sentiment towards your disposition. It's not like I asked you what you thought about a Snap-On ratcheting box wrench, I asked you about a piece of software that is in direct competition with one of the products you are touting. If you have not demoed your competition's software and cannot compare and contrast it with your own product on a technical level, that makes me wonder. Speaking of that, you never answered my question about how your ODBC Destination Component bulk loads into MySQL.
Why do you ask ME what I thought about any piece of technology? Why do you expect me to know anything and everything? I thought you are the person evaluating "all options". Your words, not mine. COZYROC ODBC Destination component uses the ODBC bulk-load API.
The C# Script Task is a built-in option. I know what you're getting at, but I do not agree with your assertion, at all.
Does it require programming? Does it require you to learn programming libraries? Point made.
Are you sure it takes longer?
Oh, yes. It takes longer . Writing code and maintaining someone else's code is harder compared to point-and-click UI.
Are you factoring in the time it takes for the request to make it through "architectural review"? What about the time it takes for the department head to approve the expenditure? What about the time it takes to generate a purchase order? Also, are you factoring in the time it takes to install it on all workstations and servers where it will be required, and to maintain those installations as updates are released and new servers are provisioned?
I'm factoring everything. You my friend live in the 70-ties. The waterfall model is long forgotten for good. And you make it sound harder than it is. As I've said the proof is in the pudding. COZYROC wouldn't exist if your babble made ANY sense. I feel sorry for your clients, though.. if they get such "professional advise".
Now, sometimes all of that mess might be worth. In a smaller shop where developers have end-to-end control I could see it.
It is not a mess. COZYROC is successful with small, medium and large organizations. Now tell me how is that possible in your dream land ?
All of this is not to mention the fact that bugs in third party components sometimes make it into shipped software, and working around that is waiting for the vendor to get back to you. With built-in components there are bugs as well, but given the number of people using them the likelihood of running into one that has not been addressed in a 7 year old, 4 year old, or even 5 month old product like SQL Server are much smaller. I'll usually take my chances with built-in components.
Again wrong. It shows how far from reality you are. Microsoft hasn't addressed bugs in SQL 2005 for the past 6-7 years. These are acknowledged bugs. Most of the bugs in COZYROC are resolved in a couple of hours and people have the updated library in their hands. Ask around and you will see these are not just words.
Your comment about contraption was a bit presumptuous by the way. Are you saying you can build a better mousetrap?
Oh, yes. Our "contraptions" will ALWAYS be better compared to your "contraptions". Not because I think you might not be a genius programmer, but for the same reasons like the ones you have stated above for Microsoft. We have many customers using our product and we have to make sure the software is well documented, have samples, the bugs are handled as quickly as possible, etc. And btw we have to deliver experience similar or better to what people already get with Microsoft to be accepted and successful.
I hope you will take my comments with a bit of humor. I don't want to hurt anyone's feeling and we are all in these forums to learn something. In fact many of our best "contraptions" came as a direct result of reading people's issues right here in the forums. Probably my style is not the best, but I don't have bad feelings.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply