System DSN with an "OLE DB Connection"

Question

System DSN with an "OLE DB Connection"

Orlando Colamatteo

SSC Guru

Points: 182279
More actions
April 7, 2011 at 1:53 pm

#248509

Is it possible to have an "OLE DB Connection" use a System or User DSN to get server information? If so, can you please provide some steps on how to do so?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 1

Well any information you can get from a SQL query you can get through an OLE DB connection in SSIS assuming you have an account with access to run the query you need. At that point you could use the information as part of a dataflow or it store in a variable depending on what you are trying to do with it.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 2

Thanks for the response. That's not exactly what I was asking. I know how to get data out of a database using an OLE DB source into a variable or whatever. I am asking about the Connection Object itself which is upstream from the Data Flow Source or Data Flow Destination.

I have since learned that SSIS 2005 does not support the "Microsoft OLE DB Provider for ODBC" (MSDASQL) interface thus cannot push to an ODBC destination defined as a System DSN...bummer...huge miss Microsoft.

I have been looking into the CozyRoc ODBC Destination but am losing hair over it at the moment...does anyone know of alternatives for SSIS 2005?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 3

Ah okay that makes sense. You can use a Script Component as a destination in the dataflow to write the information you need to an ODBC destination. Basically you have to override the code on the script component with something like,

Public Class ScriptMain

Inherits UserComponent

Dim mySQLConn As OdbcConnection

Dim sqlCmd As OdbcCommand

Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

mySQLConn = New OdbcConnection("<your connection string here>")

mySQLConn.Open()

End Sub

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

sqlCmd = New OdbcCommand("<your insert statement here>", mySQLConn)

sqlCmd.ExecuteNonQuery()

End Sub

Public Overrides Sub ReleaseConnections()

mySQLConn.Close()

End Sub

End Class

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

Do you have any experience using the technique? Can you speak to how well it scales?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 5

Yes I've used it before when i needed to push data to an odbc connection, it's not quite as fast or convenient as an OLE DB destination but it works. When i've used it it's scaled fairly linearly with number of rows since it's pretty much just a bunch of single insert statements, that could vary depending on your server setup and what destination you're pushing to.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

That's good info, thanks for your responses.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato