Integrating different systems can be a major challenge for technology professionals. While vendors try to make this process as easy as possible, they often focus on intra-generational integration rather than inter-generational integration. This can be particularly frustrating when trying to connect a long-standing SharePoint instance with a brand-new BI stack.
In the past, there have been some solutions available, such as the SharePoint List Adapter components provided by Microsoft. However, these solutions are often limited to specific versions of SQL Server and SharePoint, making it difficult to connect older SharePoint instances with newer BI tools.
One workaround for this issue is to use the script component in SQL Server Integration Services (SSIS). The script component allows you to run custom code within your integration processes, providing more flexibility and the ability to connect to any version of SharePoint.
Today, we will share a semi-generic script that can be used to connect any version of SSIS to any version of SharePoint (until WSDL is deprecated). Please note that this script requires some setup and editing to work in your specific environment. You will need to add code and service references, as well as configure the columns you want to retrieve.
To use the script, follow these steps:
- Drop a Script Component in your Data Flow and set it as a “source”.
- Set up the output columns.
- Identify an HTML connection manager.
- Alter the “Setup” part of the script as described in the script comments.
There are several issues that you may encounter when using this script, but we have found solutions for some of the most common ones:
- Understanding “Name” in SharePoint: SharePoint uses GUIDs instead of human-readable names for lists and views. You will need to translate the names into GUIDs to use them as arguments in the SharePoint web service.
- Reflection – Accessing Script Columns Generically: To make the script more generic, you can use the System.Reflection namespace in .NET to dynamically find the properties of the BufferManager class that represent the columns you want to set. This allows users to define column mappings without having to edit the code.
- Seeing ALL of the SharePoint Columns: If you are not seeing values from all of the columns when using the GetListItems method, try passing an empty XML node instead of null as the “list of columns to get”.
- Using Blobs for Really Wide Columns: SharePoint can handle more characters in strings than SSIS comfortably allows. If you encounter issues with wide columns, consider using DT_NTEXT in SSIS and NVARCHAR(MAX) in SQL Server.
While this script provides a solution for connecting SQL Server to SharePoint, there are still some challenges that remain. For example, the script requires users to manually add service references and references to the project in VSTA. It is possible to automate this process through code, but it requires additional development.
Despite these challenges, the script component in SSIS offers a flexible and customizable solution for integrating SQL Server with SharePoint. By leveraging the power of scripting, you can overcome the limitations of pre-packaged solutions and connect to any version of SharePoint.
Feel free to check out the full script and adapt it to your specific needs. If you have any improvements or extensions to share, please leave a comment where you publish your modifications.