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 FunctionFunction 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
