Sometimes, I just wonder why things that are useful in previous versions of things get removed. In SQL 2000, in DTS, there was an ODBC destination, you could write (insert, update, etc) to a foreign (non MSFT SQL) system easily. DB2 – no prob, MySQL – heck yea. For whatever reason, in SQL 2005 Integration Services (SSIS), that ability was removed (in SQL 2008 SSIS there is an ADO.NET Destination that can update ODBC sources, so they brought some functionality back).
I need to write to a MySQL database pulling data from a SQL Database, using SSIS 2005. What are the options? Well, the best I could come up with was a Script Component Destination in my DataFlow, this is how I did it:
1) Create a new SSIS Package, throw a DataFlow on the Control Flow, Add your connections (let’s say SourceDB_SQL – your source data, and then DestDB_MySQL – your MySQL destination, it needs to be a ADO.NET Connection, you need to install the MySQL connection – I installed this http://dev.mysql.com/downloads/connector/odbc/5.1.html)
2) In your DataFlow, create your OLEDB Source and get your query returning data, throw a Script Component on the Data Flow and make it a Destination.
3) Open the Script Component, set your input columns up, and then your Connection Manager
4) Open the actual script, and you just have to add a few lines of code:
' Microsoft SQL Server Integration Services user script component ' This is your new script component in Microsoft Visual Basic .NET ' ScriptMain is the entrypoint class for script components Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Imports System.Data.Odbc Public Class ScriptMain Inherits UserComponent Dim mySQLConn As OdbcConnection Dim sqlCmd As OdbcCommand Public Overrides Sub AcquireConnections(ByVal Transaction As Object) mySQLConn = CType(Me.Connections.MySQLDatabase.AcquireConnection(Nothing), OdbcConnection) End Sub Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) sqlCmd = New OdbcCommand("INSERT INTO steve_test(ShipTo, YearValue) VALUES(" & Row.ShipTo & ", '" & Row.YearValue & "')", mySQLConn) sqlCmd.ExecuteNonQuery() End Sub Public Overrides Sub ReleaseConnections() Me.Connections.MySQLDatabase.ReleaseConnection(mySQLConn) End Sub End Class
Run it and you are done! Easy. Now you can write data into MySQL using SQL 2005 SSIS (or any ODBC destination if you can get it to work :))
9 replies on “SQL 2005: SSIS – Pushing Data to MySQL using Script Component Destination”
Will this approach work for a non SQL server data source? I have a mumps DB which I need to extract data from and load into SQL Server
not sure, is there an ODBC driver? then you can use it as source connection.
I can't get pass the following error that shows when I try to build:”Error 1 Validation error. Data Flow Task: Script Component : System.InvalidCastException: Unable to cast object of type 'MySql.Data.MySqlClient.MySqlConnection' to type 'System.Data.Odbc.OdbcConnection'. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.AcquireConnections(Object transaction) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction) Package.dtsx “
not sure, works fine on mine, I just tried it again. Are you sure you have the MySQL odbc stuff all installed correctly?
Make sure you’re using System.Data.Odbc provider as the data source for your Connection Manager in the Script Destination. I was using a .NET ProvidersMySQL Data Provider as the connection manager in the script component and got the same error. It worked for me once I change the connection manager to the Odbc one.
Trying to get this to work…only using a single variable initially and I continue to get the following:[DTS.Pipeline] Warning: The output column “PlayerID” (1949) on output “Output 0” (1193) and component “IQC Player” (1170) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance. Thought I followed all steps correctly, but must be missing something. Here is the script code:' Microsoft SQL Server Integration Services user script component' This is your new script component in Microsoft Visual Basic .NET' ScriptMain is the entrypoint class for script componentsImports SystemImports System.DataImports System.MathImports Microsoft.SqlServer.Dts.Pipeline.WrapperImports Microsoft.SqlServer.Dts.Runtime.WrapperImports System.Data.OdbcPublic Class ScriptMain Inherits UserComponent Dim mySQLConn As OdbcConnection Dim sqlCmd As OdbcCommand Public Overrides Sub AcquireConnections(ByVal Transaction As Object) mySQLConn = CType(Me.Connections.IQModel.AcquireConnection(Nothing), OdbcConnection) End Sub Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) sqlCmd = New OdbcCommand(“INSERT INTO IQC_Player on duplicate key update PlayerID=” & Row.PlayerID & “')”, mySQLConn) sqlCmd.ExecuteNonQuery() End Sub Public Overrides Sub ReleaseConnections() Me.Connections.IQModel.ReleaseConnection(mySQLConn) End SubEnd ClassAny help is greatly appreciated.
Thank you!!! You just saved me a ton of time!
Awesome, Thank you!