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:
- AdventureworksLT sample database.
- Permissions to the SQL server instance where the AdventureworksLT sample database resides.
- VisualSVNServer installed with permissions to access the repository.
- 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:
- Gets the list of objects that have changed since the last check in date.
- Checks out the latest copy of the database scripts from SVN to a working folder.
- Creates the folder structure if required.
- For each of the objects obtained in step 1, it compares the script from the database to that from the working folder.
- Updates the script in the working folder if changes are identified.
- 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 ClassExecute 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 ClassScript 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 ClassData 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 ClassThe 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 ClassData 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
