Versioning and Source Code Control - SSIS

SQL Server 2014 and SQL Server Integration Services (SSIS) integrate with source control products such as Subversion (SVN) and Visual Studio Team System. SVN is an open-source control product commonly used on many types of development platforms. Given the open-source nature of the product, several people have written extensions and integration points into different tools, such as Visual Studio. Visual Studio Team System is part of Microsoft’s Team Foundation Server, a suite of SDLC management tools — which includes a source control engine.

Learn how to use SSIS, from beginner basics to advanced techniques, with online video tutorials taught by industry experts. Enroll for Free "SSIS Training" Demo!

SSIS Version Control

Subversion (SVN)

SVN is an open-source product that you can download and install. The easiest version to work with on the Windows platform is from www.visualsvn.com because it includes an SVN server management UI. You can download and install the server repository VisualSVNServer from the site just mentioned. This section also uses a Visual Studio plug-in for SVN called AhnkSVN, which is available from https://ankhsvn.open.collab.net. This integrates Visual Studio with the SVN repository. In this section, you’ll create a project in SQL Server Data Tools (SSDT) that demonstrates integrated source control with SVN.

To begin, install VisualSVNServer and AhnkSVN from the sites just mentioned.
To configure SSIS source control integration with SVN, open the SQL Server Business Intelligence Development Studio. You don’t need to connect to an instance of SQL Server to configure integrated source control.

To configure SVN as your SSIS source control, click Tools? Options. Click Source Control and select Plug-in Selection. Select “Ankh – Subversion Support for Visual Studio” for the Current source control plug-in, as shown in the below screenshot.

img

For the purposes of this example, use “AnkhSVN – Subversion Support for Visual Studio” from the Source Control Environment Settings dropdown list and use the source control default options (refer to below screenshot). This SVN walkthrough assumes that you have installed the SVN server and AnkhSVN on your local machine.

Open SQL Server Business Intelligence Development Studio. Because SSDT uses the Visual Studio Integrated Development Environment (IDE), opening SQL Server Business Intelligence Development Studio will open Visual Studio 2013.

When the SSDT IDE opens, click File ? New ? Project to start a new project. Enter a project name in the New Project dialog. For now, do not check the Add to source control checkbox, as shown in below screen shot.

img

Click OK to proceed, and a new project is created in the SSDT IDE.

Add the project to SVN by right-clicking the project name in Solution Explorer and selecting Add Selected Projects to Subversion.
You will be prompted to choose the Subversion repository and folders.

a. The Project Name is what will be named within the SVN repository.
b. The Repository Url is the location of the Project within your SVN repository. Below screen shot

shows a new folder created in the ProSSIS repository within SVN.
a new folder created in the ProSSIS repository within SVN
 

NOTE You will have to have a repository created to go through this exercise. This is done through the Visual SVN Server application.

1. Select OK to add your project to the SVN repository.

2. In the next screen, specify a log message, which is created as part of any operation with SVN so that changes can be documented, as shown in below screen shot. Click OK.
which is created as part of any operation with SVN so that changes can be documented

3. The final dialog asks if you would like to mark the project as managed by Subversion. Click Yes.

4. After successfully connecting the project to SVN, you will notice a few new indicators in Visual  Studio that demonstrate the integration. Below screen shot shows the Solution Explorer on the right with a few indicators next to the files. The blue plus symbol indicates that a file is pending initial addition to the Subversion project. The Pending Changes window at the bottom shows all the operations that need to be committed to SVN. SVN uses a commit process to save all changes.
the Solution Explorer on the right with a few indicators next to the files

5. The project shown here has a few packages added for demonstration purposes. You can add any existing packages to your project to test the source control integration.

6. To complete the connection of the project with SVN, click the Commit button within the Pending Changes window. This will add all the files to the SVN repository. To confirm this, open the VisualSVN Server component from the Start ? All Programs menu. Drill into the local server repository and the folder you created for the project. You should now see all the files from the project listed in the details window (see below screen shot). You may need to rightclick on the Repository in Visual SVN Server and click “Refresh” to see the files.

7. You may need to rightclick on the Repository in Visual SVN Server and click “Refresh” to see the files

8. In addition to the files being added to the SVN repository, note that the files shown in the Solution Explorer in Visual Studio now have a blue checkbox next to them (instead of a blue plus symbol). This indicates that the files have now been added to the SVN repository.

