Automated DB Source Control (2 of 5): The SSIS Package

Josh,

To continue from the last post, you'll need an SSIS package to drive the process.  This package joins all the steps, running the custom code, the SQL Scripts, and Checking In the changes.  Here's what you came up with...

Parameters

In the SSIS Package there are 6 parameters:

• DatabasesToSourceControl: this parameter contains a semi-colon separated list of databases that will be source controlled.  The pattern is [Server Name].[Database Name];
• LocalSourceCodePath: This is the path where the local workspace will be maintained/setup
• TfExePath: The path to the local TF.exe
• TfsPassword: The password setup for the alternate credentials in Part 1 of 5
• TfsUrl: The URL for the TFS server
• TfsUser: The username setup for the alternate credentials in Part 1 of 5

Variables

In the SSIS Package there are 5 variables:

• CombinedComments: This variable contains the comments created for each database and is passed in to the Check In task
• Comment: This variable is used within the For Each Loop to hold the current database's comments until it is combined into the CombinedComments variable
• CurrentDb: This variable is used within the For Each Loop
• CurrentServer: This variable is used within the For Each Loop
• DatabaseList: This variable is set from the Generate SQL Scripts script task and is used in the For Each Loop

Connection Managers

Expression for ConnectionString: "Data Source=" + @[User::CurrentServer]  + ";Initial Catalog=" + @[User::CurrentDb]  + ";Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;"

SSIS Tasks

The Script Tasks that execute the Custom Client code are generally in the form shown below:

public void Main()
{
 string localSourceCodepath = Dts.Variables["$Package::LocalSourceCodePath"].Value.ToString();
 string tfsUrl = Dts.Variables["$Package::TfsUrl"].Value.ToString();
 string tfsUser = Dts.Variables["$Package::TfsUser"].Value.ToString();
 string tfsPassword = Dts.Variables["$Package::TfsPassword"].Value.ToString();
 
 try
 {
  VisualStudioOnlineClient client = new VisualStudioOnlineClient(tfsUrl, localSourceCodepath, tfsUser, tfsPassword);
  client.ValidateWorkspace();
 }
 catch (Exception e)
 {
  Dts.Events.FireError(-1, null, e.ToString(), null, -1);
  Dts.TaskResult = (int)ScriptResults.Failure;
 }
 

 Dts.TaskResult = (int)ScriptResults.Success;
}

They take the four required parameters for the constructor and the execution is wrapped in a try catch so any errors will be logged by SSIS.

Verify Workspace Script Task

Executes custom library method "ValidateWorkspace"
Custom code is elaborated in Part 3.
Read only variables: $Package::LocalSourceCodePath, $Package::TfsPassword, $Package::TfsUrl, $Package::TfsUser

Generate SQL Scripts Script Task

Creates the script files of the database objects, also sets the "DatabaseList" object used in the For Each Loop. See Part 4 for code and explanation.

Read only variables: $Package::DatabasesToSourceControl,$Package::LocalSourceCodePath

Read/write varables: User::DatabaseList

Add Items to TFS

Executes custom library method "AddItemsToSourceControl". Custom code is elaborated in Part 3.
Read only variables: $Package::LocalSourceCodePath, $Package::TfsPassword, $Package::TfsUrl, $Package::TfsUser

Loop Through Databases For Each Loop

Loops through list of databases

Get Change Log Execute SQL Task

Executes SQL statement to get recent changes in DB

DECLARE @Comment VARCHAR(8000),
        @DbName  VARCHAR(500) = ?; 

DECLARE @ChangeLog TABLE
  (
     servername   VARCHAR(500),
     databasename VARCHAR(500),
     loginname    VARCHAR(500),
     objectname   VARCHAR(500) NULL
  )

INSERT INTO @ChangeLog
SELECT servername,
       databasename,
       loginname,
       '[' + A.subclass_name + '] ' + objectname AS ObjectName
FROM   sys.traces t
       CROSS apply Fn_trace_gettable(Reverse(Substring(Reverse(t.path),
                                     Charindex('\',
Reverse(t.path)),
260))
+ N'log.trc', DEFAULT) f
LEFT JOIN (SELECT DISTINCT TSV.subclass_value,
TSV.subclass_name
FROM   sys.trace_subclass_values TSV
INNER JOIN sys.trace_columns TC
ON TC.NAME = 'objecttype'
AND TC.trace_column_id = TSV.trace_column_id) A
ON f.objecttype = A.subclass_value
WHERE  eventclass IN ( 46, 47, 164 )
       AND eventsubclass = 0
       AND databaseid <> 2
       AND objecttype NOT IN ( 21587 )
       AND loginname NOT IN (
               'Some Specific Names',
               'NT AUTHORITY\SYSTEM' )
       AND starttime >= Dateadd(hour, -24, Getdate())
       AND databasename = @DbName
GROUP  BY servername,
          databasename,
          loginname,
          objectname,
          subclass_name
ORDER  BY Max(starttime) DESC

SELECT @Comment = COALESCE(@Comment + ', ', '') + '{'
                  + CL.servername + '.' + CL.databasename + ' '
                  + CL.loginname + ': '
                  + Isnull(Stuff((SELECT '; ' + CL2.objectname FROM @ChangeLog
                  CL2
                         WHERE CL.servername = CL2.servername AND
                  CL.databasename =
                         CL2.databasename AND CL.loginname = CL2.loginname FOR
                  xml path
                         ('')), 1, 1,
                         ''), '') + '}'
FROM   @ChangeLog CL
GROUP  BY CL.servername,
          CL.databasename,
          CL.loginname

SELECT @Comment AS [Comments] 

Format Comment Script Task

Combines the comments from all databases and formats them to be used as the comment when performing the checkin.
Read only variables: User::Comment,User::CurrentServer

Read/write variables: User::CombinedComments

public void Main()
{
 string comment = Dts.Variables["User::Comment"].Value.ToString();
 string currentServer = Dts.Variables["User::CurrentServer"].Value.ToString();
 string combinedComments = Dts.Variables["User::CombinedComments"].Value.ToString();

 try
 {
  if (!String.IsNullOrWhiteSpace(comment))
  {
   if (String.IsNullOrWhiteSpace(combinedComments))
    combinedComments = comment;
   else
    combinedComments += ", " + comment;

   Dts.Variables["User::CombinedComments"].Value = combinedComments;

   Dts.TaskResult = (int)ScriptResults.Success;
  }
 }
 catch (Exception e)
 {
  Dts.Events.FireError(-1, null, e.ToString(), null, -1);
  Dts.TaskResult = (int)ScriptResults.Failure;
 }
 
}

Check In Items Script Task

Executes custom library method "CheckIn". Custom code elaborated in Part 3.

Read only variables: User::CombinedComments, User::Comment, User::CurrentServer, $Package::LocalSourceCodePath, $Package::TfsPassword, $Package::TfsUrl, $Package::TfsUser

public void Main()
{
 string localSourceCodepath = Dts.Variables["$Package::LocalSourceCodePath"].Value.ToString();
 string tfsUrl = Dts.Variables["$Package::TfsUrl"].Value.ToString();
 string tfsUser = Dts.Variables["$Package::TfsUser"].Value.ToString();
 string tfsPassword = Dts.Variables["$Package::TfsPassword"].Value.ToString();
 string comment = Dts.Variables["User::CombinedComments"].Value.ToString();
 string currentServer = Dts.Variables["User::CurrentServer"].Value.ToString();
 
 try
 {
  VisualStudioOnlineClient client = new VisualStudioOnlineClient(tfsUrl, localSourceCodepath, tfsUser, tfsPassword);
  int pendingChangeCount = client.CheckIn(localSourceCodepath + "\\" + currentServer, comment);

  bool fireAgain = false;

  Dts.Events.FireInformation(-1, null, String.Format("There were {0} pending changes found.", pendingChangeCount), null, -1, ref fireAgain);
 }
 catch (Exception e)
 {
  Dts.Events.FireError(-1, null, e.ToString(), null, -1);
  Dts.TaskResult = (int)ScriptResults.Failure;
 }
 

 Dts.TaskResult = (int)ScriptResults.Success;
}