Hi!I have a problem that stops me from using Integration Services as THE ETL tool.My goal is to load a data warehouse type of a database. The database is MaxDB (former SAP DB), but this is not the point. Let's take ANY ODBC compliant DB and assume all I have is ODBC driver - no OLE DB driver.I figured out how to read from ODBC source (using Data Reader and ADO.NET provider for ODBC).Now my question is how do I output/write my data into ODBC source? When I try to use OLE DB Destination it does not give me an option to use .Net Provide for ODBC. I tried other "destinations" with no luck.I usethis version of SQL Server 2005:(Microsoft SQL Server 2005 - 9.00.1187.07 (Intel X86) May 24 2005 18:22:46 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.1 (Build 2600: Service Pack 2) Please, help!Dima

I haven't had enough coffee yet this morning to be sure that you're not overlooking any options among the data flow destinations that ship with SSIS, but in any case, you could always create your own destination fairly easily by using the Script component with the ODBC .NET Framework Data Provider.Please see the BOL topic, "Creating a Destination with the Script Component," to see how little code you would need to write. The other topics in the "Extending the Data Flow with the Script Component" section will help you to understand the other features of the Script component.-Doug

I do too think I'm missing an elephant...Can you guys please try to do it on your side?It just kills me that this simple thing is not there for me.Cretaing custom destination is not even an option. I just don't have time for this kind of work - which is fun of course.

Integration Services does not come with built-in support for ODBC destinations. Perhaps in the near future a custom third-party component will be available for this purpose. In the meantime I encourage you to review the Script component as an interim option. To save you the time of looking it up in BOL, I'm going to paste below the teensy amount of code required to create a simple ADO.NET destination component by using the Script component. With a couple modifications to use an ODBC Connection Manager instead, and to adjust the Command object for your destination and its columns, you're good to go.---------------Sample ADO.NET Destination using the Script component, from the BOL topic "Creating a Destination with the Script Component"---------------Imports System.Data.SqlClient...Public Class ScriptMain Inherits UserComponent Dim connMgr As IDTSConnectionManager90 Dim sqlConn As SqlConnection Dim sqlCmd As SqlCommand Dim sqlParam As SqlParameter Public Overrides Sub AcquireConnections(ByVal Transaction As Object) connMgr = Me.Connections.MyADONETConnectionManager sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection) End Sub Public Overrides Sub PreExecute() sqlCmd = New SqlCommand("INSERT INTO Person.Address2(AddressID, City) " & _ "VALUES(@addressid, @city)", sqlConn) sqlParam = New SqlParameter("@addressid", SqlDbType.Int) sqlCmd.Parameters.Add(sqlParam) sqlParam = New SqlParameter("@city", SqlDbType.NVarChar, 30) sqlCmd.Parameters.Add(sqlParam) End Sub Public Overrides Sub MyAddressInput_ProcessInputRow(ByVal Row As MyAddressInputBuffer) With sqlCmd .Parameters("@addressid").Value = Row.AddressID .Parameters("@city").Value = Row.City .ExecuteNonQuery() End With End Sub Public Overrides Sub ReleaseConnections() connMgr.ReleaseConnection(sqlConn) End Sub End Class

Thanks Doug.This is a big disappointment. Especially, that SQL Server's 2000 DTS support any destination.There is OLE DB Driver for ODBC - why it's not in the list?? Has this been removed on purpose?

The native OLE DB Provider for ODBC, MSDASQL, is a deprecated component of MDAC. As such it would be unwise to use it in new development, even if it were available.I do empathize with your situation where an OLE DB provider is, apparently, not available for the data destination that you wish to use. I cannot personally speak to the design decisions that led to the fact that ODBC is not available as a destination in Integration Services. In my previous responses, I've simply been trying to point out that you can very easily make up for its absence -- at least on an ad hoc basis for a particular package -- with some simple custom C# or VB.NET code.Best regards,-Doug

Doug,I appreciate your help. The above comments directed to MS design team.I was at this year TechEd and they painted SSIS as the greatest thing ever and independent product from SQL Server. I even spoke to one of the two main guys on the team and specifically asked about using MaxDB as a destination and he said: "No problem - just use ODBC". I broght it home to my linux/java coworkers as greatest thing ever.I spent several full days trying to figure it out. The documentation says it's there and I was pulling my hair looking for the thing. Imagine my disappointement when I found out that this is not available. And no the custom work will not cut it.I like Microsoft - but this is a mistake - this shows MS in this evil light. Basically, what they say is - use our great SSIS product (and it is very good indeed) but you may only use it to put in OUR SQL Server (which is also a very good product).In my eyes it pushes the product to totally different (lower) level - if DTS's where as one of the 3-4 ETL tools - the SSIS is going to be just an add-on to SQL server, which is a pity.I'm just surprised that none is screaming about it yet....Dima.

Dima,Well, I see more clearly now where you're coming from. I regret that you received inaccurate information from one of our TechEd presenters.I will forward your comments just to make sure that they're noticed.You're reporting that "The documentation says it's there ..." As a member of the documentation team, I want to fix this error if I can find it. Do you remember which topic or topics you found this information in?To split hairs, let me just reiterate that one can use ODBC as a destination with minimal custom coding. But you're right, this support is not built into the product in a prepackaged ODBC destination.You're being unfairly harsh in your suspicion that we only want customers to save data into SQL Server. In addition to SQL Server, we provide Excel and flat file destinations, along with the general-purpose OLE DB destination. Microsoft itself provides OLE DB Providers (and managed providers) for Oracle and DB2, and third-party companies like DataDirect extend OLE DB support to include Sybase and Informix. Other companies make providers for MySql, SqlBase, Pervasive, etc....all usable with the Integration Services OLE DB destination. So it's inaccurate to speculate that Integration Services is trying to force everyone to use SQL Server.Please let us know if we can help your team with guidance on developing ODBC destination components. Best regards,-Doug