9. To test SVN integration with your new SSIS project, manually lock one of the packages in your project for editing by right-clicking the package in Solution Explorer and choosing Subversion ? Lock, as shown in below screen shot.
manually lock one of the packages in your project for editing by right-clicking the package in Solution Explorer and choosing Subversion ? Lock

10. When you lock a file for editing, you prevent other people from checking in any edits of the file until you have completed your edits. The Select Files to Lock dialog appears, as shown in below screen shot. You can enter a comment to identify why you are locking the package.

11. The Select Files to Lock dialog appears

12. After you edit and save the package, the file is displayed in the Pending Changes list, and it has updated icons in Solution Explorer to indicate both that it has been changed and that it is locked (see in below screen shot).
it has updated icons in Solution Explorer to indicate both that it has been changed and that it is locked.

NOTE This is a good location for change control documentation references or meaningful notes. Although it may seem obvious what change you have made in the code, documenting the change will help you in the future if you need to roll back a change or remember what changes were made!

13. You can now commit your changes by clicking the Commit button in the Pending Changes window, and the packages will be checked in and unlocked for the next operation.

14. Like many source control tools, SVN comes with several valuable features for working in a team situation and for code and process management. One of those features is versioning.

15. Every check-in operation preserves the previous version, so the entire version history can be acted upon. below screen shot shows the version history of a file with the right-click context menu displayed. Older versions can be restored and compared with current versions, logs can be updated, and changes can be highlighted.
the version history of a file with the right-click context menu displayed

16. Now that you’ve taken a look at what a common open-source tool (SVN) can do in terms of source code management and team coordination, it’s time to look at an enterprise tool in the Microsoft technology stack. The next section provides a brief introduction to Microsoft’s source control server and client tools known collectively as Visual Studio Team System.

[Related Article: SSIS Tutorials]

Team Foundation Server, Team System, and SSIS

When Visual Studio 2010 was released, Microsoft updated Team System and Team Foundation Server — a powerful enterprise software development life cycle suite and project management repository consisting of collaborative services, integrated functionality, and an extensible application programming interface (API). Team System seamlessly integrates software development, project management, testing, and source control into the IDE for Visual Studio 2013.

Using Team System with SSDT and SSIS requires that you have at least Visual Studio Team System 2010 on your network and have installed at least Team Explorer 2010 on your development machine. For the purposes of this SSIS Software Development Life Cycle Topicwe’ve installed TFS 2013 and Visual Studio 2013. Also keep in mind that in order to create Projects and Team Collections the version of TFS must match the version of Visual Studio that is being used. For instance, Visual Studio 2013 cannot create Projects and Team Collections in TFS 2010, but Visual Studio 2010 can.

Visual Studio Team System 2013 (VSTS) can be purchased for use, but if you would like to evaluate the software, Microsoft provides a virtual machine that you can download and used for a period of time. Trial versions are available for both virtual PC and Hyper-V images and can be found at https://download.microsoft.com. Search for VSTS 2013 Trial on the Microsoft download page, and the results will include both virtual machines that can be used for testing. The examples in this section use the same trial version of VSTS.

Visual Studio Team Explorer 2013 is the client tool that integrates with Visual Studio and allows you to connect to the Team System server and explore the development items and work with the source control environment built into VSTS. The source control used by VSTS is a robust source control environment. You can search for and download Visual Studio Team Explorer from https://download.microsoft.com.

After satisfying the preceding requirements, follow these steps to use the VSTS source control. In the next section, you will also see how to create bug tracking tickets and work items in the VSTS Team Explorer.

To configure Team Foundation Server as your SSIS source control, open SSDT (or close any existing open projects) and click Tools ? Options. Choose Source Control and select Visual Studio Team Foundation Server. Expand the Source Control node for detailed configuration, as shown in below screen shot.

Expand the Source Control node for detailed configuration

MSF Agile and SSIS

MSF Agile is an iterative methodology template included with Team System. In a typical agile software project, a time- and scope-limited project — called an iteration — is defined by collaboration with the customer. Deliverables are established, but they may be de-scoped in the interests of delivering a completed feature-set at the end of the iteration. An important aspect of agile iterations is that features slip, but timelines do not. In other words, if the team realizes that all features cannot be developed to completion during the time allotted, the time is not extended, and features that cannot be developed to completion are removed from the feature-set.

NOTE Agile methodologies are very suitable to SSIS and BI development projects because they allow more flexibility; changes can be readily adopted to provide an end solution that is suited to the user’s needs.

