What are SSIS packages deployment and change-tracking best practices ?

Question

I've got quite a robust development environment regarding my SQL database Schema and Data (everything is source controlled, deployment is automated, etc), but when it comes to SSIS packages, the process is quite tedious and totally manual.

• How can I automate the SSIS package upgrade process (right now, I copy my packages manually to each server, and then I use Management Studio's "SSIS Package Upgrade Wizard")

• How can I deploy my Schedules (right now, I'm doing it manually)

• How can I deploy my SQL Agent Jobs (right now, I'm doing it manually)

• How can I get my Schedules and Jobs int my TeamSystem repository (right now, I use no source-control for this)

• Are there other things I can/should do to enhance the way I work regarding SSIS packages ?

I'm using MS SQL Server 2008 SP1, but I imagine some advices could be useful on other versions as well.

Answer 1

Brann,

For point 1 see this site http://jessicammoss.blogspot.com/2008/05/ssis-configuration-to-configuration-to.html

I had the same question a while back and this site helped a lot. I am curious about the other points as well as my team deploys them manually like you but I thought I'd share what I knew.

Thanks.

Answer 2

1. I don't know if upgrading the package can be automated at all.
2. To deploy SSIS packages create a Manifest file
3. You can write SQL scripts to create SQL schedules and jobs and then call this script using some vb.net script or something.
4. To add SQL scripts to TFS, open SQL Server Management Studio > File > New > Project > SQL Server Scripts. Add Scripts to the Queries folder and then later you can add this solution to the TFS. I have never tried this. Instead I copy the script files to TFS manually.

In my opinion there is no point in creating a full fledged deployment utility for deploying SSIS packages as this is a very rare activity. Deployment/installation should be simple when we distribute a software product to non-tech users so that it is user friendly. But an SSIS package will be always handled by a DBA or sort of person who already knows how to use it...

Answer 3

Well what we do is save everything in source control. Our SSIS packages are saved as DTSX files and those are put into our source control. We use the source control versions of everything to promote to QA and Prod. We do all our configuration for SSIS packges using configuration tables in a database we have set up to manage meta data for SSIS packages. This generally consists of all our connection strings as well as some variables we might want to set at runtime. We also have all our logging set up in this database as well. One of the beauties of this is it becomes far easier to upload the packages that are being changed because the configuration tables do not usually need to be changed and all that is needed is to load the revised dtsx file to the location it will run from. And even if we need to add a new refernce to the configuration table, this is an insert into a table and easily scripted for the upgrade process.

You can also use configuration xml files to change your configurations easily (these can also be stored in source control), but we prefer having everything in the ETL database where we store the meta data.

Answer 4

On point 1 (automating deployment):

It's possible to deploy SSIS packages from the command line using dtutil.exe.

The following example would deploy the local file c:\test.dtsx to MSDB storage on server1 with the name test_package:

dtutil.exe /FILE C:\test.dtsx /dests server1 /COPY SQL;test_package

It will do any combination of import/export for all the various SSIS storage options, if you can work out the combination of parameters to use.

This may not be relevant to you, but what this won't do (that the wizard will) is copy configuration files; you'd need a separate batch step for that.

On points 2 and 3 (source control of SQL Agent):

As others have said, SQL Agent Jobs and schedules can be scripted as T-SQL (in Management Studio, right-click the Job and select Script job to...), held in source control, and deployed like any other T-SQL script.

Our DB source control tree is set up with one folder per production server containing one subfolder per user database plus an extra subfolder called _config which contains scripts for server-level objects such as logins, linked servers and SQL Agent Jobs.

(It's not really relevant to your question but we store SSIS packages in their own tree, since they can be applied to multiple servers)

We don't use TFS, so I can't comment on the specifics of getting the scripts into it.

Answer 5

Regarding scripting out objects, you can do it via DMO, SMO, PowerShell, VBScript, and any other number of methods. I still use my ancient VB DMO script that I wrote that was based on code from several websites and execute the code via a SSIS script task. Setup a SQL Server agent job that executes the package and add a step that checks-in the code. I'm not familiar with TFS, but I've done this with both Visual SourceSafe and Perforce without any problems. The trick is that you just need to learn how to do an unattended command-line check-in that merges changes (updates, deletes, creates). Below is my ancient VB.NET script task. Keep in mind that you can replace portions of this with variables to make it more dynamic.

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.

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


<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
Partial Class ScriptMain

Private Sub ScriptMain_Startup(ByVal sender As Object, ByVal e As System.EventArgs)

End Sub

Private Sub ScriptMain_Shutdown(ByVal sender As Object, ByVal e As System.EventArgs)
    Try
        ' Unlock variables from the read-only and read-write variable collection properties
        If (Dts.Variables.Count <> 0) Then
            Dts.Variables.Unlock()
        End If
    Catch ex As Exception
    End Try
End Sub

Enum ScriptResults
    Success = DTSExecResult.Success
    Failure = DTSExecResult.Failure
End Enum
Private Function CleanFileName(ByVal FileName As String) As String


    FileName = Regex.Replace(FileName, "[/\\:?=*<>]", "-")
    Return FileName

End Function







Public Sub Main()

    ''THESE MUST BE SET AND THE AGENT ACCOUNT MUST HAVE WRITE ACCESS TO THE DRIVE SELECTED
    Dim FolderName = "\\Your\Folder\Hierarchy\"
    Dim ServerName = "your-server-name"

    Dim ScriptOptions As Integer
    ScriptOptions = 1 ' Generate Drop
    ScriptOptions = ScriptOptions Or 4 ' Generate Default (Create)
    ScriptOptions = ScriptOptions Or 34 ' Generate Permissions (Database & Object)

    Dim ScriptOptions_Jobs As Integer
    ScriptOptions_Jobs = 1 ' Generate Drop
    ScriptOptions_Jobs = ScriptOptions_Jobs Or 1203765415 ' Other stuff


    Dim ScriptOptions_Tables As Integer
    ScriptOptions_Tables = 1 ' Generate Drop
    ScriptOptions_Tables = ScriptOptions_Tables Or 4 ' Generate Default (Create)
    ScriptOptions_Tables = ScriptOptions_Tables Or 34 ' Generate Permissions (Database & Object)
    ScriptOptions_Tables = ScriptOptions_Tables Or 73736 ' Generate Indexes

    Dim EXCLUDE_LIST = "master/tempdb/model/msdb"

    ''Standardize the end of the folder name to include \.
    If Right(FolderName, 1) <> "\" Then
        FolderName = FolderName & "\"
    End If

    ''Declare a folder that can be deleted.  Delete doesn't like ending with \.
    Dim FolderNameDelete = FolderName
    If Right(FolderNameDelete, 1) = "\" Then FolderNameDelete = _
        Left(FolderNameDelete, Len(FolderNameDelete) - 1)

    'Used for file system tasks.
    Dim oFSO = CreateObject("Scripting.FileSystemObject")

    'Delete if the folder exists.  If you don't delete the folder and populate the scripts,
    'then objects that were deleted from the database won't disappear and the Perforce diff
    'won't recognize that the objects need to be deleted in Perforce.
    If oFSO.FolderExists(FolderName) = True Then
        oFSO.DeleteFolder(FolderNameDelete, True)
    End If

    'Create the folder now so that we have a clean script destination.
    oFSO.CreateFolder(FolderName)
    oFSO.CreateFolder(FolderName & "Databases")
    oFSO.CreateFolder(FolderName & "Jobs")

    'connect to the server instance using trusted credentials so we dont have password stored in a file and we dont have
    'to worry about password changes breaking anything
    Dim oServer = CreateObject("SQLDMO.SQLServer")
    With oServer
        .LoginSecure = True
        .Connect(ServerName)
    End With

    'Script out SQL Server Agent Jobs.
    For Each oJob In oServer.JobServer.Jobs
        oJob.Script(ScriptOptions_Jobs, FolderName & "Jobs\" & CleanFileName(oJob.Name) & ".sql")
    Next


    'loop through all databases and excluding those in the EXCLUDE_LIST above, script out all the stored procedures.
    'You could easily change this to read from a table or to let you pass in a different list.
    For Each oDB In oServer.Databases

        If InStr(1, EXCLUDE_LIST, oDB.Name) = 0 Then

            'each db will get a folder in the main folder (FolderName) that will act as a container for the backup
            'folders we'll create each time we run this
            Dim dbFolder = FolderName & "Databases\" & oDB.Name & "\"

            oFSO.CreateFolder(dbFolder)
            oFSO.CreateFolder(dbFolder & "Stored Procedures")
            oFSO.CreateFolder(dbFolder & "Tables")
            oFSO.CreateFolder(dbFolder & "User-Defined Functions")
            oFSO.CreateFolder(dbFolder & "Views")

            'script out all the non-system procs
            For Each oProc In oDB.StoredProcedures
                If oProc.SystemObject = False Then
                    ''262150= SQLDMOScript_ObjectPermissions + SQLDMOScript_PrimaryObject + SQLDMOScript_OwnerQualify
                    oProc.Script(ScriptOptions, dbFolder & "\Stored Procedures\" & CleanFileName(oProc.Owner) & "_" & CleanFileName(oProc.Name) & ".sql")
                End If
            Next

            'script out all the non-system views
            For Each oView In oDB.Views
                If oView.SystemObject = False Then
                    ''262150= SQLDMOScript_ObjectPermissions + SQLDMOScript_PrimaryObject + SQLDMOScript_OwnerQualify
                    oView.Script(ScriptOptions, dbFolder & "\Views\" & CleanFileName(oView.Owner) & "_" & CleanFileName(oView.Name) & ".sql")
                End If
            Next

            'script out all the non-system user-defined functions
            For Each oUDF In oDB.UserDefinedFunctions
                If oUDF.SystemObject = False Then
                    ''262150= SQLDMOScript_ObjectPermissions + SQLDMOScript_PrimaryObject + SQLDMOScript_OwnerQualify
                    oUDF.Script(ScriptOptions, dbFolder & "\User-Defined Functions\" & CleanFileName(oUDF.Owner) & "." & CleanFileName(oUDF.Name) & ".sql")
                End If
            Next

            'script out all the non-system tables
            For Each oTable In oDB.Tables
                If oTable.SystemObject = False Then
                    ''262150= SQLDMOScript_ObjectPermissions + SQLDMOScript_PrimaryObject + SQLDMOScript_OwnerQualify
                    oTable.Script(ScriptOptions_Tables, dbFolder & "\Tables\" & CleanFileName(oTable.Owner) & "_" & CleanFileName(oTable.Name) & ".sql")
                End If
            Next


        End If
    Next

    'close it all out
    oServer.DisConnect()
    oServer = Nothing
    oFSO = Nothing

    Dts.TaskResult = ScriptResults.Success
End Sub

End Class