Technical Article

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

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Related content

Technical Article

Shows Tables for a Given Database from DOS

If you are using OSQL or ISQL from DOS querys are really hard to use, because the result is to big for the screen. With this procedure, you can especify from wich column to wich column show the list of tables. For Example: proc_tables master, 3, 10 will show you only a short list of […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2001-10-28

753 reads

Technical Article

Run a Script Against All User Databases

Now you can run administrative T-SQL tasks against all your databases in one shot. Very usefull for environments that have a seperate database for each client, ASP's, etc. It's pretty straight forward and can be used for almost any task. This example Looks for a table and prints the database name and selects from the […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2001-10-21

3,113 reads

Technical Article

Find Longstanding Open Transactions

Ever forget to commit a transaction and then find out hours later that there is deadlocked transactions all over your database server? Worse yet has one of your coworkers done this to your database server? Never again, this stored procedure will net send the machine that has an open transaction, and send you (The DBA) […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4 (1)

You rated this post out of 5. Change rating

2001-10-08

9,105 reads