No one uses a single methodology alone. There are facets of waterfall thinking in any iterative project. In practice, your methodology is a function of the constraints of the development environment imposed by regulatory concerns, personal style, and results.
Once an MSF Agile Team Project hierarchy has been successfully created, the following subitems are available under the project in Team Explorer (refer to above screen shot):

• Work Items
• Documents
• Reports
• Team Builds
• Source Control

The next sections cover what’s available to you in each of these subitems.

Work Items

1. In MSF Agile projects, work items consist of tasks, bugs, scenarios, and Quality of Service (QoS) requirements.

2. Bugs are self-explanatory — they are deficiencies or defects in the code or performance of the application.

3. Scenarios map to requirements and are akin to use cases in practice.
Quality of Service (QoS) requirements include acceptable performance under attack or stress. QoS includes scalability and security.

4. Tasks are a catchall category for work items that includes features yet to be developed.

5. To create a work item, right-click the Work Item folder, select Add Work Item, and choose one of the work item types. Below screen shot shows the work item Bug template, which enables bugs to be tracked and handled for your SSIS project.

shows the work item Bug template, which enables bugs to be tracked and handled for your SSIS project

Documents

The MSF Agile template includes several document templates to get you started with project documentation. Included are the following:

1. Development: Microsoft Project templates for development and testing efforts

2. Process Guidance: An HTML document that describes the MSF Agile process

3. Project Management: An Excel template containing a project “to do” list and an issues and triage spreadsheet

4. Requirements: Listing requirements for validation scenarios and a Quality of Service (QoS) Requirements list

5. Security: Document sample defining the security plan for functional areas in the solution
Shared Documents: A repository for miscellaneous project documents Test: Test plans for unit and integration testing

Reports

The MSF Agile template contains several built-in Reporting Services project status reports. These reports are accessible directly from Reporting Services or from the Project Portal (SharePoint Portal Services) website.

The Reporting Services home page contains links to several reports grouped by report type, as shown in below screen shot.

The Reporting Services home page contains links to several reports grouped by report type

A few examples of valuable reports include:

Capacity Chart with Work Assigned gives a snapshot of the capacity of the amount of work that can be performed given the current team and the development velocity.

Burn Rate Chart shows the amount of work that can’t be performed given the number of hours left and the current trend of work velocity. This report shows hours completed, hours remaining, ideal trend, and actual trend.

Remaining Work report shows the teams progress on a current iteration by highlighting the work completed versus the work remaining trended over time. The Remaining Work report is part of the larger reporting solution provided by the Project Portal.

Team Builds

A Team Build is the compilation of the code together to be used for deployment. For SSIS, this would be packages in your project that are deployed to the server. If you will be using the SSIS 2014 and deploying your packages to the SSIS Server, then you will be using the deployment functionality in Project Deployment Model in SSIS. This is covered in Administering SSIS Topic.

Source Control

Source control within Visual Studio Team System is very similar to the SVN functionality walked through earlier in the SSIS Software Development Life Cycle Topic. You are able to check items into the source control, revert to a prior version of a package, keep notes as to what was changed, and so on. The section “Version and Source Control with Team System” later in the SSIS Software

Development Life Cycle Topic covers the VSTS source control features.

The Project Portal

The Project Portal is implemented in SharePoint Portal Services and contains several helpful portals, including the following:

• Announcements
• Shared documents and Wiki sites
• Reports (Bug Rates, Builds, and Quality Indicators)

The Project Portal provides a nice interface for the development team, but project managers are the target audience. The Project Portal can also serve to inform business stakeholders of project status. To navigate to the Project Portal home page, right-click the Team Project in Team Explorer and click Show Project Portal. Below Screen shot shows an example of the Project Portal with one of the reports viewed.

An example of the Project Portal with one of the reports viewed
Putting Team System to Work

In this section, you’ll create a small SSIS package to demonstrate some fundamental Team System features.

Frequently Asked SSIS Interview Questions & Answers

Create a new SSIS project in SSDT by clicking File ? New ? Project. From the Project Types tree view, select Business Intelligence Projects. From the Templates list view, select Integration Services Project. Do not check the Add to Source Control checkbox. Enter SSIS VSTS Integration Example as the project name in the Name text box, as shown in Below screen shot.

1. Enter SSIS VSTS Integration Example as the project name in the Name text box

2. Click OK to create the new project. Drag a Data Flow Task onto the Control Flow workspace.

