Mixing SQL Server data and SharePoint lists data in the same query

Even though it was already possible to join both SQL Server data and SharePoint lists in the current version by using the “xmlQuery” statement, we have gone a step further in this new version of Enesys RS Data Extension (Standard and Enterprise edition only) in order to make the process plain simple.

When using the “xmlQuery” statement, you need to have an “aspx” page (or whatever technology you like) that will query the Database and return the data into XML format so that it can be consumed by Enesys RS Data Extension. That was better than nothing but somewhat tedious.

With the addition of our new “sqlServerQuery” statement things are much more straightforward. You can now specify a connection string to your database within the integrated query designer, write your SELECT statement to retrieve data from your SQL Server data tables, preview the data in the query designer and finally mix this SQL Data with other SharePoint lists.

Basic Intellisense features

When joining two SharePoint lists, you will usually come up with a query that contains three statements.

Two “list” statements used to retrieve data from your SharePoint lists and a sqlQuery statement that will contain a SQL SELECT clause for joining the two SharePoint lists.

In previous versions, you don’t have much help when writing the SELECT clause. For simple joins, that may not really be a concern. For more complex SELECT clauses, writing down the field and list names may be a more tedious task.

Version 3.4 provides basic intellisense features that will make it unnecessary to look around for column names:

For more information on Enesys RS Data Extension 3.4 new features, see “What’s new” section.

Enesys IS Data Extension Release Candidate 2

Enesys IS Data Extension RC 2 has just been released. Our SQL Server Integration Services extension, is provided as part of Standard and Enterprise editions of Enesys RS Data Extension.

Enesys IS Data Extension Release Candidate 2 includes new data flow source and destination components for synchronizing SharePoint lists with SQL Table. That approach lets you keep in sync extremely large lists in no time.

For more information see “Enesys IS Data Extension RC2”