How to Export SQL Views into SharePoint

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.

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.

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.

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.

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.

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

Choose a Destination

Save and Execute Package

Complete the Wizard

The Execution was Succesful

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

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 .