SQL 2005: SSIS – PUSHING DATA TO MYSQL USING SCRIPT COMPONENT DESTINATION
来源:互联网 发布:机械制作软件 编辑:程序博客网 时间:2024/04/30 02:11
SQL 2005: SSIS – PUSHING DATA TO MYSQL USING SCRIPT COMPONENT DESTINATION
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 SystemImports System.DataImports System.MathImports Microsoft.SqlServer.Dts.Pipeline.WrapperImports Microsoft.SqlServer.Dts.Runtime.WrapperImports 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 SubEnd 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 :))
- SSIS – Two Ways Using Expressions Can Make Your Life Easier – Multi DB Select, Non Standard DB Select
- Dev and Prod Systems, Using a HOST file to ensure your datasource is pointing the right system
- SSIS – Custom Control Flow Component – Execute SQL Job And Wait
- ETL Method – Fastest Way To Get Data from DB2 to Microsoft SQL Server
- SSIS – Pulling Data from a non default collation DB to a default collation DB
- SQL 2005: SSIS – PUSHING DATA TO MYSQL USING SCRIPT COMPONENT DESTINATION
- SSIS - Script Component, Split single row to multiple rows
- SSIS: Throwing errors from script task/component
- SSIS - OLE DB Destination (Data Access - Fast load configuration)
- Using SQLCMD to deploy sql script on SQLServer
- Using Presto to combine data from Hive and MySQL in one SQL-like query
- Using sqoop to move data from mysql to hive
- Informatica – PowerCenter Express – Data Sync – MSSQL to MySQL ( Using MySQL JDBC Driver )
- Pushing data changes from the server to clients
- Using ROW_NUMBER() to paginate your data with SQL Server 2005 and ASP.NET
- Only Using the MDF File to Restore the Data Base in SQL SERVER 2005
- sql table data copy to exel using vba
- Store binary data to MySQL using escape_string in MySQLdb
- Building Data Dictionary using SQL Server 2005
- SQL Server 2005 Integration Services (SSIS)数据源之MySQL
- How to save SQL Server data as Insert INTO SQL Script
- SSIS之操作Script Component组件的Blob Column类型的处理
- bind "xxx.js" to object using script
- 教育培训服务业的电子商务在哪里?未来这个领域商业机会在哪?现在发展过程的困境是什么?
- 为什么要禁止在正式程序中使用sleep语句?
- 工作队列 的变化
- C语言的3des、java的3des、C#的3des。三种加密遇到的问题
- [笔记]Javascript中的11个难以理解的问题
- SQL 2005: SSIS – PUSHING DATA TO MYSQL USING SCRIPT COMPONENT DESTINATION
- ubuntu普通用户进入根目录的方法
- Java版中文转汉语拼音工具
- 英语
- cp 复制文件与文件夹
- JAVA功能代码《5》----将Java中的util.Date转换成sql.Date
- Configuring a Gateway of Last Resort Using IP Commands
- Xfire 图片(image) webservice byte 加密 传输 ---- 上传
- 【头脑风暴】产品流程图