3. Right-click in the Connection Managers tab and select New OLE DB Connection to add a database connection. Click the New button to create a new OLE DB Connection.

4. Select your local server from the Server Name dropdown list. Configure the connection for Windows or SQL Server authentication. Select AdventureWorks as the database name. You can click the Test Connection button to test the connectivity configuration. Click OK to close the Connection Manager dialog, and OK again to continue.

5. Double-click the Data Flow Task to edit it. Drag an OLE DB source onto the Data Flow workspace. Double-click the OLE DB source to edit it.

6. In the OLE DB Source Editor window, select the AdventureWorks connection in the OLE DB Connection Manager dropdown list. Select Table or View in the Data Access Mode dropdown list. Select [Sales].

7. [SalesOrderHeader] in the “Name of the table or the view” dropdown list. Click OK to continue.

8. Drag an Aggregate Transformation onto the Data Flow workspace. Connect the output of the OLE DB source to the Aggregate Transformation by dragging the blue arrow from the source to the transformation. From the Available Input Columns table, select OrderDate and SubTotal. In the grid below, ensure that the operation for OrderDate is Group by, and the operation for SubTotal is Sum, as shown in the Below screenshot.

Ensure that the operation for OrderDate is Group by, and the operation for SubTotal is Sum
Click OK to close the Aggregate Transformation Editor.

9. Drag an Excel Destination onto the Data Flow workspace and connect the Aggregate output to it. Double-click the Excel Destination to open the Excel Destination Editor. Click the New button beside the OLE DB Connection Manager dropdown list to create a new Excel connection object. Enter c:SSIS_output.xlsx in the Excel file path text box. Click OK to continue.

10. You can create an Excel spreadsheet in this step. If you enter the desired name of a spreadsheet that does not yet exist, the Excel Destination Editor will not be able to locate a worksheet name. In this case, the “No tables or views could be loaded” message will appear in the Name of Excel Worksheet dropdown list.

11. To create a worksheet, click the New button beside the Name of the Excel Sheet dropdown list. A Create Table dialog will appear. Click OK to accept the defaults and create the worksheet and Excel workbook.

12. Click Mappings in the Excel Destination Editor to configure column-todata mappings. Accept the defaults by clicking OK.

13. Click File ? Save All to save your work.

Now that you have created a simple SSIS package, you will use this package to test the Team System functionality with SSIS.

Version and Source Control with Team System

The objective in this section is to walk you through integrating your SSIS project and package with Team System source control and versioning functionality.

To add your SSIS project to the Team Project, open Solution Explorer, right-click the project, and click Add Project to Source Control.

The Add Solution SSIS VSTS Integration Example to Source Control dialog appears, containing a list of Team Projects. Select the SSIS VSTS Project you created earlier, as shown in below screen shot.

Select the SSIS VSTS Project you created earlier

Click OK to continue. You have successfully created a Team Project and an SSIS project. The Team Project contains version control information — even now.

Click View ? Other Windows ? Pending Changes to view the current source control status for the SSIS project, as shown in below screenshot.

To view the current source control status for the SSIS project

The Change column indicates that the files are currently in an Add status. This means the files are not yet source-controlled but are ready to be added to source control.

Click the Check In button to add the current SSIS VSTS Integration Example project to the SSIS VSTS Project’s source control. This clears the Pending Checkin list. Editing the SSIS VSTS Integration Example project will cause the affected files to reappear in the Pending Checkin list.

NOTE: Any change made to the SSIS VSTS Integration Example project is now tracked against the source-controlled version maintained by the SSIS VSTS Project. Seemingly insignificant changes count: For instance, moving any item in the Data Flow workspace is considered an edit to the package item and is tracked.

The default behavior for source control in Visual Studio is that checked-in items are automatically checked out when edited.

You can view the current status of all Team Projects on your Team Foundation Server in the Source Control Explorer, as shown in below screen shot. To access the Source Control Explorer, double-click Source Control in the Team Explorer or click View ? Other Windows ? Source Control Explorer.

You can view the current status of all Team Projects on your Team Foundation Server in the Source Control Explorer

This next example implements a larger change to demonstrate practical source control management, before moving into some advanced source control functionality

In your SSIS project, add an Execute SQL Task to the Control Flow workspace. Configure the task by setting the Connection Type to OLE DB, the Connection to your AdventureWorks connection, and the SQLSourceType to Direct input. Set the SQL Statement to the following:

List of Related Microsoft Certification Courses: