Really portable DTS packages - Pt 2

Following on from my previous post, Really portable DTS packages, this is where we start to get into the inner workings of the template package.

First let me briefly summarise the approach that is taken.

To make things as dynamic as possible, and require a fairly low level of technical expertise for any migration, I the store most of the global variables required by a package in a database table. The location and security of this table I'll leave to your own implementation, but I favour having a secure central storage location that the other servers read from.

Each package also contains a static set of seven global variables that are used in retrieving the global variables from the table. These variables are used for items like a server name, database name, userid, password and a flag to indicate a trusted connection. The values for these global variables are set by passing them in on the command line using the /A parameter.

The first step in the package uses the passed details to setup a specific connection in the package to connect to the specified database. I could have used another Dynamic Properties task here, but I wanted to incorporate the functionality of prompting the user if the package is run without passing in the parameters. The next step executes an SQL statement to retrieve all the global variables necessary for package execution using the Package GUID. the result of the SQL statement is stored in a global variable as a Rowset.

The third step processes the Rowset object and dynamically creates all the global variables. The fourth step is simply a Dynamic Properties task that is used to dynamically assign all the necessary properties like file destinations, server and database names. The last step in the template package is executed at the end of the workflow and clears all the connection setting and variables that were created at runtime. This isn't really necessary as the package doesn't save those details between executions anyway, but it kept the security and compliance guys happy.

As we're passing in the server name at the start, there's no need to worrying about instances, changing server names and the like. When the package is ported to a different environment, the only changes you need to make are in the table. No DTS editing involved.

The structure of the table I'm using to store the global variables looks like this,

CREATE TABLE [dbo].[tblDTSPkgVariables] (
 [RecID] [int] IDENTITY (1, 1) NOT NULL , -- allows easy record management in web front-end
 [PkgName] [varchar] (128) NOT NULL , -- much easier to read in front-end
 [PkgID] [uniqueidentifier] NOT NULL , -- the key to all the variables
 [GV_Name] [varchar] (30) NOT NULL , -- duh, the name of course
 [GV_Type] [varchar] (30) NULL , -- data type of the variable
 [GV_Value] [varchar] (1000) NULL , -- the actual value to assign
 CONSTRAINT [PK_tblDTSPkgVariables] PRIMARY KEY CLUSTERED
 (
  [RecID]
 )
)

Here's the script behind the first ActiveXScript task. Now I'm no gun programmer, so don't look at the methods/syntax I use as best practices. If you've got suggestions of how my script could be better, let me know.

Option Explicit
Function Main() Dim oConn ' DTS connection Object
 Dim iErrNum ' error number
 Dim sErrDesc ' error description
 Dim sPkgName

 ' capture the package ID and name
 DTSGlobalVariables("CON_PKGID").Value = DTSGlobalVariables.Parent.PackageID
 sPkgName = DTSGlobalVariables.Parent.Name

 If UCase(DTSGlobalVariables("CON_SERVER").Value) = "EMPTY" Then
  iErrNum = GetSetting(sPkgName)
 End If

 If iErrNum = 0 Then  ' open object referring to VariableStorage connection
  Set oConn = DTSGlobalVariables.Parent.Connections("GV_Store")

  On Error Resume Next

  oConn.Datasource = DTSGlobalVariables("CON_SERVER").Value
  oConn.Catalog = DTSGlobalVariables("CON_DATABASE").Value
  oConn.UseTrustedConnection = CBool(DTSGlobalVariables("CON_TRUSTED").Value)
  If CBool(DTSGlobalVariables("CON_TRUSTED").Value) = True Then
   oConn.UserID = ""
   oConn.Password = ""
  Else
   oConn.UserID = DTSGlobalVariables("CON_USERID").Value
   If IsNull(DTSGlobalVariables("CON_PASSWORD").Value) or (DTSGlobalVariables("CON_PASSWORD").Value = "") Then
    oConn.Password = ""
   Else
    oConn.Password = DTSGlobalVariables("CON_PASSWORD").Value
   End If
  End if

  iErrNum = Err.number
  sErrDesc = Err.Description
  On Error Goto 0  Set oConn = Nothing
 End If If iErrNum = 0 Then
  ' signal success
  Main = DTSTaskExecResult_Success Else
  ' log failure details
  On Error Resume Next
  DTSPackageLog.WriteTaskRecord iErrNum, sErrDesc
  On Error Goto 0  ' signal failure
  Main = DTSTaskExecResult_Failure End If
End Function

Function GetSetting(sPkgName) Dim sMsg ' message string
 Dim sTitle ' message box title
 Dim sInput ' input value
 Dim iErrNum iErrNum = 0 sTitle = sPkgName & " - Server Name"
 sMsg = "Enter name of server holding Global Variable List, or CANCEL to exit."
 sInput = GetString(sMsg, sTitle, DTSGlobalVariables("CON_SERVER").Value)
 If UCase(sInput) <> "CANCEL" Then
  DTSGlobalVariables("CON_SERVER").Value = sInput
 Else
  DTSGlobalVariables("CON_SERVER").Value = "Empty"
  iErrNum = vbObjectError + 1
 End If
 If iErrNum = 0 Then
  sTitle = sPkgName & " - Database Name"
  sMsg = "Enter name of Database holding Global Variable List, or CANCEL to exit."
  sInput = GetString(sMsg, sTitle, DTSGlobalVariables("CON_DATABASE").Value)
  If UCase(sInput) <> "CANCEL" Then
   DTSGlobalVariables("CON_DATABASE").Value = sInput
  Else
   DTSGlobalVariables("CON_DATABASE").Value = "Empty"
   iErrNum = vbObjectError + 1
  End If
 End If
 If iErrNum = 0 Then
  sTitle = sPkgName & " - Security Mode"
  sMsg = "Enter security mode, 1 = Windows Trusted, 0 = SQL Security, or CANCEL to exit."
  sInput = GetInt(sMsg, sTitle,DTSGlobalVariables("CON_TRUSTED").Value)
  If CInt(sInput) = 1 Then
   ' using Windows security so set Trusted to True
   DTSGlobalVariables("CON_TRUSTED").Value = "True"
  ElseIf CInt(sInput) = 0 Then
   ' using SQL security so set Trusted to False and prompt for userid and password
   DTSGlobalVariables("CON_TRUSTED").Value = "False"
 
   sTitle = sPkgName & " - Username"
   sMsg = "Enter Username, or CANCEL to exit."
   sInput = GetString(sMsg, sTitle, DTSGlobalVariables("CON_USERID").Value)
   If UCase(sInput) <> "CANCEL" Then
    DTSGlobalVariables("CON_USERID").Value = sInput
   Else
    DTSGlobalVariables("CON_USERID").Value = "Empty"
    iErrNum = vbObjectError + 1
   End If
 
   sTitle = sPkgName & " - Password"
   sMsg = "Enter Password, or CANCEL to exit."
   sInput = GetString(sMsg, sTitle, DTSGlobalVariables("CON_PASSWORD").Value)
   If UCase(sInput) <> "CANCEL" Then
    DTSGlobalVariables("CON_PASSWORD").Value = sInput
   Else
    DTSGlobalVariables("CON_PASSWORD").Value = "Empty"
    iErrNum = vbObjectError + 1
   End If
  Else
   DTSGlobalVariables("CON_TRUSTED").Value = "Empty"
   iErrNum = vbObjectError + 1
  End If
 End If
 GetSetting = iErrNum
End Function

Function GetString(sMsg, sTitle, sValue) Dim sInput
 Dim sErrMsg

 sInput = ""
 sErrMsg = "" sInput = InputBox(sMsg, sTitle, sValue)
 While (IsNull(sInput) Or IsEmpty(sInput) Or IsNumeric(sInput) Or (UCase(sInput) = "EMPTY"))
  sErrMsg = "Previous entry is Invalid !!! Please try again" & vbCrLf & vbCrLf & sMsg
  sInput = InputBox(sErrMsg, sTitle, sValue)
 Wend
 GetString = sInput
End Function

Function GetInt(sMsg, sTitle, sValue) Dim sInput
 Dim sErrMsg

 sInput = ""
 sErrMsg = "" sInput = InputBox(sMsg, sTitle, sValue)
 If (IsNumeric(sInput) = False) Then sInput = 2
 If (UCase(sInput) = "CANCEL") Then sInput = -1
 While ((CInt(sInput) < -1) Or (CInt(sInput) > 1))
  sErrMsg = "Previous entry is Invalid !!! Please try again" & vbCrLf & vbCrLf & sMsg
  sInput = InputBox(sMsg, sTitle, sValue)
  If (IsNumeric(sInput) = False) Then sInput = 2
  If (UCase(sInput) = "CANCEL") Then sInput = -1
 Wend
 GetInt = sInput
End Function

Really portable DTS packages

Ok, I've been asked many a time how I setup DTS pacakges to make them portable. There's been many solutions thrown around the internet, but personally, I don't think any of them making DTS packages really portable. This fine article by Shane Dovers gives a few guidelines, but doesn't show you how it all hangs together. And this article by Kevin Feit sort of goes half way but leaves a lot to be desired in a real world scenario.

My solution to the problem of easily moving DTS packages from environment to environment is based mainly on this Microsoft Best Practices document. Don't be frightened off by the fact that the title says "for Business Intelligence Solutions", even if you are simply loading data into SQL Server, there are a number of DTS gems in this document.

I'm not going to bore you all to death with the details in one huge long post ( I'd have to think up another topic then wouldn't I ) for now. Let me me present the layout of the template package used when creating new packages. Over the next few posts I'll go through the tasks and scripts in the package.