Doug,I'm sorry you explanation about "old" version OLE DB does not cut it. Why do I have a choice to pick ".Net Framework Data Provider for Odbc" as a source and it magically disappears on destination page (using Export/Import Wizard)? How is that different? About help:ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/e8c77aa7-6772-485e-918e-cef9eeb18c58.htmtells me: "An ODBC connection manager enables a package to connect to a variety of database management systems using the Open Database Connectivity specification (ODBC)."There is no IF's and BUT's. So, one can consider its misleading. There are more places like this.I may have been too harsh in my comments. It's just I have no choice in selection of my target DB but I can select any ETL tool I want and I need it now.I love SSIS and the way it's architected. However, depending on Data Direct (acquired by PROGRESS) or some other third-party is not an option for me, even though could be perfect scenario for someone else.The fact that MS provides support for Oracle and DB2 is twofold. One (evil linux guy) can say that MS does not want to loose this customer base, or even gain more market with this move. Supporting smaller DB's is not in MS interest.However, if SSIS is positioned as generic ETL tool (separated from SQL Server), it MUST support ANY ODBC source out of the box. And I say must because any other tool on the market that manifests itself as ETL component of Enterprise BI solution (complete circle remember? I even have the t-shirt) does support it. Especially, while older version of the tool supports any destination.I don't mean to flame though. You explanation was good enough for my purposes and I'm in the market for ETL tool again!Dima.

For the sake of Dima and any time travelers who may one day return to this thread, I would like to demonstrate how the Script component can easily be used to create an ad hoc ODBC Destination, in far fewer lines of code than the lines of discussion in this forum thread. This sample will become the subject of a new BOL topic, "Creating an ODBC Destination with the Script Component." The sample has been stripped to the minimum for demonstration purposes and does not contain error-handling. Note the following features that distinguish this code sample from the ADO.NET Destination sample demonstrated in the existing BOL topic, "Creating a Destination with the Script Component." You cannot call the AcquireConnection method of the ODBC connection manager from managed code, because it returns a native object. Therefore the code uses the connection manager's connection string to connect to the data source by using the managed Odbc .NET Framework Data Provider. The OdbcCommand expects positional parameters indicated by using ? in the text of the command instead of the named parameters expected by the SqlCommand. Imports System.Data.Odbc...Public Class ScriptMain Inherits UserComponent Dim odbcConn As OdbcConnection Dim odbcCmd As OdbcCommand Dim odbcParam As OdbcParameter Public Overrides Sub AcquireConnections(ByVal Transaction As Object) Dim connectionString As String connectionString = Me.Connections.MyODBCConnectionManager.ConnectionString odbcConn = New OdbcConnection(connectionString) odbcConn.Open() End Sub Public Overrides Sub PreExecute() odbcCmd = New OdbcCommand("INSERT INTO Person.Address2(AddressID, City) " & _ "VALUES(?, ?)", odbcConn) odbcParam = New OdbcParameter("@addressid", OdbcType.Int) odbcCmd.Parameters.Add(odbcParam) odbcParam = New OdbcParameter("@city", OdbcType.NVarChar, 30) odbcCmd.Parameters.Add(odbcParam) End Sub Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) With odbcCmd .Parameters("@addressid").Value = Row.AddressID .Parameters("@city").Value = Row.City .ExecuteNonQuery() End With End Sub Public Overrides Sub ReleaseConnections() odbcConn.Close() End Sub End Class-Doug

Thank you Doug. I may use it actually in next few weeks...:)

Dima S wrote: I'm sorry you explanation about "old" version OLE DB does not cut it. Why do I have a choice to pick ".Net Framework Data Provider for Odbc" as a source and it magically disappears on destination page (using Export/Import Wizard)? How is that different? The difference is that SSIS ships with ADO.NET Source Adapter, but there is no ADO.NET Destination Adapter (yet).Well, there is an ADO.NET Destination, but it does something very different - it allows one to use output of SSIS Package as ADO.NET Source in another application (e.g. Reporting Services or third party application).Due to time constraints, an adapter that would allow to write to ADO.NET destination does not ship with SQL 2005 release. This is definetely something we plan to fix for next release (usual disclaimers about unreleased product apply).Thanks,Michael.

This seems to work fine when the ODBC is to SQL Server. When I try to connect to Oracle, the password keeps disappearing! Every time I open the Connection Manager back up, it is gone. Any ideas? Scott Barrett

I tested all of these today in an attempt to load an Oracle database. Microsoft, you dropped the ball! None of these methods is worth the attempt as they are way too slow. The net of it, if you need to load a variety of sources, really anything non-Microsoft, get something else or use the old DTS.....I am filing my Premier Support issue on Monday.....Scott Barrett

Yeah, I looked at it and tried to implement it - it's slow and it's difficult to use. If I have to do it just once - it's OK. But if my destination is PostgreSQL or other - it's a pain to do it for each table.Although, I thought you can use Oracle OLE DB Provider as desination with no problems....If this is also a problem - this sucks.I just don't see how hard it was to add? I can write a tool in 2 hours that will allow me to write to any ODBC destination! Of course no functionality like in SSIS...How am I supposed to migrate my SQL 2000 DTS packages that push data into ODBC sources? It actually keeps us from upgrading to SQL 2005 database, since not all packages can be migrated to SSIS (yes I know I can run old DTS's under SSIS, but I don't want to do it).

