ODBC Destination in SSIS

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 use this 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.htm

tells 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.|||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).sql