CEDANIEL.COM

This post applies to Microsoft Office SharePoint Server 2007, WSS 3.0, SQL Server 2008 Integration Services, and MySQL.

Scenario: Many times as SharePoint lists grow to the 1000s of rows, it is more applicable to build your reporting system external of the SharePoint system. Data Views begin to be difficult to deal with and you may need to build a custom solution for optimal performance. In my case, I have a mixed shop of developers that have a variety of skills. When I lead a project with my team, I try to distribute the workload as best as I can so that all can contribute to the quality and completion of the project. One of my developers is extremely talented in the world of Linux, Apache, MySQL, and PHP (aka LAMP). I still believe that the LAMP stack is the best environment for web applications but lately the requirements of projects have been dominated by the world of SharePoint. SharePoint has become Microsoft’s hostile takeover environment. It seems they are wrapping all of their services underneath it. All stack examples I’ve seen lately seem to include SharePoint somewhere in the pipe.

So, knowing that IT Shops are buying into SharePoint (hardcore) and executives are getting used to its look and feel.  SO… what do we do with our highly skilled LAMP developers? They cannot be left out of the project. In my opinion, we need to at least give them the reporting portion of the project. However, according to this technique that I’ll describe in the solution below it is possible to build a LAMP solution that can make a transaction to a SharePoint list. I have not fully tested this solution yet – but in theory it should work. So…….. how do we do this?

Solution: In summary: What you will be doing is using Microsoft SQL Server 2008 Integration Services to build a package that will take data from a SharePoint List to MySQL. This solution is possible using Microsoft SQL Server 2005 but it is much easier using the 2008 version. SSIS 2008 comes with an ADO.NET destination component for the ETL engine Data Flow. The ADO.NET component made it much easier to set up the connection and send the data to MySQL.

First you need to setup an ODBC connection on your development box that uses an account that can successfully connect to your MySQL database. Go to the MySQL Site and download/install the ODBC Drivers for Windows here: Click Here

Once you have downloaded the drivers, you can setup your connection and test it. To do this. Go to Start –> Control Panel –> Administrative Tools –> Data Sources (ODBC). This will open the “ODBC Data Source Administrator” dialog box. Click on the “System DSN” tab and click the “Add” button. Locate the entry titled “MySQL ODBC 5.1 Driver” and SELECT it. This will load the MySQL Connector/ODBC Data Source Configuration. Fill out fields in the form and make sure you click the “Test” button to confirm your connection is Successful. Then Click “OK” to Save it. You should now see your connection listed in the System Data Sources. Click “OK” to close the ODBC Data Source Administrator Dialog.

Now that you have your ODBC Connection defined, you are ready to build your Integration Services Package.

BUT, before you begin make sure you follow the instructions in this blog entry: Click Here

This blog entry will give you instructions on how to install an adaptor that you will need to pull data from a list and send it to a database server. Once this adaptor is setup you will be able to add the “SharePoint List Source” tool to your SSIS Package.

One final thing needs to be done in MySQL before you develop the package. When SharePoint sends data via SSIS, there will be quotes associated with text. This will cause a problem when attempting to import the data into MySQL because the database will not know what to do with the quotes around the text data. In other words MySQL is expecting:

HERE IS TEXT                      (Instead of)                    “HERE IS TEXT”

So to fix this problem, you have to go into MySQL and globally configure the database to set “sql_mode” to ANSI_QUOTES. MySQL Describes setting ANSI_QUOTES as:  Treat “"” as an identifier quote character (like the “`” quote character) and not as a string quote character. You can still use “`” to quote identifiers with this mode enabled. With ANSI_QUOTES enabled, you cannot use double quotes to quote literal strings, because it is interpreted as an identifier.

Here is the MySQL reference that describes MySQL SQL MODES: Click Here

With all the above accomplished, you will now be able to build your package.

Some steps to help you build the package:

Add a Data Flow Task to your Control Flow in the package. Go inside the Data Flow Task and add a “SharePoint List Source” control and an “ADO.NET Destination” control. Configure the “SharePoint List Source” control with a SharePoint “SiteListName”, “SiteListViewName”, and “SiteUrl” (Use your All Items view fo rthe “SiteListViewName” for testing).  Check your “Column Mappings” to verify the control is seeing the List Data. Click “OK” to close the control.

Now drag the Green Arrow from the “SharePoint List Source” to the the “ADO NET Destination” to connect the flow. Now configure the ADO NET Destination Control. In the “Connection Manager” section, click the “New…” button. This will open the “Configure ADO.NET Connection Manager”. Click on the “New…” Button inside here.  This will take you to the “Connection Manager” dialog. From the “Provider” drop-down, choose “Odbc Data Provider” fromt the choices under “.Net Providers”. Now, under the “Data Source specification” section (make sure “Use user or system data source name:” is selected) click the drop down and find your previously defined ODBC connection you created above.  Under the “Login information” section, enter the User name and Password that has access to the MySQL Database. Now click the “Test Connection” button to confirm that your connection is successful. It the connection is successfull, click the “OK” button and you will see your MySQL connection listed in the “Configure ADO.NET Connection Manager” dialog. Now click “OK” to get out of this. You should now be back in the “ADO.NET Destination Editor” dialog. In the “Use a table or view” drop down, TYPE the name of a table in your MySQL database (many time the drop down will not populate properly – must be a bug – so you have to type the name of the table). Once you type the name of the table, click the “Preview” button to confirm the connection to the table.  Now click the “Mappings” link on the left side of the Dialog. This will show you what fields you have available from the SharePoint list on the left and what receiving fields you have available in the MySQL database/table on the right. If the fields are matched exactly, SSIS will attempt to predict a match with lines added between the two. If a prediction is not made, just click a field from the left and drag it to a field on the right. It will create a line between the two. Once you are finished mapping the fields, click the “OK” button.

Now you are ready to Run your package. Hopefully, you see “Greenage”. If the package is successful, you’ll see the final step turn green. Check your MySQL Database/table for data.

Now your LAMP developer can develop a reporting system off of the MySQL database.