Another Script to Dump DTS packages to Text
This script will dump most of the structure of a DTS package to a text file. This includes connections strings (note SQL Server connections have their passwords encrypted so the won't dump), tasks, steps, global veriables, etc.) The script is command line drive and should allow for the use of trusted or SQL logins. I have used this to dump all of my DTS packages to text files so that I can easily search them to see what tables, columns, etc. are being used.
Option Explicit
' ************************************
' ************************************
' ************************************
Dim sServerName
Dim bServerName
Dim sLoginId
Dim bLoginId
Dim sPassword
Dim bPassword
Dim sDatabase
Dim bDatabase
Dim bIncludHeadings
Dim bIs70
Dim sPath
Dim bPath
DIM sPackageName
DIM bPackageName
DIM sDTSPkg
Dim oServer
Dim oResults
Dim iRowCount
Dim sQuery
Const DTSSQLStgFlag_Default = 0
Const DTSSQLStgFlag_UseTrustedConnection = 256
Const DTSStepExecResult_Failure = 1
Const ForWriting = 2
' ************************************
' ************************************
' ************************************
CLASS DTSPackageScript
DIM oFile
Private Function ScriptColumn(oColumn, indent)
Dim oProperty
FOR Each oProperty IN oColumn.Properties
oFile.WriteLine String(indent, vbTab) & "*" _
& oProperty.Name & ":" _
& vbTab & oProperty.Value
NEXT
oFile.WriteLine
END Function
' ************************************
Private Function ScriptConnection(oConnection, indent)
Dim oProperty
ON ERROR RESUME NEXT
oFile.WriteLine String(indent, vbTab) & "===> " & _
oConnection.name
oFile.WriteLine String(indent, vbTab) & "*Catalog: " & _
oConnection.Catalog
oFile.WriteLine String(indent, vbTab) & "*Connected: " & _
oConnection.Connected
oFile.WriteLine String(indent, vbTab) & "*ConnectImmediate: " & _
oConnection.ConnectImmediate
oFile.WriteLine String(indent, vbTab) & "*ConnectionTimeout: " & _
oConnection.ConnectionTimeout
oFile.WriteLine String(indent, vbTab) & "*DataSource: " & _
oConnection.DataSource
oFile.WriteLine String(indent, vbTab) & "*Description: " & _
oConnection.Description
oFile.WriteLine String(indent, vbTab) & "*ID: " & _
oConnection.ID
oFile.WriteLine String(indent, vbTab) & "*InTransaction: " & _
oConnection.InTransaction
oFile.WriteLine String(indent, vbTab) & "*InUse: " & _
oConnection.InUse
oFile.WriteLine String(indent, vbTab) & "*LastOwnerTaskName: " & _
oConnection.LastOwnerTaskName
oFile.WriteLine String(indent, vbTab) & "*Parent: " & _
oConnection.Parent
oFile.WriteLine String(indent, vbTab) & "*Password: " & _
oConnection.Password
oFile.WriteLine String(indent, vbTab) & "*ProviderId: " & _
oConnection.ProviderId
oFile.WriteLine String(indent, vbTab) & "*Reusable: " & _
oConnection.Reusable
oFile.WriteLine String(indent, vbTab) & "*UDLPath: " & _
oConnection.UDLPath
oFile.WriteLine String(indent, vbTab) & "*UseDSL: " & _
oConnection.UseDSL
oFile.WriteLine String(indent, vbTab) & "*UserId: " & _
oConnection.UserId
oFile.WriteLine String(indent, vbTab) & "*UseTrustedConnection: " & _
oConnection.UseTrustedConnection
oFile.WriteLine String(indent, vbTab) & _
" -- Oledb connection properties -- "
FOR EACH oProperty in oConnection.ConnectionProperties
oFile.WriteLine String(indent, vbTab) & "*" _
& oProperty.name & ":" & vbTab & _
oProperty.value
NEXT
oFile.WriteLine
SET oConn = Nothing
END Function
' ************************************
Private Function ScriptCustomTask(oCustomTask, indent)
Dim oProperty
FOR Each oProperty IN oCustomTask.Properties
oFile.WriteLine String(indent, vbTab) & "*" _
& oProperty.Name & ":" _
& vbTab & oProperty.Value
NEXT
oFile.WriteLine
END Function
' ************************************
Private Function ScriptDTSMQMessage(oDTSMQMessage, indent)
Dim oProperty
FOR Each oProperty IN oDTSMQMessage.Properties
oFile.WriteLine String(indent, vbTab) & "*" _
& oProperty.Name & ":" _
& vbTab & oProperty.Value
NEXT
END Function
' ************************************
Private Function ScriptDynamicPropertiesTaskAssignment( _
oDynamicPropertiesTaskAssignment, indent)
Dim oProperty
FOR Each oProperty IN oDynamicPropertiesTaskAssignment.Properties
oFile.WriteLine String(indent, vbtab) & "*" _
& oProperty.Name & ":" _
& vbTab & oProperty.Value
NEXT
END Function
' ************************************
Private Function ScriptGlobalVariable(oGlobalVariable, indent)
Dim oProperty
FOR Each oProperty IN oGlobalVariable.Properties
oFile.WriteLine String(indent, vbTab) & "*" _
& oProperty.Name & ":" _
& vbTab & oProperty.Value
NEXT
END Function
' ************************************
Private Function ScriptLookUp(oLookUp, indent)
Dim oProperty
FOR Each oProperty IN oLookUp.Properties
oFile.WriteLine String(indent, vbTab) & "*" _
& oProperty.Name & ":" _
& vbTab & oProperty.Value
NEXT
END Function
' ************************************
Private Function ScriptOLEDBProperty(oOLEDBProperty, indent)
Dim oProperty
FOR Each oProperty IN oOLEDBProperty.Properties
oFile.WriteLine String(indent, vbTab) & "*" _
& oProperty.Name & ":" _
& vbTab & oProperty.Value
NEXT
END Function
' ************************************
Private Function ScriptOLEDBProviderInfo(oOLEDBProviderInfo, indent)
' Note: May have to list out each property.
' not sure supports the Poperty collection
Dim oProperty
FOR Each oProperty IN oOLEDBProviderInfo.Properties
oFile.WriteLine String(indent, vbTab) & "*" _
& oProperty.Name & ":" _
& vbTab & oProperty.Value
NEXT
END Function
' ************************************
Private Function ScriptPackageInfo(oPackageInfo, indent)
Dim oProperty
FOR Each oProperty IN oPackageInfo.Properties
oFile.WriteLine String(indent, vbTab) & "*" _
& oProperty.Name & ":" _
& vbTab & oProperty.Value
NEXT
END Function
' ************************************
Private Function ScriptPackageLineage(oPackageLineage, indent)
Dim oProperty
FOR Each oProperty IN oPackageLineage.Properties
oFile.WriteLine String(indent, vbTab) & "*" _
& oProperty.Name & ":" _
& vbTab & oProperty.Value
NEXT
END Function
' ************************************
Private Function ScriptPackageLogRecord(oPackageLogRecord, indent)
Dim oProperty
FOR Each oProperty IN oPackageLogRecord.Properties
oFile.WriteLine String(indent, vbTab) & "*" _
& oProperty.Name & ":" _
& vbTab & oProperty.Value
NEXT
END Function
' ************************************
Private Function ScriptPrecedenceConstraint(oPrecedenceConstraint, indent)
Dim oProperty
FOR Each oProperty IN oPrecedenceConstraint.Properties
oFile.WriteLine String(indent, vbTab) & "*" _
& oProperty.Name & ":" _
& vbTab & oProperty.Value
NEXT
END Function
' ************************************
Private Function ScriptProperty(Properties, indent)
Dim oProperty
ON ERROR RESUME NEXT
FOR Each oProperty IN Properties
oFile.WriteLine String(indent, vbTab) & "*" _
& oProperty.Name & ":" _
& vbTab & oProperty.Value
NEXT
END Function
' ************************************
Private Function ScriptSavedPackageInfo(oSavedPackageInfo, indent)
Dim oProperty
FOR Each oProperty IN oSavedPackageInfo.Properties
oFile.WriteLine String(indent, vbTab) & "*" _
& oProperty.Name & ":" _
& vbTab & oProperty.Value
NEXT
END Function
' ************************************
Private Function ScriptScriptingLanguageInfo(oScriptingLanguageInfo, indent)
Dim oProperty
FOR Each oProperty IN oScriptingLanguageInfo.Properties
oFile.WriteLine String(indent, vbTab) & "*" _
& oProperty.Name & ":" _
& vbTab & oProperty.Value
NEXT
END Function
' ************************************
Private Function ScriptStep(oStep, indent)
Dim oProperty
Dim oPrecedenceConstraint
FOR Each oProperty IN oStep.Properties
oFile.WriteLine String(indent, vbTab) & "*" _
& oProperty.Name & ":" _
& vbTab & oProperty.Value
NEXT
oFile.WriteLine String(indent, vbTab) & "-- Precedence Constraint"
ScriptPrecedenceConstraints oStep, indent + 1
END Function
' ************************************
Private Function ScriptStepLineage(oStepLineage, indent)
Dim oProperty
FOR Each oProperty IN oStepLineage.Properties
oFile.WriteLine String(indent, vbTab) & "*" _
& oProperty.Name & ":" _
& vbTab & oProperty.Value
NEXT
END Function
' ************************************
Private Function ScriptStepLogRecord(oStepLogRecord, indent)
Dim oProperty
FOR Each oProperty IN oStepLogRecord.Properties
oFile.WriteLine String(indent, vbTab) & "*" _
& oProperty.Name & ":" _
& vbTab & oProperty.Value
NEXT
END Function
' ************************************
Private Function ScriptTaskInfo(oTaskInfo, indent)
Dim oProperty
FOR Each oProperty IN oTaskInfo.Properties
oFile.WriteLine String(indent, vbTab) & "*" _
& oProperty.Name & ":" _
& vbTab & oProperty.Value
NEXT
END Function
' ************************************
Private Function ScriptTask(oTask, indent)
' Need to add in Handling of each different
' type of task i.e., ActiveScriptTask, BulkInsertTask,
' etc.
Dim oProperty
FOR Each oProperty IN oTask.Properties
oFile.WriteLine String(indent, vbTab) & "*" _
& oProperty.Name & ":" _
& vbTab & oProperty.Value
NEXT
ON ERROR RESUME NEXT
Dim oDTSObject
SELECT CASE oTask.CustomTaskID
CASE "DTSDataPumpTask"
Set oDTSObject = oTask.CustomTask
'oFile.WriteLine String(indent, vbTab) & _
' "-- Destination Command Properties"
'ScriptOLEDBProperties _
' oDTSObject.DestinationCommandProperties, indent + 1
'oFile.WriteLine String(indent, vbTab) & _
' "-- Source Command Properties"
'ScriptOLEDBProperties _
' oDTSObject.SourceCommandProperties, indent + 1
oFile.WriteLine String(indent, vbTab) & _
"-- Destination Column Definitions "
ScriptColumns _
oDTSObject.DestinationColumnDefinitions, indent + 1
oFile.WriteLine String(indent, vbTab) & _
"-- Lookups"
ScriptLookups oDTSObject, indent + 1
oFile.WriteLine String(indent, vbTab) & _
"-- Transformations"
ScriptTransformations oDTSObject, indent + 1
CASE "DTSDataDrivenQueryTask"
Set oDTSObject = oTask.CustomTask
'oFile.WriteLine String(indent, vbTab) & _
' "-- Destination Command Properties"
'ScriptOLEDBProperties _
' oDTSObject.DestinationCommandProperties, indent + 1
'oFile.WriteLine String(indent, vbTab) & _
' "-- Source Command Properties"
'ScriptOLEDBProperties _
' oDTSObject.SourceCommandProperties, indent + 1
oFile.WriteLine String(indent, vbTab) & _
"-- Destination Column Definitions "
ScriptColumns _
oDTSObject.DestinationColumnDefinitions, indent + 1
oFile.WriteLine String(indent, vbTab) & _
"-- Lookups"
ScriptLookups oDTSObject, indent + 1
oFile.WriteLine String(indent, vbTab) & _
"-- Transformations"
ScriptTransformations oDTSObject, indent + 1
oFile.WriteLine String(indent, vbTab) & _
"-- Delete Query Columns "
ScriptColumns oDTSObject.DeleteQueryColumns, indent + 1
oFile.WriteLine String(indent, vbTab) & _
"-- Insert Query Columns "
ScriptColumns oDTSObject.InsertQueryColumns, indent + 1
oFile.WriteLine String(indent, vbTab) & _
"-- Update Query Columns "
ScriptColumns oDTSObject.UpdateQueryColumns, indent + 1
oFile.WriteLine String(indent, vbTab) & _
"-- User Query Columns "
ScriptColumns oDTSObject.UserQueryColumns, indent + 1
CASE "DTSMessageQueueTask"
Set oDTSObject = oTask.CustomTask
oFile.WriteLine String(indent, vbTab) & _
"-- DTSMQMessages "
ScriptDTSMQMessages oDTSObject.DTSMQMessages, indent + 1
CASE "DTSDynamicPropertiesTask"
Set oDTSObject = oTask.CustomTask
oFile.WriteLine String(indent, vbTab) & _
"-- Dynamic Properties Task Assignments"
ScriptDynamicPropertiesTaskAssignment _
oDTSObject.DynamicPropertiesTaskAssignments, indent + 1
CASE "DTSExecutePackageTask"
Set oDTSObject = oTask.CustomTask
oFile.WriteLine String(indent, vbTab) & _
"-- Global Variables"
ScriptGlobalVariables _
oDTSObject, indent
CASE "DTSParallelDataPumpTask"
Set oDTSObject = oTask.CustomTask
'oFile.WriteLine String(indent, vbTab) & _
' "-- Destination Command Properties"
'ScriptOLEDBProperties _
' oDTSObject.DestinationCommandProperties, indent + 1
'oFile.WriteLine String(indent, vbTab) & _
' "-- Source Command Properties"
'ScriptOLEDBProperties _
' oDTSObject.SourceCommandProperties, indent + 1
oFile.WriteLine String(indent, vbTab) & _
"-- Transformation Sets"
ScriptTransformationSets _
oDTSObject, indent
END SELECT
ScriptTask = sText
END Function
' ************************************
Private Function ScriptTaskLogRecord(oTaskLogRecord, indent)
Dim oProperty
FOR Each oProperty IN oTaskLogRecord.Properties
oFile.WriteLine String(indent, vbTab) & "*" _
& oProperty.Name & ":" _
& vbTab & oProperty.Value
NEXT
END Function
' ************************************
Private Function ScriptTransformation(oTransformation, indent)
' Need to other collections
Dim oProperty
FOR Each oProperty IN oTransformation.Properties
oFile.WriteLine String(indent, vbTab) & "*" _
& oProperty.Name & ":" _
& vbTab & oProperty.Value
NEXT
oFile.WriteLine String(indent, vbTab) & "-- SourceColumns "
ScriptColumns oTransformation.SourceColumns, indent + 1
oFile.WriteLine String(indent, vbTab) & "-- DestinationColumns "
ScriptColumns oTransformation.DestinationColumns, indent + 1
oFile.WriteLine String(indent, vbTab) & "-- TransformServerProperties "
ScriptColumns oTransformation.TransformServerProperties, indent + 1
END Function
' ************************************
Private Function ScriptTransformationtionInfo(oTransformationtionInfo, indent)
' Need to other collections
Dim oProperty
FOR Each oProperty IN oTransformationtionInfo.Properties
oFile.WriteLine String(indent, vbTab) & "*" _
& oProperty.Name & ":" _
& vbTab & oProperty.Value
NEXT
END Function
' ************************************
Private Function ScriptTransformationSet(oTransformationSet, indent)
' Need to other collections
Dim oProperty
FOR Each oProperty IN oTransformationSet.Properties
oFile.WriteLine String(indent, vbTab ) & "*" _
& oProperty.Name & ":" _
& vbTab & oProperty.Value
NEXT
END Function
' ************************************
' Collections
' ************************************
Private Function ScriptColumns(oColumns, indent)
Dim oColumn
FOR EACH oColumn in oColumns
ScriptColumn oColumn, indent + 1
NEXT
END Function
' ************************************
Private Function ScriptConnections(oDtsPackage, indent)
Dim oConns
Dim oConnection
SET oConns = oDtsPackage.Connections
FOR EACH oConnection IN oConns
ScriptConnection oConnection, indent
NEXT
SET oConns = Nothing
END Function
' ************************************
Private Function ScriptDTSMQMessages(oDtsPackage, indent)
Dim oDTSMQMessage
FOR EACH oDTSMQMessage IN oDtsPackage.DTSMQMessages
ScriptDTSMQMessage oDTSMQMessage, indent + 1
NEXT
END Function
' ************************************
Private Function ScriptDynamicPropertiesTaskAssignments(oDtsPackage, indent)
Dim oDynamicPropertiesTaskAssignment
FOR EACH oDynamicPropertiesTaskAssignment IN oDtsPackage.DynamicPropertiesTaskAssignments
ScriptDynamicPropertiesTaskAssignment _
oDynamicPropertiesTaskAssignment, indent + 1
NEXT
END Function
' ************************************
Private Function ScriptGlobalVariables(oDtsPackage, indent)
Dim oGlobalVariable
FOR EACH oGlobalVariable IN oDtsPackage.GlobalVariables
ScriptGlobalVariable oGlobalVariable, indent + 1
NEXT
END Function
' ************************************
Private Function ScriptLookups(oDtsPackage, indent)
Dim oLookup
FOR EACH oLookup IN oDtsPackage.Lookups
ScriptLookup oLookup, indent + 1
NEXT
END Function
' ************************************
Private Function ScriptOLEDBProperties(oDtsPackage, indent)
Dim oOLEDBProperty
FOR EACH oOLEDBProperty IN oDtsPackage.OLEDBProperties
ScriptOLEDBProperty oOLEDBProperty, indent + 1
NEXT
END Function
' ************************************
Private Function ScriptOLEDBProviderInfos(oDtsPackage, indent)
Dim oOLEDBProviderInfo
FOR EACH oOLEDBProviderInfo IN oDtsPackage.OLEDBProviderInfos
ScriptOLEDBProviderInfo oOLEDBProviderInfo, indent + 1
NEXT
END Function
' ************************************
Private Function ScriptPrecedenceConstraints(oDtsPackage, indent)
Dim oPrecedenceConstraint
FOR EACH oPrecedenceConstraint IN oDtsPackage.PrecedenceConstraints
ScriptPrecedenceConstraint oPrecedenceConstraint, indent + 1
NEXT
END Function
' ************************************
Private Function ScriptProperties(oDtsPackage, indent)
Dim oProperty
ON ERROR RESUME NEXT
FOR EACH oProperty IN oDtsPackage.Properties
ScriptProperty oProperty, indent + 1
NEXT
END Function
' ************************************
Private Function ScriptSavedPackageInfos(oDtsPackage, indent)
Dim oSavedPackageInfo
ON ERROR RESUME NEXT
FOR EACH oSavedPackageInfo IN oDtsPackage.SavedPackageInfos
ScriptSavedPackageInfo oSavedPackageInfo, indent + 1
NEXT
END Function
' ************************************
Private Function ScriptScriptingLanguageInfos(oDtsPackage, indent)
Dim oScriptingLanguageInfo
FOR EACH oScriptingLanguageInfo IN oDtsPackage.ScriptingLanguageInfos
oFile.WriteLine String(indent, vbTab) & _
"*UseCache:" & vbTab & _
oScriptingLanguageInfo.UseCache
ScriptScriptingLanguageInfo oScriptingLanguageInfo, indent + 1
NEXT
END Function
' ************************************
Private Function ScriptSteps(oDtsPackage, indent)
Dim oStep
FOR EACH oStep IN oDtsPackage.Steps
oFile.WriteLine vbCrLf & String(indent, vbTab ) & "==> " & _
oStep.Name
ScriptStep oStep, indent + 1
NEXT
END Function
' ************************************
Private Function ScriptTaskInfos(oDtsPackage, indent)
Dim oTaskInfo
FOR EACH oTaskInfo IN oDtsPackage.TaskInfos
oFile.WriteLine String(indent, vbTab) & _
"*UseCache:" & vbTab & _
oTaskInfo.UseCache
ScriptTaskInfo oTaskInfo, indent + 1
NEXT
END Function
' ************************************
Private Function ScriptTasks(oDtsPackage, indent)
Dim oTask
FOR EACH oTask IN oDtsPackage.Tasks
oFile.WriteLine vbCrLf & String(indent, vbTab) & _
"==> " & oTask.Name
oFile.WriteLine String(indent, vbTab) & _
"*Custom Task ID:" & vbTab & oTask.CustomTaskID
ScriptTask oTask, indent + 1
NEXT
END Function
' ************************************
Private Function ScriptTransformationInfos(oDtsPackage, indent)
Dim oTransformationInfo
FOR EACH oTransformationInfo IN oDtsPackage.TransformationInfos
ScriptTransformationInfo oTransformationInfo, indent + 1
NEXT
END Function
' ************************************
Private Function ScriptTransformations(oDtsPackage, indent)
Dim oTransformation
FOR EACH oTransformation IN oDtsPackage.Transformations
ScriptTransformation oTransformation, indent + 1
NEXT
END Function
' ************************************
Private Function ScriptTransformationSets(oDtsPackage, indent)
Dim oTransformationSet
FOR EACH oTransformationSet IN oDtsPackage.TransformationSets
ScriptTransformationSet oTransformationSet, indent + 1
oFile.WriteLine String(indent, vbTab) & "-- Destination Column Definitions "
ScriptColumns oTransformationSet.DestinationColumnDefinitions, indent + 1
oFile.WriteLine String(indent, vbTab) & "-- Lookups "
ScriptLookups oTransformationSet.Lookups, indent + 1
oFile.WriteLine String(indent, vbTab) & "-- Transformation "
ScriptTransformations oTransformationSet, indent
oFile.WriteLine String(indent, vbTab) & "-- Delete Query Columns "
ScriptColumns oTransformationSet.DeleteQueryColumns, indent + 1
oFile.WriteLine String(indent, vbTab) & "-- Insert Query Columns "
ScriptColumns oTransformationSet.InsertQueryColumns, indent + 1
oFile.WriteLine String(indent, vbTab) & "-- Update Query Columns "
ScriptColumns oTransformationSet.UpdateQueryColumns, indent + 1
oFile.WriteLine String(indent, vbTab) & "-- User Query Columns "
ScriptColumns oTransformationSet.UserQueryColumns, indent + 1
NEXT
END Function
' ************************************
Public Sub DumpPackage(sPackageName, sServerName, sLoginId, sPassword, oOutFile)
DIM oDTSPackage
DIM indent
Set oFile = oOutFile
indent = 1
oFile.WriteLine "================================================"
oFile.WriteLine sPackageName
oFile.WriteLine "================================================"
ON ERROR RESUME NEXT
Set oDTSPackage = CreateObject("DTS.Package")
IF LEN(TRIM(sLoginId)) = 0 THEN
oDTSPackage.LoadFromSQLServer sServerName, , , _
DTSSQLStgFlag_UseTrustedConnection,"","","",sPackageName
ELSE
oDTSPackage.LoadFromSQLServer sServerName, sLoginId, sPassword, _
DTSSQLStgFlag_Default,"","","",sPackageName
End IF
oFile.WriteLine "***** Package Properties *****"
ScriptProperty oDTSPackage.Properties, indent
oFile.WriteLine "***** Package Connections *****"
ScriptConnections oDTSPackage, indent
oFile.WriteLine "***** Package Global Variables *****"
ScriptGlobalVariables oDTSPackage, indent
oFile.WriteLine "***** Package Save PackageInfos *****"
ScriptSavedPackageInfos oDTSPackage, indent
oFile.WriteLine "***** Package Steps *****"
ScriptSteps oDTSPackage, indent
oFile.WriteLine "***** Package Tasks *****"
ScriptTasks oDTSPackage, indent
oFile.WriteLine
SET oDTSPackage = Nothing
Set oFile = Nothing
END Sub
END CLASS
' **********************************************
' **********************************************
' **********************************************
' **********************************************
Main
' *******************************************************************
sub Main
Dim objArgs
Dim iCnt
Dim tStartTime
Dim tEndTime
tStartTime = Time()
iCnt = 0
WScript.Echo "Start Time: " & FormatDateTime(Date, vbLongDate) _
& " " & FormatDateTime(tStartTime, vbLongTime)
' check for command line switches
If not GetSwitches then
Exit Sub
end if
' Creat an object
'Set oServer = CreateObject("SQLDMO.SQLServer")
'oServer.Connect sServerName, sLoginId, sPassword
Set oServer = ServerConnect(sServerName, sLoginId, sPassword)
if (oServer is nothing) then
WScript.Echo"Error Creating Object"
Exit sub
end if
' Remove any trailing
IF Right(sPath,1) = "\" THEN
sPath = Left(sPath,Len(sPath)-1)
END If
CheckDir (sPath)
IF bPackageName THEN
sQuery = "SELECT DISTINCT [NAME] FROM msdb.dbo.sysdtspackages" & _
" WHERE [NAME] = '" & sPackageName & "'"
ELSE
sQuery = "SELECT DISTINCT [NAME] FROM msdb.dbo.sysdtspackages"
END IF
SET oResults = oServer.ExecuteWithResults (sQuery)
iCnt = oResults.Rows
FOR iRowcount = 1 TO oResults.Rows
sDTSPkg = oResults.GetColumnString(iRowCount, 1)
Wscript.Echo vbTab & sDTSPkg _
& vbTab & "(" & FormatDateTime(Date(),vbShortDate) _
& " " & FormatDateTime(Time(), vbLongTime) & ")"
DumpPackage sDTSPkg
NEXT
oServer.DisConnect
oServer.Close
Set oServer = Nothing
tEndTime = Time()
WScript.Echo "End Time: " & FormatDateTime(Date(), vbLongDate) _
& " " & FormatDateTime(tEndTime, vbLongTime)
WScript.Echo "Processed " & iCnt & " packages in " _
& DateDiff("n", tStartTime, tEndTime) _
& " Minutes"
End Sub
'*************************************
'*************************************
'*************************************
Function DumpPackage(sPackageName)
DIM clsDTSScript
DIM sPackageFile
DIM oFS
DIM oFT
' DIM sPackageName
SET oFS = CreateObject("Scripting.FileSystemObject")
SET clsDTSScript = New DTSPackageScript
sPackageFile = sPath & "\" & sPackageName & ".txt"
SET oFT = oFS.CreateTextFile(sPackageFile,True)
clsDTSScript.DumpPackage sPackageName, sServerName, sLoginId, sPassword, oFT
SET clsDTSScript = Nothing
oFT.Close()
SET oFT = Nothing
SET oFS = Nothing
End Function
'*************************************
'*************************************
'*************************************
Function GetSwitches()
Dim j
Dim sOption
Dim iCnt
Dim objArgs
GetSwitches = True
set objArgs = Wscript.Arguments
iCnt = ObjArgs.Count - 1
sServerName = ""
bServerName = False
sLoginId = ""
bLoginId = False
sPassword = ""
bPassword = False
sDatabase = ""
bDatabase = False
bIncludHeadings = False
sPath = ""
bPath = False
sPackageName = ""
bPackageName = False
' bTrustedConnect = False
j = 0
While j <= iCnt
If Left(objArgs(j),1) = "-" Or _
Left(objArgs(j),1) = "/" Then
sOption = UCase(Mid(objArgs(j), 2, 1))
Select Case sOption
Case "S"
bServername = TRUE
if Len(Trim(objArgs(j))) = 2 Then
j = j + 1
sServerName = Trim(objArgs(j))
if Len(Trim(objArgs(j))) = 0 then
bServerName = False
End if
Else
sServerName = Trim(Mid(objArgs(j),3))
End if
Case "U"
bLoginId = TRUE
if Len(Trim(objArgs(j))) = 2 Then
j = j + 1
sLoginId = Trim(objArgs(j))
if Len(Trim(objArgs(j))) = 0 then
bLoginId = False
End if
Else
sLoginId = Trim(Mid(objArgs(j),3))
End if
Case "P"
bPassword = TRUE
if Len(Trim(objArgs(j))) = 2 Then
j = j + 1
if j > iCnt Then ' There is nothing following the password
bPassword = False
j = j - 1
else
sPassword = Trim(objArgs(j))
if Len(Trim(objArgs(j))) = 0 then
bPassword = False
End if
End if
Else
sPassword = Trim(Mid(objArgs(j),3))
End if
Case "D"
bPath = TRUE
if Len(Trim(objArgs(j))) = 2 Then
j = j + 1
sPath = Trim(objArgs(j))
if Len(Trim(objArgs(j))) = 0 then
bPath = False
End if
Else
sPath = Trim(Mid(objArgs(j),3))
End if
Case "N"
bPackageName = TRUE
if Len(Trim(objArgs(j))) = 2 Then
j = j + 1
sPackageName = Trim(objArgs(j))
if Len(Trim(objArgs(j))) = 0 then
bPackageName = False
End if
Else
sPackageName = Trim(Mid(objArgs(j),3))
End if
Case "?"
Usage
GetSwitches = False
Exit Function
End Select
end if
j = j + 1
Wend
If (bPassword Or bLoginId) and Not bServerName then
WScript.Echo "***ERROR: Must specifiy Server when using Login Id or Password"
Usage
GetSwitches = False
end if
If Not bpath then
WScript.Echo "***ERROR: Must specifiy a path to place output into."
Usage
GetSwitches = False
end if
End Function
'*************************************
'*************************************
'*************************************
Function Usage()
WScript.Echo
WScript.Echo "==========================================="
WScript.Echo "ScriptDTSPkg.vbs Version 1.00"
WScript.Echo vbTab & "By Mark Carey"
WScript.Echo "Usage:"
WScript.Echo "cscript ScriptDTSPkg.vbs [//Nologo] [-S <server> [-U <loginid> [-P <password>]]] "
WScript.Echo vbTab & vbTab & "-D <directory> [-N <Package name>]"
WScript.Echo
WScript.Echo vbtab & "-S <server> = SQL Server name to connect to"
WScript.Echo vbTab & "-U <loginId> = SQL Server login id"
WScript.Echo vbTab & vbTab & "Uses trusted connection if no -U"
WScript.Echo vbTab & "-P <password> = SQL Server password"
WScript.Echo vbTab & "-D <directory> = Path to write files to"
WScript.Echo vbTab & "-N <Package name> = optional package name to extract"
WScript.Echo vbTab & "-? = Usage"
WScript.Echo "==========================================="
End function
'*************************************
'*************************************
'*************************************
Function CheckDir(sPath)
Dim oFs
Set oFs = CreateObject("Scripting.FileSystemObject")
if oFs.FolderExists(sPath) = vbFalse then
oFs.CreateFolder(sPath)
end if
Set oFs = Nothing
End function
'*************************************
'*************************************
'*************************************
FUNCTION ServerConnect(sServerName, sUID, sPwd)
Dim bServerName
Dim bPassword
Dim bUid
Dim mobjConn
Set ServerConnect = Nothing
IF LEN(TRIM(sServerName)) = 0 THEN
bServerName = vbFalse
ELSE
bServerName = vbTrue
END IF
IF LEN(TRIM(sPwd)) = 0 THEN
bPassword = vbFalse
ELSE
bPassword = vbTrue
END IF
IF LEN(TRIM(sUid)) = 0 THEN
bUid = vbFalse
ELSE
bUid = vbTrue
END IF
Set mobjConn = CreateObject("SQLDMO.SQLServer")
ON ERROR RESUME NEXT
IF (mobjConn IS Nothing) THEN
WScript.Echo "Error Creating Server Object"
Connect = vbFalse
Exit Function
ELSE
IF bServerName THEN
IF bPassword THEN
mobjConn.Connect sServerName, sUid, sPwd
ELSE
IF bUid THEN
mobjConn.Connect sServerName, sUid
ELSE
mobjConn.LoginSecure = vbTrue
mobjConn.connect sServerName ' This uses trusted
END IF
END IF
ELSE
mobjConn.LoginSecure = vbTrue
mobjConn.connect sServerName ' This uses trusted
END IF
END IF
IF Err.Number <> 0 THEN
WScript.Echo "Error Connecting to server"
WScript.Echo Err.Description
Connect = vbFalse
Exit Function
END IF
Set ServerConnect = mobjConn
END FUNCTION