Scott Barrett wrote:This seems to work fine when the ODBC is to SQL Server. When I try to connect to Oracle, the password keeps disappearing! Every time I open the Connection Manager back up, it is gone.Any ideas?Scott BarrettScoot,I think this is something to do with Microsoft not wanting to be responsible for you storing passwords in the package. I think this can be gotten around using password protection on the package however my workaround is simply to store the password in a configuration.-Jamie

There's no evil intent or malice intended here. Our team had limited resources and time to deliver SSIS and had to make priority decisions on where to best use those resources. Our primary goal was to create a platform and then build an application on top of that platform to meet customer needs. As you well know, to our chagrine, we were unable to provide every component that we would have liked to provide to our customers. We provide excellent performance options for SQL Server, that's true. We chose to focus on SQL Server mainly because our primary user base uses SQL Server. Also, we ship as part of the SQL Server box. We need to dance with the one that brought us. The bright side is that we're listening and hear what you're saying. An ODBC adapter was actually on the list of features early on and didn't make it in due to restraints. Hearing from customers like you that it's a priority is good and gives us guidance on what to focus on next. Also, I know that there are a number of individuals and companies that are developing adapters and other components that may already be writing an ODBC component. I believe, it's only a matter of short time at this point.Thanks,K

Jamie...Configuration does not work with that for some reason. I ended up appending it in the Script component. I am reporting it as a bug tomorrow. Got lots of bugs to report tomorrow....BTW, hands down, yours is the best blog on the 'Net! Keep up the good work. I am trying to get a blog at SQLJunkies to write about my experiences with SSIS and Oracle. Microsoft sent me an alpha version of a new Oracle Bulk Load Destination from a third-party vendor. I'll let you know how it goes tomorrow...Scott in sunny, warm Florida....

Kirk,I completely understand the reasoning behind all of it, and if I could convince people to move off of Oracle I would do so in a second! I think if there was a little more public knowledge that there are 3rd party vendors that will have destinations out soon, we would all feel a little better. I just received an alpha version tonight from Microsoft and it looks promising. I will test it against Informatica and SQL*Loader and we'll see how it does. I believe these types of products/vendors will be the key in SSIS being a major player in the ETL/EII market place. If the timing were a little better and these destinations publically available, we would be there. Without this, SSIS will stay a "SQL Server ETL tool"And for the love of macaroni......we need an ODBC destination! This needs to be a hotfix or SP1 release at minimum.Scott in sunny, warm Florida....road 60 miles on the bike today....fabulous!

KirkHaselden wrote:There's no evil intent or malice intended here....You just gotta see eyes of some of the people when I say that "...SSIS does not write to ODBC sources yet...."I'm sure there was not evil intent, but it surely does "look" evil to a select user group. And isn't this a perception (evil one) that MS has been fighting for so long now with all these blogs and forums like this?Anyway, all we've got to do is to wine and hope you guys come up with the "hot fix" soon.

Dima S,Relax man. Here is your solution, exactly as you wanted it to be.It hides in this forum but in another thread:http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=20119&SiteID=1Enjoy.I have the same problem and I used that solution. Beautiful.

ailuz,That forum is for creating an ODBC data source. Dima S is requesting functionality be added to SSIS to create an ODBC data destination (this is currently not an option).I would also like to see an ODBC data destination, I need it to write out data in my SQL tables to a legacy system.Jarret

Jarret, You are right. I did not realize that. Three is indeed a problem transffering data to ODBC dest.

Well this has been a very enlightening thread as I've come across exactly the same problem in the last day. I'd already found the scripting component workaround in Books On Line but it's too slow. Are there any new developments (custom components etc) that will support writing data to an ODBC destination? Thanks, Dave.

A collegue of mine came up with an ingenious solution to this problem... its a dodgy hack but a simple and time saving one. Link to your ODBC tables in a Microsoft Access file Create a query in Access thatselects * from your linked table. Create a DataSource using the Microsoft Jet 4.0 OLE Data Provider that links to your Access file You can now write to the tables through access... I know it is a horrible hack but hey... it works:) The nice alternative is to find an OLEDB data provider for the database you are trying to connect to... you would be suprised how many minorty databases actually have one of these available Hope this helps

And you have here another disappointed user. When will we have a way to write towards odbc ? Regards.

RoMiller, I tried your method on my local machine. It only workson the table, not view. Sometimes it even failed on the table. Those error messages gave me no clue. The other problem I had is our production machine is 64-bit box and we don't plan to install microsoft office on that matchine. So even it works on my local machine, I will still haveproblem to move it to the production. Does anyone have a solution yet? I desperately need one. Thanks in advance.

Scott, i had the same problem, with dissapearing oracle passwords. i got around it by putting the connection string in an expression. this is really good especially when you are changing environments (one server to another) don't have to worry about encryption keys. but this will still not show the password on the connection manager but it will be there in the background. (it gets populated in runtime) AJ

Hi, I found thistopic quite helpful, unique and interesting. I have slightly different problem. Im trying to use OLE DB Command from SSIS package to insert data into Informix Database (Driver IBM Informix ODBC Driver 3.82.0000 2.81.TC3). The insertion should be done per record. The DB connection is fine, I could insert data into the table using via Server Explorer (ODBC) but cant do the same insert through OLE DB Command using Informix OLE DB provider. SSIS Error message: Error: 0xC0202009 at Data Flow Task, OLE DB Command [3109]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. Error: 0xC0202009 at Data Flow Task, OLE DB Command [3109]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. Error: 0xC0202009 at Data Flow Task, OLE DB Command [3109]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Command" (3109) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. Query: insert into table_name(field1, field2, field3)values(?, ?, ?) Does anybody have ideas? Does SSIS OLE DB Command support Informix OLE DB provider? Thanks, Yaroslav

When I have used an OLE DB connection I would use the following query in my SSIS packages: Query: INSERT INTO table_name(field1, field2, field3) VALUES(@fields1, @field2, @field3) I have found that the "(?,?,?)" syntax only works with ODBC connections. I will look for the MSDN article where I found this informaiton. I hope this helps...

At this point of time I'll beleive thatinsert statement could be generated in the scriptcomponent that updatesOLE DB Command before an execution. The good thing is during design you can use (?,?,?) to define column mapping.

Very disappointing responses from Microsoft. They did not had time to do it!My way to do it was to go back to SQL Server 2000 and use the old and easy DTS. Then to migrate run that DTS from SSIS.The good news is we are on http://forums.microsoft.com/msdn and Microsoft should be aware of our difficulties and for sure they are ready to help us as soon as they have the time (and money) to do it.Hope this will help!

Joel Cabot wrote: Very disappointing responses from Microsoft. They did not had time to do it!My way to do it was to go back to SQL Server 2000 and use the old and easy DTS. Then to migrate run that DTS from SSIS.The good news is we are on http://forums.microsoft.com/msdn and Microsoft should be aware of our difficulties and for sure they are ready to help us as soon as they have the time (and money) to do it.Hope this will help!But to back them up, it is their product and they also produce SQL Server. So to be fair, I'm not sure it's appropriate that they rushed to build in a solution that would potentially eliminate the use of SQL Server as a database platform for the use in SSIS. After all, SSIS comes with SQL Server.Perhaps a non-biased vendor like Informatica would be a better solution for you.

Can you summarize where SSIS is in terms of ODBC destination functionality? I have IBM Client Access ODBC that I use in my DTS. SQL Server ---> AS400. Do you have working examples for creating that kind of ODBC destination? The OLE for DB2 has problems for certain fields. Something about the data type. Any links describing on these?

DBA2 wrote: Can you summarize where SSIS is in terms of ODBC destination functionality? I have IBM Client Access ODBC that I use in my DTS. SQL Server ---> AS400. Do you have working examples for creating that kind of ODBC destination? The OLE for DB2 has problems for certain fields. Something about the data type. Any links describing on these? Lots of good material here: http://ssis.wik.is/ -Jmie

Please bring ODBC destinations into SSIS. I have come to rely on it in DTS. As a datawarehouse developer using SQL Server, I am usually bringing data to SQL Server. However, as the company expert for moving data around I definitely need to transfer data between two ODBC data sources. The data type conversions between OLEDB do not seem as good as they are in ODBC. I've been trying for too long to transfer data with ole db only to get CAST conversion errors. Instead of thinking that an ODBC script is EASY (it is hard to keep track of over 100 ? paramters), I think that going back to DTS is easy and looking for another ETL program may be necessary.

Does Microsoft have any working examples in using an ODBC destination specifically IBM Client Access ODBC ? From DB2 to SQL Server is no problem. Uploading data from tables in SQL Server to IBM DB2 tables is the issue.

I am utterly shocked odbc destination does not exists in integration services and somebody made a decision to drop it. i am migrating old app from sql server to mysql and i am forced to write data from sql server into flat file and then load the flat file manually using mysql load commands for every table. how microsoft product managent let this one slip is beyond me. i think i am just going to use perl or oracle datawarehouse builder...

Douglas, have you ever designed datawarehouse etl for a medium to large db? i don;t think so you suggestions for script component are interesting but going to be dog slow and not acceptable from perf standpoint.

Hi Stanley, Stanley35 wrote: Douglas, have you ever designed datawarehouse etl for a medium to large db? i don;t think so you suggestions for script component are interesting but going to be dog slow and not acceptable from perf standpoint. Why do you say that? What experiences you have had using the script component? I have always found perf to be perfectly fine but if you've experienced different then I'd like to know. -Jamie

Greetings! Just to keep this post alive As previous posters noted - MS is free to do what they wish with the product. The disappointing part is losing functionality - DTS worked just fine with _any_ data destination. So, now we have to maintain two skills in the house - one for DTS 2000 and one for SSIS. Of course one can use flat files (or even Access as was suggested) in the middle... Of course one can use script component.... Of course one can use old DTS for some packages and SSIS for other... The only question I have is WHY? And don't tell me you didn't have time and I don't see how is this in the interest of MS as a company (not some middle manager that may have decided to do this). To add more oil to the fire: I'm reading What's New in SQL Server 2008 CTP 2 - I don't see this fixed there. Let's all hope MS simply didn't have time to includethis inBOL Dima S.

Hi Their I am pretty concerned with this development, I work as a consultant and I am very disappointed to find such a basic feature missing from a product that claims to be BI solution. One of the main tenants of BI is the integration of/synchronisation of disparate systems. I feel that MS have provided a very flexible tool, but having a feature like this missing is extremely poor. To say that some third party will pick this up is all well and good however we are now nearly two year's on and I cannot find one. Also if you are correct Dima, it may not even be present in 2008!!!!. I am currently trying to get the work around working with the component script but am having serious problems. Currently I feel like a great product has been crippled. If there has been any movement on this could some one reply? Cheers Ken C.

Hi, Rack up one more under disappointed customer. Well, not a customer actually, since it can't do this I won't be buying MS SQL Server. I was working thru the SSIS tutorials, going, "Wow, so cool. Will make my life much easier." But then, "No, won't actually. Can't actually connect to those other databases." Trust Microsoft to get half right. > Currently I feel like a great product has been crippledWith you on that. nancy

Nancy,What databases are you connecting to that don't provide OLE DB drivers such that you have to use ODBC?SQL Server, Oracle, DB2, and others all have OLE DB providers which negate the need to use ODBC.

I heard SQL Server 2008 is around the corner. Does MS have any plans in getting some of those features we had in 2000 back in 2008? It'll make our integration with a legacy a little easier.

