In continuation with my previous post on Creating a SQL Server Reporting Services (SSRS) Project, you will now learn how to create a Shared Data Source within that project. In this post I compare and contrast Shared and Embedded Data Sources and demonstrate how to create a Share Data Source.

A Data Source is a data connection that includes the type of data source that you are connecting to, the connection information (Server Name, Database Name), and the credential type (Windows or SQL Authentication for example). For more information on Data Sources got to:

• Data Connections, Data Sources, and Connection Strings

• Embedded and Shared Data Connections or Data Sources

In this posting I will explain how to create a Share Data Source. When we get to the point when we create an actual SSRS Report I will explain and demonstrate how to create an Embedded Data Source.

Shared and Embedded Data Sources

When developing SSRS projects you have two choices when creating Data Sources, Shared or Embedded. The primary difference between the two is that the Shared source can be used by all reports in the project, while the Embedded source can only be used by the report in which it is created. Therefore, if a change occurs that affects an Embedded source (change to server name, database name, logon credentials, etc…) someone will be required to update the configurations within each report. So if you create 50 reports that include the same Embedded source, you would need to open each report and update the affected data source. On the other hand, if the same reports used a Shared source, the change would only need to be made in one place. This will become clearer once you actually create both types of sources. In addition to the aforementioned, the Shared sources are publishable and are required for Share Datasets, which will be discussed later in this series.

Video

Creating a Shared Data Source

1. Open SQL Server Data Tools (SSDT).
2. Open the project that you created in the Creating a SQL Server Reporting Services Project blog. You can also download the project from here.
3. If the Solution Explorer is not open, Go View in the menu and select Solution Explorer.

    

   

    

4. In the Solution Explorer right-click the Shared Data Sources folder.

    

   

    

5. Select Add New Data Source from the menu.
6. The Shared Data Source Properties window will open.
7. Ensure that General is selected in the left section of the window.
8. Enter SQL_ContosoSchoolDW in the Name textbox.
9. Select Microsoft SQL Server from the drop down list labeled Type. If you expand the drop down list you will see a complete list of the available connection types that are natively available in SSRS.

    

   

    

10. Click the button labeled Edit….
11. The Connection Properties window will open.
12. Enter your SQL Server name in the Server name textbox.
13. If you are using Windows Authentication accept the default. If you are using SQL Server Authentication select it and enter a user name in the User name textbox and a password in the Password textbox.
14. Select ContosoSchoolDW from the Select or enter a database name drop down list.

     

    

     

15. Click the Test Connection button to verify that your connection is valid.
16. Click the OK button.
17. Back on the Share Data Source Properties window a connection string has been inserted into the Connection string textbox.

     

    

     

18. Click Credentials in the left section of the window.
19. Several options are available. Review the options and accept the default (Use Windows Authentication (integrated security)) for now.

     

    

     

20. Click the OK button and your Share Data Source is created.

Download:  Creating Share Data Source

In the next post, SSRS 101 – Creating a Shared Dataset, I will explain and demonstrate how to create a shared dataset.

Talk to you soon,

Patrick LeBlanc, Microsoft, Technical Solutions Professional SQL Server and Business Intelligence

Founder www.sqllunch.com