DTS documenation task. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS documenation task.

Author

Topic

SQLCode
Posting Yak Master

143 Posts

Posted - 2004-03-31 : 13:44:37

Hi,
I have to analyze all DTS packages of a few servers.
Is it possible to document the packages with the workflow and properties, in any relatively quick method?

Right now I am opening the packages and documenting each step manually.

I have a few very huge packages that are used to build a warehouse.

Please help.

TIA

nr
SQLTeam MVY

12543 Posts

Posted - 2004-03-31 : 14:37:09

Try this
It will give all the connections and transformation to text files in a folder.
Any tasks not included will be marked as "not catered for" - search for those and add them.
I think this version is for an activex script in a dts package but will probably work from VB too.

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

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

==========================================
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.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-03-31 : 14:39:05

Doesn't include the workflow - maybe I'll include it sometime.

==========================================
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.

SQLCode
Posting Yak Master

143 Posts

Posted - 2004-03-31 : 14:50:28

Thanks Nigel! Appreciate your post. I keep checking your site also quite often.

I do not have VB and do not know how to use your function. Would you pls. guide me?

TIA

nr
SQLTeam MVY

12543 Posts

Posted - 2004-03-31 : 14:59:19

It's not on my site yet.

Put in into an activex script in a dts package

Add a call to
Sub ScriptDTSPackages(sServerName, sUserName, sPassword, sPath)
with the relevent parameters and it will script all packages on that server to the path given.

This is set to call ScriptDTSPackage2 with sql server security you will have to change the call if you want it to be trusted.

==========================================
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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources