Global Variables and Variable INI file
-
Attempting to have a DTS package that set the Global Variables from an INI file where the INI file location is an environmental variable but the global variables are not being set from the ini file.
Can anyone advise what is wrong ? If you wnat the actual *.dts, send me a private message and I can respond.
Execution of the package
DTSRUN.exe /SUSSCSCOPENSYS03 /E /N"Databases Missing Backups - 2000" /ASourceServer:8=COMMLSQL1
Global Variables with comments
SourceServer is the SQL Server instance to be checked for missing backups and the value is passed to the DTS package as a parameter.
TargetServer is the SQL Server instance where the missing backup information is to be retained.
TargetDatabase is the SQL Server database where the missing backup information is to be retained.
INI File:
[Parameters]
; SQLServer Instance Names
SQLServer_DBInventory = USASCHISSQL2-4\SQL2000PRD1
; SQL Server Database Names
DatabaseName_DBInventory = DBInventory
Transform data targer table definition in the TargetServer and TargetDatabase:
CREATE TABLE dbo.InstanceDatabases_MissingBackups (
MachineName nvarchar (128) NOT NULL ,
InstanceName nvarchar (128) NOT NULL ,
DatabaseName nvarchar (128) NOT NULL ,
AsOfTs datetime NOT NULL CONSTRAINT InstanceDatabases_MissingBackups_D_AsOfTs DEFAULT (getdate()),
CONSTRAINT InstanceDatabases_MissingBackups_P PRIMARY KEY CLUSTERED
(MachineName,InstanceName,DatabaseName)
)
SQL for the transform data task that determine the databases that do not have backups.
SELECT CAST( SERVERPROPERTY ('MachineName') as nvarchar(128) ) as MachineName
, COALESCE ( CAST( SERVERPROPERTY ('InstanceName') as nvarchar(128) ) , 'Default') AS InstanceName
, sysdatabases.name
from master.dbo.sysdatabases AS sysdatabases
WHERE sysdatabases.name not in ('tempdb','northwind','pubs')
-- Ignore databases were created since 6AM of the previous day
and sysdatabases.crdate <
CAST ( CAST ( current_timestamp - .5 as integer) as datetime ) - 1 + ( 1.0 / 4 )
AND NOT EXISTS
(SELECT 1
FROM msdb.dbo.backupset AS backupset
WHERE backupset.database_name = sysdatabases.name
-- database backups only - ignore transaction log backups
and backupset.type = 'D'
-- 6 AM the previous day
and backupset.backup_start_date
>= CAST ( CAST ( current_timestamp - .5 as integer) as datetime ) - 1 + ( 1.0 / 4 )
VBScript to get the DTS Ini file name and change the ini file location used by the Dynamic Properties Task
Function Main()
Dim shell, env
Set shell = CreateObject("WScript.Shell")
Set env = shell.Environment("process")
iniFileName = env("DTSINI")
' MsgBox iniFileName
Set oPkg = DTSGlobalVariables.Parent
Set oDynProps = oPkg.Tasks( "DTSTask_DTSDynamicPropertiesTask_1" ).CustomTask
for each oAssign in oDynProps.assignments
if oAssign.sourceType = DTSDynamicPropertiesSourceType_IniFile then
oAssign.SourceIniFileFileName = iniFileName
end if
next
Main = DTSTaskExecResult_Success
End Function
VBScript to show the global variables:
Option Explicit
Const Log_MsgBox = True
Const Log_EventLog = True
Const Log_TextFile = True
Const Log_TextFile_Name = "C:\Temp\GlobalVariableDiagnostics.txt"
Const Log_TextFile_Mode = 2 ' ForWriting = 2 or ForAppending = 8
Dim m_sBuffer
Function Main()
Dim oPkg
Dim oWshNetwork
Dim oGlobalVariable
Dim sName
Dim sTypeName
Dim sValue
Dim iIndex
iIndex = 1
Set oPkg = DTSGlobalVariables.Parent
Set oWshNetwork = CreateObject("WScript.Network")
m_sBuffer = m_sBuffer & "Package Information" & vbCrLf & _
"Package Name: " & vbTab & oPkg.Name & vbCrLf & _
"Log Date Time: " & vbTab & Now() & vbCrLf & _
"Current Computer Name: " & vbTab & oWshNetwork.ComputerName & vbCrLf & _
vbCrLf & _
"Current User Domain: " & vbTab & oWshNetwork.UserDomain & vbCrLf & _
"Current User Name: " & vbTab & oWshNetwork.UserName & vbCrLf & _
vbCrLf
m_sBuffer = m_sBuffer & "Global Variables: " & DTSGlobalVariables.Count & vbCrLf & vbCrLf
For Each oGlobalVariable in DTSGlobalVariables
sName = oGlobalVariable.Name
sTypeName = GetTypeName(oGlobalVariable)
On Error Resume Next
sValue = CStr(oGlobalVariable.Value)
If Err.Number <> 0 Then
sValue = "<Invalid CStr Value>"
End If
On Error GoTo 0
LogVariable iIndex, sName, sTypeName, sValue
iIndex = iIndex + 1
Next
Set oPkg = Nothing
If Log_MsgBox Then
MsgBox m_sBuffer, vbOk, "GlobalVariable Diagnostics"
End if
If Log_EventLog Then
Const EventTypeInfo = 4
Dim oWshShell
Set oWshShell = CreateObject("WScript.Shell")
oWshShell.LogEvent EventTypeInfo, m_sBuffer
Set oWshShell = Nothing
End If
If Log_TextFile Then
Dim oFSO
Dim oFile
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFile = oFSO.OpenTextFile(Log_TextFile_Name, Log_TextFile_Mode, True)
oFile.Write m_sBuffer & vbCrLf
oFile.Close
Set oFile = Nothing
Set oFSO = Nothing
End If
Main = DTSTaskExecResult_Success
End Function
Function GetTypeName(ByVal pGlobalVariable)
Dim sTypeName
Dim vType
vType = VarType(pGlobalVariable.Value)
Select Case vType
Case 0 : sTypeName = "Empty"
Case 1 : sTypeName = "Null"
Case 2 : sTypeName = "Integer (Small)"
Case 3 : sTypeName = "Integer"
Case 4 : sTypeName = "Real (4 Byte)"
Case 5 : sTypeName = "Real (8 Byte)"
Case 6 : sTypeName = "Currency"
Case 7 : sTypeName = "Date"
Case 8 : sTypeName = "String (BSTR)"
Case 9 : sTypeName = "Dispatch"
Case 10 : sTypeName = "10 Unknown (ERROR)"
Case 11 : sTypeName = "Boolean"
Case 12 : sTypeName = "12 Unknown (VARIANT)"
Case 14 : sTypeName = "Decimal"
Case 16 : sTypeName = "Integer (1 Byte)"
Case 17 : sTypeName = "Unsigned Int (1 Byte)"
Case 18 : sTypeName = "Unsigned Int (2 Byte)"
Case 19 : sTypeName = "Unsigned Int (4 Byte)"
Case 20 : sTypeName = "20 Unknown (I8)"
Case 21 : sTypeName = "21 Unknown (UI8)"
Case 22 : sTypeName = "Int"
Case 23 : sTypeName = "Unsigned Int"
Case 8204 : sTypeName = "Array (Blank Name)"
Case Else : sTypeName = CStr(vType) & " Unknown"
End Select
GetTypeName = sTypeName
End Function
Sub LogVariable(ByVal iIndex, ByVal sName, ByVal sTypeName, ByVal sValue)
Dim sBuffer
sBuffer = "Index: " & vbTab & CStr(iIndex) & vbCrLf & _
"Name: " & vbTab & sName & vbCrLf & _
"Type: " & vbTab & sTypeName & vbCrLf & _
"Value: " & vbTab & sValue & vbCrLf & vbCrLf
m_sBuffer = m_sBuffer & sBuffer
End Sub
SQL = Scarcely Qualifies as a Language
-
At a guess I'd say that the profile that the DTS package is running under doesn't contain the INI file setting in it's environment.
How about just passing the INI file location in as a global variable from the command line?
--------------------
Colt 45 - the original point and click interface
-
"Can you not use the DTS Dynamic Properties task to assign the gv value from the INI file?"
Yes the package does contain a Dynamic Properties, but the INI file name is also dynamic and the original post contains a VBScript to get the DTS Ini file name from an environmental variable and then change the global variable source ini file location before running the Dynamic Properties Task.
Here is part of the VBScript:
Set oPkg = DTSGlobalVariables.Parent
Set oDynProps = oPkg.Tasks( "DTSTask_DTSDynamicPropertiesTask_1" ).CustomTask
for each oAssign in oDynProps.assignments
if oAssign.sourceType = DTSDynamicPropertiesSourceType_IniFile then
oAssign.SourceIniFileFileName = iniFileName
end if
next
SQL = Scarcely Qualifies as a Language
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply