Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Version Control with Integration Services

By P. Praveena,

Introduction

I had seen a poll on the SQLServerCentral homepage with regards to the version control system used for storing DB scripts and the results show that there are a considerable number of votes to the option “We don’t use source control for database scripts”. While there are a few options like schema comparison feature (not available in Visual studio professional edition) and the Red Gate SQL Source Control, which makes source control for database objects easier, these features aren’t available always at the workplace. Most times developers are more comfortable working directly in SSMS rather than using Visual Studio to create database projects and make changes from Visual Studio IDE. Working with SSMS, there is always a chance that one might forget to check in the changes back to the source control system. This article focuses on using SSIS to automatically check in the changes that the developers missed for the day into the version control system.

Prerequisites:

You will need the below installations:

  1. AdventureworksLT sample database.
  2. Permissions to the SQL server instance where the AdventureworksLT sample database resides.
  3. VisualSVNServer installed with permissions to access the repository.
  4. TortoiseSVN client on the m/c from which the SSIS package will be run.

The screen shot of the Adventure Works database on my computer is shown below.  I have also installed a VisualSVN server on my machine for demonstration. Usually you will need to setup Visual SVN server on a separate server where you would like to store your source code.

You can open the VisualSVN server manager to check for the Server URL. You can also create or manage users, repositories and logging options. I have created one user as below.

Creating a new Repository:

A new repository needs to be created using the Visual SVN server manager before we can store our scripts. Enter the name of the repository (AdventureWorksLT) and select the check box. It will create subfolders trunk, branches and tags under the AdventureWorksLT folder.

Once you create the repository, you will be able to see the structure like below within the VisualSVN server manager.

If you right click on the AdventureWorksLT repository and click browse you should be seeing the AdventureWorksLT folder in a browser. Once you click on the folder you will see the subfolders as shown in the picture below. Based on how the subversion authentication is setup, you might be asked for the credentials before you see the page. Once the scripts are stored, you will also be able to browse the script file from here.

SSIS package:

Let us first review the SSIS package before actually running it. The SSIS package:

  1. Gets the list of objects that have changed since the last check in date.
  2. Checks out the latest copy of the database scripts from SVN to a working folder.
  3. Creates the folder structure if required.
  4. For each of the objects obtained in step 1, it compares the script from the database to that from the working folder.
  5. Updates the script in the working folder if changes are identified.
  6. Checks in the modified code and deletes the working copy.

The list of variables used in the package is shown below. Please change the values of the SVNRepository, WorkingDirectory, LastAutoCheckInDate based on the environment setup you have. If you don’t have any code within sourcesafe yet or if you are running the package to set up the initial version, you can choose LastAutoCheckInDate such that all the objects for the database are selected.

Execute SQL Task 1 “Objects Modified in database since last scheduled check in”

The query below is used in the Execute SQL Task of the SSIS package. You can choose all the types you need to save the scripts for.

SELECT  o.name AS [name], o.object_id AS [Obj_Id], o.[type] AS  ObjectType, s.name As schemaNm
FROM
   sys.objects o INNER JOIN
   sys.schemas s ON o.schema_id=s.schema_id
WHERE Modify_Date>?
and o.[type] in ('P ','U ','V ','FN','TF')

This task gets the list of all the objects that have been modified after the LastAutoCheckinDate mentioned in the variables. The results are mapped to the variable User::ObjectList

Execute Process Task 2 “svn checkout”

In the Execute Process task, AdventureWorksLT repository is checked out from SVN using command line. The path for the Executable may be different based on your TortoiseSVN installation.

The below expression is used for the Arguments property.

Script Task 3 “Create Folders if does not exist”

In this Script Task, we check if the folder structure exists already. If it does not, you can use the script below with a little customization to create a suitable folder structure:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO

Public Class ScriptMain

    Public Sub Main()

        Dim WorkDirectory As String
        WorkDirectory = Dts.Variables("WorkingDirectory").Value.ToString()
        If (Directory.Exists(WorkDirectory + "\Stored Procedures") = False) Then
            Directory.CreateDirectory(WorkDirectory + "\Stored Procedures")
        End If
        If (Directory.Exists(WorkDirectory + "\Views") = False) Then
            Directory.CreateDirectory(WorkDirectory + "\Views")
        End If
        If (Directory.Exists(WorkDirectory + "\Functions") = False) Then
            Directory.CreateDirectory(WorkDirectory + "\Functions")
        End If
        If (Directory.Exists(WorkDirectory + "\Tables") = False) Then
            Directory.CreateDirectory(WorkDirectory + "\Tables")
        End If
        If (Directory.Exists(WorkDirectory + "\Scripts") = False) Then
            Directory.CreateDirectory(WorkDirectory + "\Scripts")
        End If

        Dts.TaskResult = Dts.Results.Success

    End Sub

