Getting data out of MySQL with SSIS is a snap. Putting data into MySQL has been a different matter. I’ve always done this in the past with a hodgepodge of ODBC, Linked Servers, OPENQUERY and the Script Task. All of these work well but they’re just not as convenient as loading files with the OLED Destination.
|I recently attempted to use the ADO.NET Destination to load files into MySQL without luck. I tweeted about this and Todd McDermid (blog||twitter) kindly pointed me at a blog post about writing data to MySQL with SSIS. This works well but I am having difficulties with data conversions. The author of the blog post, Matt Mason, posted a follow up article with the various options of SSIS / MySQL interaction. All of these involve pushing data into MySQL but I though why not get it to pull data. Here’s an outline of this approach.|
First create the following table in a MySQL database. This is a clone of the HumanResources.Employees table from the AdventureWorks sample database. We will be doing a simple extract-truncate-load of the table into MySQL from SQL Server.
# Create MySQL replica table of the HumanResources.Employee from the AdventureWorks database CREATE TABLE `HumanResourcesEmployee` ( `EmployeeID` INT PRIMARY KEY NOT NULL, `NationalIDNumber` VARCHAR(15) NOT NULL, `ContactID` INT NOT NULL, `LoginID` VARCHAR(256) NOT NULL, `ManagerID` INT NULL, `Title` VARCHAR(50) NOT NULL, `BirthDate` DATETIME NOT NULL, `MaritalStatus` CHAR(1) NOT NULL, `Gender` CHAR(1) NOT NULL, `HireDate` DATETIME NOT NULL, `SalariedFlag` VARCHAR(5), `VacationHours` SMALLINT, `SickLeaveHours` SMALLINT, `CurrentFlag` VARCHAR(5), `rowguid` VARCHAR(40), `ModifiedDate` DATETIME );
This will export the contents of the HumanResources.Employees table and write it to a flat file. Edit the task and add an OLE Source and configure it as illustrated below. The OLEDB connection manager should point at your SQL Server instance hosting the AdventureWorks database.
Next add a Flat File Destination and name it “Write Employees File” then connect the OLEDB Source to it. Configure the connection manager as illustrated below. The important things to note here are the fact I’m writing the file to E:\Employees.txt and it is pipe delimited. You may need to alter these according to your setup.
The dataflow should now look something like below.
Go back to the Control Flow pane and add an Execute SQL Task to the canvas. Call this task “TRUNCATE TABLE HumanResourcesEmployee”. Edit the task and configure it like below. You need to add a ADO.NET connection manager which references an ODBC connection to your MySQL database. You’ll need the MySQL ODBC Driver for this.
My ODBC connection looks like below.
Add another Execute SQL Task and call it “Load new Employees file into MySQL”. Connect up the tasks in sequence.
Edit “Load new Employees file into MySQL”, add the ADO.NET connection to MySQL and enter the below SQL. You will need to change the file path and delimiter character if you have changed them.
LOAD DATA LOCAL INFILE 'E:\\Employees.txt' # Column order MUST match the table INTO TABLE HumanResourcesEmployee FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n' # Windows line terminator IGNORE 1 LINES; # Ignore column header line
I originally attempted to enclose this in a store procedure as this provides more flexibility but MySQL gave the following error.
LOAD DATA is not allowed in stored procedures
MySQL prevents you from using LOAD DATA INFILE inside stored procedures (my major beef with MySQL is what you can and cannot do inside stored procedures) so we have no choice but to enter it in the SQLStatement pane. Execute the package and if all goes well the Employees.txt file will be loaded into MySQL.
I’ve not yet used this in production so use with caution, but it’s simple to setup and fast. One thing to note for this load is that MySQL has replaced backslashes with hyphens in the LoginId column so it would be sensible to check all data conversions.