The next version will have a new destination (ADO .NET Destination) adapter. It will be possible to use it with ODBC providers (through ADO .NET bridge). It will most probably be available for initial testing in CTP5 version. Thanks, Bob

Add me to the list of dissapointed customers!! Next Version being 2008 I suppose? not really good enough I'd say. I'm still trying to migrate all my companies apps onto SQL server 2005, some of which will now never make it because there is no ODBC destination in SSIS. Like alot of people I have no experience of scripting components so I have no option to go back to DTS, so much for progress eh. To have no ODBC destination in SSIS is criminal, especially as it was DTS.Aren't newer versionsof a product supposed to be an enhancement of the previous or have I missed something? I cannot help but join in the cynical voices that have formed the opinion that Microsoft is not reallybothered about ODBC, the problem is here that they don'tappear tobothered to ask or listen tothe most important people involved with it's products, their customers.

bobbins wrote: Add me to the list of dissapointed customers!! Next Version being 2008 I suppose? not really good enough I'd say. I'm still trying to migrate all my companies apps onto SQL server 2005, some of which will now never make it because there is no ODBC destination in SSIS. Like alot of people I have no experience of scripting components so I have no option to go back to DTS, so much for progress eh. To have no ODBC destination in SSIS is criminal, especially as it was DTS.Aren't newer versionsof a product supposed to be an enhancement of the previous or have I missed something? I cannot help but join in the cynical voices that have formed the opinion that Microsoft is not reallybothered about ODBC, the problem is here that they don'tappear tobothered to ask or listen tothe most important people involved with it's products, their customers. SSIS isn't an enhancement to the DTS product -- to compare is not fair. SSIS is a rewrite.Just keep in mind that SSIS is provided with SQL Server, and as such Microsoft didn't feel the need to rush to market with a solution that worked with other database providers. They do allow the use of OLE DB destinations, and most databases have OLE DB drivers available, so you should try to use that first and foremost.

Thanks for the reply but i'll be totally honest with you, I disagree. SSIS may well be a re-write but it is still the ETL tool supplied with 2005, as was DTS in 2000so I don't think itunfair to compare the two, people will only naturally compare old with new whether it is a re-write or not. Theredoesn't seem to be an OLE DB drivers for Ingres on Unix (yes, some of us still us this stuff!) and even if there was I wouldn't know how to put it into SSIS anyway. I can see how Microsoft can justify saying that they didn't need to rush to market with a solution that worked with other database providers, so why did they put it in DTS?I think (and Iam not alone judging by thesizeof this thread)this is a mistake and Microsoft should just act like a grown up an come out a admit it instead of trying to put some spinon it and make excuses for it not being there. How do think we all feel at work whenwe sat there saying "oh, there's no ODBC destination in SSIS soI guesswe'll have to use DTS instead", people just cantbelieve it. Thanks

bobbins wrote: Thanks for the reply but i'll be totally honest with you, I disagree. SSIS may well be a re-write but it is still the ETL tool supplied with 2005, as was DTS in 2000so I don't think itunfair to compare the two, people will only naturally compare old with new whether it is a re-write or not. Theredoesn't seem to be an OLE DB drivers for Ingres on Unix (yes, some of us still us this stuff!) and even if there was I wouldn't know how to put it into SSIS anyway. I can see how Microsoft can justify saying that they didn't need to rush to market with a solution that worked with other database providers, so why did they put it in DTS?I think (and Iam not alone judging by thesizeof this thread)this is a mistake and Microsoft should just act like a grown up an come out a admit it instead of trying to put some spinon it and make excuses for it not being there. How do think we all feel at work whenwe sat there saying "oh, there's no ODBC destination in SSIS soI guesswe'll have to use DTS instead", people just cantbelieve it. Thanks I largely agree. To be fair I think by saying they are going to rectify it they have admitted that they have made a mistake . Also, its wrong to say there is no ODBC destination in SSIS. There is code further up this thread that shows how it can be done from a script component. Note I am NOT saying this is an adequate substitute for a fully fledged component that supports ODBC - far from it. -Jamie

Has anyone tried the following This is what I got from BOL Executes the specified pass-through query on the given linked server, which is an OLE DB data source. The OPENQUERY function can be referenced in the FROM clause of a query as though it is a table name. The OPENQUERY function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one.EXEC sp_addlinkedserver 'OracleSvr', 'Oracle 7.3', 'MSDAORA', 'ORCLDB' GO SELECT * FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles') GO For INSERT, UPDATE or DELETE, I am not sure of the syntax Maybe INSERT OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles') Select name, id from local_tables ??? I am going to have to play around with this. But do you guys have any tips on this. I hate to put this into production.

I have been using SSIS for a while and today I have run into the same problem : the lack of an ODBC destination. Someone questioned the need for ODBC , since there's a lot of OLE DB providers. But I've been asked to develop a package that needs to write datato Mysql 3.51. This means I have a Mysql .net provider and a Mysql ODBC provider but not a Mysql OLE Db provider . Although I would prefer that this customer uses another database than Mysql, it is something that I have to accept. There's a lot of Mysql databases around certainly on web servers.Just like many other posters in this threadI cannot understand why this was left out in SSIS.Another omission that has made me lose time was the fact thatas a developer one could release DTS packages on any server, now thecustomer has to buy an SQL server 2005 license to allow for me to write an ETL package in SSIS. I do think we need ODBC as a data destination.

In my previous post Imistakenly typedMysql 3.51, it is Mysql 5.0. I have found an old OLEDB driver on the Mysql website, but that is from 2001 and is for Mysql 3.x so most likely will not work with mysql 5.0.

I have been working on a similar scriptsolution and have tried to attempt to make it in such a way to be easily reusable across components and easily handle null support. You cansee the source here http://solidcoding.blogspot.com/2007/11/odbc-destination-script-for-ssis.html Thiscode could also be created in a class library which can the be referenced by the script components. You could then avoid having to rewrite this code for each place you would like to use a ODBC destination.

Uh Phil... try using the OLEDB to write a dataset back to DB2 to use as in a join to retrieve the final set into SqlServer. What I find is that OLE DB works great with the IBMDADB.2 wrapper if you only want to read data from DB2. But, you can't write to a DB2 tablespace because someone at this party is trying to shove unicode up a very sensitive orafice (I'm so PC ;-) , and even though the source Sql Server table is 3 varchar columns and the DB2 destination is 3 varchar columns the OLE DB destination adaptor keeps whining that it can't convert between unicode and non-unicode types. Great! Please don't. Just store it as non-unicode like I asked. This has been an ongoing problem with SSIS at our company: that SSIS is SO DAMN UNICODE HAPPY! So either OLE DB hosed the metadata read or our DB2 server suddenly had a fit of Unicode Love (doubt it) but either way you ain't gonna write to it. I even played along and converted the columns to nvarchar before attempting to write, which blew everything out of the water when SSIS tried to throw that unicode up against the DB2 table and it wasn't really unicode (I still see the splatter when I close my eyes...) I just wanted to vent when you asked what is wrong with OLE DB connections. Nothing, unless you want to write outside the Microsoft Sandbox I would be very happy and go away if I could find out how to get the Dest Adaptor to NOT insist on writing unicode when neither the source or the target is unicode. Kind of kills the whole party. I know "Shut up kid, it's gonna be taken care of in version 8 (or will it be 9???). I do love wasting a week working on work arounds in this environment. You know what I think is cool is that the forums all assume that the people with the problems have control of their environment. LIke I can walk into my IS and say IBMDADB2 isn't working for me I'm gonna try this 3rd party connection, please install it this week please. The part where they say "sure, no problem" is a great day dream for me. The reality would be me trying to get out before he tries to take my head with a spare tape reel! Oh by the way this little rant was inspired by the way this thread ended up, even though it started with the original poster having the exact same problem as me. Nobody addressed the original problem and went with him on the work around. Let's review that: He said when he first tried to use the OLE DB it complained that it couldn't convert between unicode and non-unicode so he tried the same response as I did and converted it to unicode and got errors at runtime. That is the whole problem! It is not about not being able to write to DB2 but, that either OLE DB, or the wrapper or SqlServer is insisting that the target is unicode. Fix that gap in the destination logic and lots of problems will go away. So do I have a question or not? Yeah, guess I do... that script to create an ODBC connector is there any kind of hack like that out there that I could maybe run to convince a destination adapter that it really doesn't need unicode on it's output? I can always dream,

Hi Folks, there is a solution to use ODBC destinations: simplycreate a new OLEDB connection in Conection Manager. As provider select "MSDataShape" - THIS IS THE OLEDB-ODBC-DRIVER (MSDASQL)! As Data Source you can type in your ODBC-DSN. Then put a OLE DB destination object to the data flow pane and select the previously created connectionin "OLE DB Connection Manager". That's it! The transfer to MySQL seems to be very slow. I had to decrease the value for DefaultBufferMaxRows (property of the data flow pane) from 10000 to 200 to get a useful progress view (transferes only 200 rows per second, BUT IT WORKS )! Hope this helps... Regards, Jan

Scott Barrett wrote: This seems to work fine when the ODBC is to SQL Server. When I try to connect to Oracle, the password keeps disappearing! Every time I open the Connection Manager back up, it is gone.Any ideas?Scott BarrettI had the same problem when trying to connect to Mysql.I think SSIS 2005 is NOT compatible whith uploading Data into Mysql whithout scripting or loading by a flat file (and outer processing...).For me it will be very difficult to explain why I decided to use SSIS...

l00k wrote: Hi Folks, there is a solution to use ODBC destinations: simplycreate a new OLEDB connection in Conection Manager. As provider select "MSDataShape" - THIS IS THE OLEDB-ODBC-DRIVER (MSDASQL)! As Data Source you can type in your ODBC-DSN. Then put a OLE DB destination object to the data flow pane and select the previously created connectionin "OLE DB Connection Manager". That's it! The transfer to MySQL seems to be very slow. I had to decrease the value for DefaultBufferMaxRows (property of the data flow pane) from 10000 to 200 to get a useful progress view (transferes only 200 rows per second, BUT IT WORKS )! Hope this helps... Regards, JanThis does indeed work for mysql. It does give some warnings and is slower than it should be, though. Thanks to the parent poster for this info... you saved my program. :-)Not an ideal solution, but it will do until MS adds full support for the ODBC standard in SSIS packages.That said, since essentially every DB type supports ODBC, I think it was a wrong decision for MS to work on supporting OLE drivers for specific DBs before supporting the more general ODBC connections. ODBC should have come first. SSIS is a step backwards from DTS if you need ODBC support.

Phil Brammer wrote: SSIS isn't an enhancement to the DTS product -- to compare is not fair. SSIS is a rewrite.And a bad one at that... for anyone that needs to push or pull data from non MS datasources.bleh.

If you guys are still around. What versions of MySQL and the ODBC driver did you use to accomplish this. I have tried it and executing the SSIS task, VS does not complain andsays that it is moving the records into the MySQL (5.0.67) database. When I check there is no data. Could it be the storage engine type? character set? Any ideas would be appreciated.

