Pin Me

How to Export SQL Views into SharePoint

written by: George Garza•edited by: Lamar Stonecypher•updated: 3/2/2010

SharePoint can be a central source to implement different data types and can display them in the Web parts. It's a collaboration front-end tool that can bring in data from various back-end sources, like SQL, or Excel, or XML, or operational functions of Visual Studio with C# or Visual Basic.

  • slide 1 of 5

    Back End and Front End Database

    The term back-end database comes from developers taking a small database programming system and providing the end-user operations with the database to make them a single item. The developer then decides how to integrate the data for display. One of the most frequently used tool sets is Microsoft Access.

    Using the back-end has advantages in terms of scalability and performance. The back-end database can have hundreds of thousands of data rows, but only a small portion will be for display.

    The front-end is the portion that will function to display the data from the back end. One other feature that is noteworthy is that the data from the two different systems does not have to be the same type. Frequently SQL data have different specifications than SharePoint. The conversion tools are built in to the software making the back-end - front-end operations feasible.

  • slide 2 of 5

    What is a SQL View?

    SQL, short for Structured Query Language, provides the means whereby database programmers retrieve data from a database. Normally the results are presented within a temporary table, which are the answers to the query previously posed.

    A view is a temporary table, a virtual table because it is not intended to last. Rather the view in a database is created under specific conditions. It is created by a query joining one or more tables using the SQL language. The results are displayed in a table, and this table is used to create the view.

    Here is an example of a SQLstatement

    SELECT

    Product_List.ProductID, Product_List.ProductName

    FROM

    Products AS Product_List

    WHERE (Product_List.Discontinued = 0)

    This statement produce two items the ProductID and the ProductName from a table called Products, with the condition that the products have not been discontinued.

  • slide 3 of 5

    Display and Web Parts in SharePoint

    SharePoint is a browser based program. It is capable of displaying information in a variety of contexts. It frequently acts like a front-end to other programs like SQL Server 2005, or C# (sharp) or Visual Basic that will serve as back-end programs. Some of the ways that the data from a SQL view can be displayed is within a Web Part. This is part of the browser display that can contain information from other sources. Web Parts are building blocks in a SharePoint Site. One of the ways of displaying data from a back-end operation is using a List View. This will take the data and show it on the front-end, which then will show up in the Web Part.

  • slide 4 of 5

    Exporting SQL View to SharePoint

    To export SQL data to SharePoint you must use SQL Server Integration Services (SSIS). This is part of the Sql Server 2005 package. Run the import/export wizard to start the export.

    Start the Export Wizard 

    Choose the Data Source

    Choose the Data Source 

    If the source data is a view, the SQL Server Import and Export Wizard automatically converts the view to a table in the destination.

    Data Source File Name 

    Enter the Name of the Data Source. SQL SSIS can exporting a variety of different files: Excel, flat files, CSV, Access,or XML.

    Select The Name of the Data Source 

    Choose a Destination

    Choose a Destination 

    Save and Execute Package

    Save and Execute Package 

    Complete the Wizard

    Complete the Wizard 

    The Execution was Succesful

    The Execution was Successful 

    Those are the export steps. At the other end SharePoint can import the following kind of files: spreadsheets, calendar, list, Cxcel, list template, flat files. Thus multiple file types are not an import problem for SharePoint.

    Source: How to: Run the SQL Server Import and Export Wizard

    Image Source: SQL Server 2005 Import / Export Wizard

  • slide 5 of 5

    Summary: Exporting SQL View to SharePoint

    Back-end data that derives from SQL can be integrated into a front-end program, like Sharepoint.That is what makes SharePoint a powerful utility for bringing disparate pieces of software together. Data sources can find a place in SharePoint, whether they are Access, Excel, CSV, or SQL.

    Source:http://office.microsoft.com/en-us/sharepointserver/HA102164281033.aspx

    SQL Views can be seen here.

    Click the following to view SQL Web Part Editor .

SharePoint and Applications

SharePoint can be used as a stand alone browser application. But it can also be used with a variety of other programs and tools. In this series we look at how those tools and applications can be used to make SharePoint a more vibrant system.
  1. Integrating Silverlight and SharePoint
  2. SharePoint Policy and Procedure Templates
  3. How to Export SQL Views into SharePoint