Export a DTS packages to a text file

[WillStanley]

Is it possible to export a DTS packages to a text file programmatically so that I can do a keyword text search?

 

[nigelrivett]

You can export to a visual basic file which you can then search.

I run a script which will write all the task attributes to a text file.
I'll post it on my web site (

http://www.nigelrivett.com)

when I get round to it.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[nigelrivett]

Oh well - if you run this in a dts package activex script or a VB app it should script all the packages - it'll highlight anything not catered for so you can add it.


Private Sub ScriptDTSPackages(sServerName, sUserName, sPassword, sPath)
Dim objCon
Dim objCmd
Dim objRsPackageNames

Set objCon = CreateObject("ADODB.Connection")
objCon.ConnectionString = "Provider=sqloledb;" & _
"Data Source=" & sServerName & ";" & _
"User Id=" & sUserName & ";" & ";" & _
"Password=" & sPassword & ";" & _
"Initial Catalog=" & "msdb"
objCon.CursorLocation = 3 'adUseClient
objCon.Open

Set objCmd = CreateObject("ADODB.Command")
objCmd.ActiveConnection = objCon
objCmd.CommandType = 1 'adCmdText
objCmd.CommandText = "select distinct name from sysdtspackages order by name"

Set objRsPackageNames = CreateObject("ADODB.Recordset")
Set objRsPackageNames = objCmd.Execute

Do While Not objRsPackageNames.EOF
ScriptDTSPackage2 objRsPackageNames("Name"), sPath, sServerName, 0, sUserName, sPassword
objRsPackageNames.MoveNext
Loop

Set objRsPackageNames = Nothing
Set objCmd = Nothing
Set objCon = Nothing

End Sub

Private Sub ScriptDTSPackage2(sPackageName, sFolder, sServer, iTrusted, sUID, sPWD)
Dim objFileScript
Dim objFStream

Dim objDTSPackage
Dim objDTSTask
Dim objDTSConnection
Dim objDTSTransformation
Dim objDTSDataPumpTask

Dim objDTSDynamicPropertiesTask
Dim objDTSDynamicPropertiesTaskAssignment

Dim i
Dim s1
Dim s2

Set objFileScript = CreateObject("Scripting.FileSystemObject")
Set objFStream = objFileScript.CreateTextFile(sFolder & sPackageName & ".txt")
' load package

Set objDTSPackage = CreateObject("DTS.Package2")
If iTrusted = 1 Then
objDTSPackage.LoadFromSQLServer sServer, , , 256, , , , sPackageName
Else
objDTSPackage.LoadFromSQLServer sServer, sUID, sPWD, , , , , sPackageName
End If

' connections
objFStream.WriteLine "************************"
objFStream.WriteLine "Connections"
objFStream.WriteLine "************************"
For Each objDTSConnection In objDTSPackage.Connections
objFStream.WriteLine "<ID=" & objDTSConnection.ID & ">" & "<name=" & objDTSConnection.Name & ">" & "<Source=" & objDTSConnection.DataSource & ">" & "<ProviderID=" & objDTSConnection.ProviderID & ">"
Next

' tasks
objFStream.WriteBlankLines 2
objFStream.WriteLine "************************"
objFStream.WriteLine "Tasks"
objFStream.WriteLine "************************"
For Each objDTSTask In objDTSPackage.Tasks
objFStream.WriteLine "<type=" & objDTSTask.CustomTaskID & ">" & "<name=" & objDTSTask.Name & ">" & "<Description=" & objDTSTask.Description & ">"
' executesql task
If objDTSTask.CustomTaskID = "DTSExecuteSQLTask" Then
objFStream.WriteLine "<sql statement>"
objFStream.WriteLine objDTSTask.Properties("SQLStatement")
objFStream.WriteLine "<sql statement end>"
' datapump task
ElseIf objDTSTask.CustomTaskID = "DTSDataPumpTask" Then
objFStream.WriteLine "<SourceObjectName=" & objDTSTask.Properties("SourceObjectName") & ">" & "<SourceConnectionID=" & objDTSTask.Properties("SourceConnectionID") & ">"
If objDTSTask.Properties("SourceSQLStatement") <> "" Then
objFStream.WriteLine "<source sql statement>"
objFStream.WriteLine objDTSTask.Properties("SourceSQLStatement")
objFStream.WriteLine "<source sql statement end>"
End If
objFStream.WriteLine "<DestinationObjectName=" & objDTSTask.Properties("DestinationObjectName") & ">" & "<DestinationConnectionID=" & objDTSTask.Properties("DestinationConnectionID") & ">"
Set objDTSDataPumpTask = objDTSTask.CustomTask
objFStream.WriteLine "<transformations=" & ">"
For Each objDTSTransformation In objDTSDataPumpTask.Transformations
For i = 1 To objDTSTransformation.DestinationColumns.Count
objFStream.WriteLine objDTSTask.Properties("SourceObjectName") & "." & objDTSTransformation.SourceColumns(i).Name & " --> " & objDTSTask.Properties("DestinationObjectName") & "." & objDTSTransformation.DestinationColumns(i).Name
Next
Next
objFStream.WriteLine "<transformations=" & " end>"
' dynamic proprties task
ElseIf objDTSTask.CustomTaskID = "DTSDynamicPropertiesTask" Then
Set objDTSDynamicPropertiesTask = objDTSTask.CustomTask
For Each objDTSDynamicPropertiesTaskAssignment In objDTSDynamicPropertiesTask.Assignments
If objDTSDynamicPropertiesTaskAssignment.SourceType = 0 Then
s1 = "<INIFile=" & objDTSDynamicPropertiesTaskAssignment.SourceIniFileFileName & ">" & "<key=" & objDTSDynamicPropertiesTaskAssignment.SourceIniFileSection & "." & objDTSDynamicPropertiesTaskAssignment.SourceIniFileKey & ">"
End If
s2 = " --> " & "<" & objDTSDynamicPropertiesTaskAssignment.DestinationPropertyID & ">"
objFStream.WriteLine s1 & s2
Next
ElseIf objDTSTask.CustomTaskID = "DTSActiveScriptTask" Then
objFStream.WriteLine "<ActiveXScript>"
objFStream.WriteLine objDTSTask.Properties("ActiveXScript")
objFStream.WriteLine "<ActiveXScript end>"
ElseIf objDTSTask.CustomTaskID = "DTSCreateProcessTask" Then
objFStream.WriteLine "<ProcessCommandLine>"
objFStream.WriteLine objDTSTask.Properties("ProcessCommandLine")
objFStream.WriteLine "<ProcessCommandLine end>"
Else
objFStream.WriteLine "**********************" & objDTSTask.Name & " task name not catered for **********************"
End If
objFStream.WriteBlankLines 2
Next

objFStream.Close
Set objFStream = Nothing
Set objDTSPackage = Nothing
End Sub



======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[WillStanley]

It works a charm, thank you very much.

I have used it in an MS Access DTS text utility.