Can you illustrate that in this thread? It sounds like what I need to do for my same problem. I am using the datareader to connect to a Mainframe system that worked fine in DTS through a ODBC connection but now in SSIS the password will not remain when the package is schedule through a job. I tried the package configuration but that is not practical for security purposes. How and where did you setup the expression for the connection string? Thank you in advance

Our destination DB is SQL Server 2005, but we run everything through ODBC, have never used OLE DB.Have to read up on OLE DB Connection Manager, I guess?

JK2008 said:I have been working on a similar scriptsolution and have tried to attempt to make it in such a way to be easily reusable across components and easily handle null support. You cansee the source here http://solidcoding.blogspot.com/2007/11/odbc-destination-script-for-ssis.html Thiscode could also be created in a class library which can the be referenced by the script components. You could then avoid having to rewrite this code for each place you would like to use a ODBC destination.Checked that blogspot post and copy-pasted the code, thought I should test if it compiled before I added any AddColumn calls. I get an error on the line: cmd.CommandType = CommandType.Text"Name 'CommandType' is not declared"As you may understand, I'm a total noob at SSIS... ;)

WHERE do we need to run this script and what will happen when we run this?? Will i see the .NET Framework Data Provider for Odbc option in the Destination after this??? Please advice.

No - the above is a script to be used inside a Script component that you place in your Data Flow.Place a Script component in your data flow instead of an OLE DB Source (or other source). Now, cut and paste the referenced code into that script component...

I am too bad with this tool Todd. So i guess i am not going to use this option. thanks anyways.

Guys, Your calls have been finally answered. Check CozyRoc ODBC Destination component. It includes support for both regular and bulk-load of data. Cheers!SSIS Tasks Components Scripts | http://www.cozyroc.com/

Guys, Your calls have been finally answered. Check CozyRoc ODBC Destination component. It includes support for both regular and bulk-load of data. Cheers! Or use the ADO.Net Destination in SQL Server 2008 SSIS

Guys, Your calls have been finally answered. Check CozyRoc ODBC Destination component. It includes support for both regular and bulk-load of data. Cheers! Or use the ADO.Net Destination in SQL Server 2008 SSIS Two points Phil: 1. Your solution is only for 2008. 2. ADO.NET Destination doesn't support bulk-load of data, making it much-much slower.SSIS Tasks Components Scripts | http://www.cozyroc.com/

Guys, Your calls have been finally answered. Check CozyRoc ODBC Destination component. It includes support for both regular and bulk-load of data. Cheers! Or use the ADO.Net Destination in SQL Server 2008 SSIS Two points Phil: 1. Your solution is only for 2008. 2. ADO.NET Destination doesn't support bulk-load of data, making it much-much slower. SSIS Tasks Components Scripts | http://www.cozyroc.com/ Instead of advertising on these forums, as we've discussed many a time before, can you instead post why and how your custom components can help the user's issue? I simply posted a stock option for users that don't use third-party components/tasks for any number of reasons (policy, untested, etc...).

Guys, Your calls have been finally answered. Check CozyRoc ODBC Destination component. It includes support for both regular and bulk-load of data. Cheers! Or use the ADO.Net Destination in SQL Server 2008 SSIS Two points Phil: 1. Your solution is only for 2008. 2. ADO.NET Destination doesn't support bulk-load of data, making it much-much slower. SSIS Tasks Components Scripts | http://www.cozyroc.com/ Instead of advertising on these forums, as we've discussed many a time before, can you instead post why and how your custom components can help the user's issue? I simply posted a stock option for users that don't use third-party components/tasks for any number of reasons (policy, untested, etc...). Phil, This post is related exactly to the issue at hand - the post title is "ODBC Destination in SSIS" and the offered solution is CozyRoc ODBC Destination. What could be closer? I don't see what is your problem. Are you against third-party components/tasks?SSIS Tasks Components Scripts | http://www.cozyroc.com/

Phil, This post is related exactly to the issue at hand - the post title is "ODBC Destination in SSIS" and the offered solution is CozyRoc ODBC Destination. What could be closer? I don't see what is your problem. Are you against third-party components/tasks? SSIS Tasks Components Scripts | http://www.cozyroc.com/ I completely support third-party development. SSIS was meant to be extended and added-on to, and I think it is great that you can deliver a wide variety of valuable tasks/components to the SSIS community. The problem comes from bringing up super old posts, advertising a product - especially one that isn't free. It would be much different if you demonstrated to each OP *how* to use your new-fangled ODBC destination component instead of simply directing them to your Website. I understand you want to get people to use your components/tasks (and you want to earn some money), but these forums are not a means for direct-advertising, so-to-speak.

Phil, This post is related exactly to the issue at hand - the post title is "ODBC Destination in SSIS" and the offered solution is CozyRoc ODBC Destination. What could be closer? I don't see what is your problem. Are you against third-party components/tasks? SSIS Tasks Components Scripts | http://www.cozyroc.com/ I completely support third-party development. SSIS was meant to be extended and added-on to, and I think it is great that you can deliver a wide variety of valuable tasks/components to the SSIS community. The problem comes from bringing up super old posts, advertising a product - especially one that isn't free. It would be much different if you demonstrated to each OP *how* to use your new-fangled ODBC destination component instead of simply directing them to your Website. I understand you want to get people to use your components/tasks (and you want to earn some money), but these forums are not a means for direct-advertising, so-to-speak. Phil, I understand. You don't oppose free third-party development ;) Your comment about the super old posts is not correct. The most recent post on this message thread was from June 9, 2009. So even though the thread was started back in 2005, it is still alive and relevant to the community. I know we had similar discussions back in 2007 and I thought we had earned your respect after 2 years of serious SSIS development. Speaking of money, just want to let you know we still haven't recuperated the investments we have made so far. So-to-speak enjoy CozyRoc sponsorship of SSIS market while you can. p.s. Your comment about the untested third-party components is also not correct. This somehow implies the standard Microsoft components are bug-free, which everyone knows is not true. Personally I think you can get better mileage from small third-party providers because they can respond better/faster to clients issues. Think about the response time you get when you have issues with the "tested" standard components. Unless you have special contract, you may have to wait months and years for resolution. Agreed?SSIS Tasks Components Scripts | http://www.cozyroc.com/

Any external component usually goes through extra testing in most shops (on the consumer side), regardless of the testing that the vendor put into developing the product. That is what I meant. I'm by no means trying to indicate that CozyRoc's offerings are untested, I'm merely saying that many shops avoid third-party solutions where possible (due to extra cash outlay, support contracts, production-control restrictions, etc...), and when third-party solutions are required, they perform extra testing on said products. This has nothing to do with my respect of CozyRoc, rather it has everything to do with a one-line post in many, many threads here on this forum where commercial advertising is not allowed. Your "advertising" posts never seem to indicate the $499.95 price tag to use the components in a production environment. That is the source of my frustration. The posts are leading users into thinking they have a solution, but are unaware at the time of reading that there is a price to use said solution. We can take this discussion off-line if you wish, but we shouldn't really be having this conversation here as it is way off-topic. Phil

Any external component usually goes through extra testing in most shops (on the consumer side), regardless of the testing that the vendor put into developing the product. That is what I meant. I'm by no means trying to indicate that CozyRoc's offerings are untested, I'm merely saying that many shops avoid third-party solutions where possible (due to extra cash outlay, support contracts, production-control restrictions, etc...), and when third-party solutions are required, they perform extra testing on said products. This has nothing to do with my respect of CozyRoc, rather it has everything to do with a one-line post in many, many threads here on this forum where commercial advertising is not allowed. Your "advertising" posts never seem to indicate the $499.95 price tag to use the components in a production environment. That is the source of my frustration. The posts are leading users into thinking they have a solution, but are unaware at the time of reading that there is a price to use said solution. We can take this discussion off-line if you wish, but we shouldn't really be having this conversation here as it is way off-topic. Phil Phil, You know perfectly well how the technology works. People search for issues and solutions in the forum. Then they find these old postings and decide there is no available solution. By insisting I should not post in these old postings you are not making people's life easier. It is exactly the opposite. Each and every post I make is on-the-spot. This is not random process. In fact these posts are collected and used as a starting point and reference in the CozyRoc development.SSIS Tasks Components Scripts | http://www.cozyroc.com/

Has anyone tried to use the MSDataShape option above to transfer data to a Sybase database? I am using SQL Server 2005 SP2With the MSDataShape, I can see the Sybase database and I am able to do my column mappings properly but when it gets to the point of writing the data into Sybase I get the following message;[OLE DB Destination [7011]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft Cursor Engine" Hresult: 0x80004005 Description: "Data provider or other service returned an E_FAIL status."Can anyone be of assistance?Thanks

I have recently migrated to SQL Server 2008 and I must say this is no longer an issue. I have used the ADO.NET Destination to solve the problem.With the ADO.NET destination, all you need is a DSN connection which you can refer to. All else is just the same as sending data to an OLEDB Destination.

So now it's 2010 and the 2005 product has run its full course. There's still no ADO.NET destination in the product nor is there an ODBC destination. I guess "definitely something we plan to fix" did not have as much weight as "usual disclaimers about unreleased product".

These are all excuses and have no bearing on our inability to use the product to do real world work. Eliminating all possible connections to destinations other than SQL Server, particularly when they existed in earlier products, is poor business practice. Then telling us we can write our own in a few lines of code is an insult. In the real world we are using these tools to move millions and often 10s of millions of records at a time. To describe the offered solution as "slow" would be misleading at best and a lie at worst. Slow does not begin to describe how terrible the performance really is. We can't take down the data warehouse for 17 hours to load the data.

This stops us from upgrading our SQL server from 2K to 2K5! We're having a lot of problems working with Informix in SSIS... and going back to 2K, everything is just fine. and I'm quite sure I won't spend another thousands of bucks to have 2K8 , 2K10 ... unless it really has all those I want.

This stops us from upgrading our SQL server from 2K to 2K5! We're having a lot of problems working with Informix in SSIS... and going back to 2K, everything is just fine. and I'm quite sure I won't spend another thousands of bucks to have 2K8 , 2K10 ... unless it really has all those I want. Hunk, SQL Server 2008 includes ADO.NET Destination component, which allows inserting into ODBC database. For SQL Server 2005 you have two options: - implement script component. - purchase third-party component.SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Hi Folks, there is a solution to use ODBC destinations: simply create a new OLEDB connection in Conection Manager. As provider select "MSDataShape" - THIS IS THE OLEDB-ODBC-DRIVER (MSDASQL)! As Data Source you can type in your ODBC-DSN. Then put a OLE DB destination object to the data flow pane and select the previously created connection in "OLE DB Connection Manager". That's it! The transfer to MySQL seems to be very slow. I had to decrease the value for DefaultBufferMaxRows (property of the data flow pane) from 10000 to 200 to get a useful progress view (transferes only 200 rows per second, BUT IT WORKS )! Hope this helps... Regards, Jan This did not work for me...I get exception: TITLE: Connection Manager ------------------------------ Test connection failed because of an error in initializing provider. The .Net Framework Data Provider for OLEDB (System.Data.OleDb) does not support the Microsoft OLE DB Provider for ODBC Drivers (MSDASQL). Use the .Net Framework Data Provider for ODBC (System.Data.Odbc). ------------------------------ BUTTONS: OK ------------------------------ Make everything as simple as possible, but not simpler. -Albert Einstein