End Class

Execute Process Task 4 “SVN add Folders”

In this Execute Process Task, we execute an svn add on each of the folders created in the step above. If you skip this step and checkin your changes, these folders or their contents will not be added in the new revision. I have created a batch file with the below code. This will check for all the folders that we created earlier and add each one using a loop.

@echo off

cd %1

for /f "usebackq tokens=2*" %%i in (`svn status ^| findstr /r "^\?"`) do svn add "%%i %%j"

@echo off

The batch file needs one argument which is shown below:

Foreach loop “For Each modified SQL object”

The foreach loop “For each modified SQL object”, loops through the objects list obtained in Task 1.

Script Task 5 “GetDefinition”

In this script task, using SMO the scripts for the objects of concern are created and stored in the user variable User::ObjectDefinition. This will need some modification based on the objects you need to consider.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
Imports System.Data.OleDb
Imports System.Xml

Public Class ScriptMain

    Public Sub Main()

        Dim SqlServer As Server
        Dim scripter As Scripter
        Dim db As Database
        Dim objScripts As Collections.Specialized.StringCollection
        Dim otherScripts As Collections.Specialized.StringCollection
        Dim scriptOptions As ScriptingOptions
        Dim scripttable As New DataTable("scripttable")

        scripttable.Columns.Add("ScriptColumn", Type.GetType("System.String"))
        Dim objType As String
        objType = Dts.Variables("ObjectType").Value.ToString()
        SqlServer = New Server("XP3TZFFC1")
        SqlServer.ConnectionContext.LoginSecure = True
        SqlServer.ConnectionContext.Connect()
        scripter = New Scripter(SqlServer)
        db = SqlServer.Databases("AdventureWorksLT2008R2")
        scriptOptions = New ScriptingOptions()
        scriptOptions.ScriptDrops = True
        scriptOptions.IncludeIfNotExists = True
        scriptOptions.DriAll = True

        If (objType = "P ") Then
            Dim myobj As StoredProcedure
            myobj = db.StoredProcedures(Dts.Variables("Name").Value.ToString(), Dts.Variables("SchemaNm").Value.ToString())
            objScripts = myobj.Script()
            otherScripts = myobj.Script(scriptOptions)

        ElseIf (objType = "U ") Then
            Dim myobj As Table
            myobj = db.Tables(Dts.Variables("Name").Value.ToString(), Dts.Variables("SchemaNm").Value.ToString())
            objScripts = myobj.Script()
            otherScripts = myobj.Script(scriptOptions)

        ElseIf (objType = "V ") Then
            Dim myobj As View
            myobj = db.Views(Dts.Variables("Name").Value.ToString(), Dts.Variables("SchemaNm").Value.ToString())
            objScripts = myobj.Script()
            otherScripts = myobj.Script(scriptOptions)

        ElseIf (objType = "FN" Or Dts.Variables("ObjectType").Value.ToString() = "TF") Then
            Dim myobj As UserDefinedFunction
            myobj = db.UserDefinedFunctions(Dts.Variables("Name").Value.ToString(), Dts.Variables("SchemaNm").Value.ToString())
            objScripts = myobj.Script()
            otherScripts = myobj.Script(scriptOptions)

        Else

        End If

        For Each script As String In otherScripts

            'MsgBox(script)
            scripttable.Rows.Add(script)
            scripttable.Rows.Add("GO")

        Next script

        For Each script As String In objScripts

            'MsgBox(script)
            scripttable.Rows.Add(script)
            scripttable.Rows.Add("GO")

        Next script

        Dts.Variables("ObjectDefinition").Value = scripttable
        If (SqlServer.ConnectionContext.IsOpen) Then
            SqlServer.ConnectionContext.Disconnect()

        End If

        Dts.TaskResult = Dts.Results.Success

    End Sub

End Class

Script Task 6 “Check If File Exists”

In the Script Task “Check If File Exists”, based on whether the object is stored in SVN already, the user variable User::FileExists is set to either True or False.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Public Class ScriptMain     

      Public Sub Main()     

        Dim fileName As String
        Dim folder As String
        If (Dts.Variables("ObjectType").Value.ToString() = "P ") Then
            folder = "Stored Procedures"

        ElseIf (Dts.Variables("ObjectType").Value.ToString() = "U ") Then
            folder = "Tables"

        ElseIf (Dts.Variables("ObjectType").Value.ToString() = "V ") Then
            folder = "Views"

        ElseIf (Dts.Variables("ObjectType").Value.ToString() = "FN" Or Dts.Variables("ObjectType").Value.ToString() = "TF") Then
            folder = "Functions"

        Else
            folder = "Scripts"

        End If

        fileName = Dts.Variables("WorkingDirectory").Value.ToString() + "\" + folder + "\" + Dts.Variables("SchemaNm").Value.ToString + "." + Dts.Variables("Name").Value.ToString + ".sql"
        Dts.Variables("FilePath").Value = fileName
        Dts.Variables("FileExists").Value = File.Exists(fileName)
        Dts.TaskResult = Dts.Results.Success

    End Sub

End Class

Data Flow Task 7 “Compare Scripts”

Add a precedence constraint from Task 6 to the next Data Flow Task 7 “Compare Scripts”.

This data flow task reads the file from the working directory and compares with the object definition obtained from the database. It compares the code line by line using a Merge Join. You can even design a better way of comparing line by line by writing a script transformation to perform the comparision where you can ignore empty lines in the code or in the file.

The script below is used in the step “Read Script From ObjectDefinition” to read the definition from the database.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.data.OleDb
'Imports System.Xml

Imports System.Text

Public Class ScriptMain

    Inherits UserComponent

    Public Overrides Sub CreateNewOutputRows()

        Dim oleDA As New OleDbDataAdapter
        Dim dt As New DataTable
        Dim col As DataColumn
        Dim row As DataRow
        Dim sMsg() As String
        Dim arg() As String = {vbCrLf, vbLf}
        dt = CType(Me.Variables.ObjectDefinition, DataTable)
        Dim RowNumber As Integer = 1

        For Each row In dt.Rows
            For Each col In dt.Columns
                sMsg = row(col.Ordinal).ToString().Split(arg, StringSplitOptions.None)
                For i As Integer = 0 To sMsg.GetUpperBound(0)
                    MyOutputBuffer.AddRow()
                    MyOutputBuffer.MyCol = sMsg(i)
                    MyOutputBuffer.MyRowNumber = RowNumber
                    RowNumber = RowNumber + 1

                Next
            Next
        Next
    End Sub
End Class

The script below is used in Add Row Numbers script component and it adds the row number to the output from the file.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
    Inherits UserComponent
    Private RowNumber As Integer = 1
    Public Overrides Sub MyInput_ProcessInputRow(ByVal Row As MyInputBuffer)
        Row.RowNumber1 = RowNumber
        RowNumber = RowNumber + 1
    End Sub 
End Class

Data is sorted on both the columns on the inputs before the Merge Join.

The Merge join tranformation and Conditional split transformation are used to check if rows from either source do not match the rows from the second one. The variables User::case1 and User::case2 are set using a rowcount transformation used after the Conditional split transform. See the below 3 screenshots.

Data Flow Task 8 “Update script”

If either of the conditions, ”Script wasn’t in the working folder” or “Script was different from the database script” is met, Data Flow Task “Update script” runs

The condition in the above screen shot is @[User::case1] != 0 || @[User::case2] !=0

In this data flow task, same script component that is used in the earlier dataflow is used to read the script from the database. The only part that is different within this script component is that there is no RowNumber output column. This Data Flow task overwrites the script file in the Working folder

The screen shot below shows the destination connection manager used. The connection string property is mapped to (User::FilePath) using SSIS expressions

Execute Process Task 9 “SVN add”

If the script file didn’t exist before, we will need to perform an svn add once the file is in the working directory, to make sure it is checked in within the last step.

The expression shown below is used for the arguments.

Execute Process Task 10 “SVN chekin”

In this task, the changes the checked in to the SVN server.

File System Task 11 “Delete Working Folder”

The working folder is deleted if everything is successful.

You can browse through each of the folders to see that your code is checked in.

Conclusion:

Schedule the package to run on a daily basis by storing the last successful extecution date and using this date for the LastAutoCheckInDate ssis variable.

There are few other ways you could implement the same process based on what tools are available to you and also what programming language you prefer working in. If you have Redgate SQL Compare tool, you can replace the “For Each Modified SQL Object” with Execute Process tasks that run the required SQL Compare command line instructions. You will not need to loop through each object at that point. You can send the list of names in xml file as input to the command line. You could also use a console application to build the same process. But whatever be the method, I think it will be worth the while as it makes it easier to version control your database code. Please make sure you test these packages before you use them. This package has been developed using Visual Studio 2005. You will need to customize them to suit your environment

References:

Total article views: 159 | Views in the last 30 days: 5
 
Related Articles
FORUM

dts.variables in vb-script task

dts.variables in vb-script task

FORUM

How can I SSIS Concatenate DTS.Variables in Script Task or anywhere?

This is invalid: 'Dts.Variables("gsFileName").Value = Dts.Variables("gsFilePath").Value & Dts.Variab...

FORUM

Script Task to import Excel 12.0 file

I am using SSIS Script Taskto import and excel 12.0 file

FORUM

Using Script Task to check if Folder Exists

Using Script Task to check if Folder Exists

FORUM

Variable not in script task

Package level variable not in dts.variables in script

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones