Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2002
    Posts
    77

    Unanswered: finding objects in DTS

    Hi All,

    My workplace has about 50 dts packages created over the years. I want to get a list of DTS packages that uses the table "tbl_members". Is there a query I can
    run or a stored procedure I can create that would let me do this instead of openign the dts packages one by one and goign through the code?

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You could save all of the DTS packages as Visual Basic files, and fgrep through them all. No automated way of doing it, since they are saved in msdb as BLOBs.

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    We have found this script somewhere on the Net.

    It will export all your DTSes to TXT files. It saves you from the burden to export them each by hand.

    You can comment some parts of the DTS out, so you get shorter output files.
    Code:
    '**********************************************************************
    '  Visual Basic ActiveX Script
    '************************************************************************
    Function Main()
    Dim sServerName
    Dim sPath
    Dim sUserName
    Dim sPassword
    Dim iTrusted
     
            'sServerName = DTSGlobalVariables("ServerName").Value
            'sPath = DTSGlobalVariables("Path").Value
            'sUserName = DTSGlobalVariables("UserName").Value
            'sPassword = DTSGlobalVariables("Password").Value
     
            sServerName = "SERVER\dirIU_BI"
            sPath = "c:\tmp\DTS\"
            sUserName = "MyUserName"
            sUserName = ""
            sPassword = "*****"
    
    
            if len(sUserName) = 0 then
                   iTrusted = "Y"
            else
                   iTrusted = "N"
            end if
     iTrusted      = "Y"
            ScriptDTSPackages sServerName, sPath, iTrusted, sUserName, sPassword
            
            Main = DTSTaskExecResult_Success
    End Function
     
     
    Private Sub ScriptDTSPackages(sServerName, sPath, iTrusted, sUserName, sPassword)
    Dim objCon
    Dim objCmd
    Dim objRsPackageNames
     
            Set objCon = CreateObject("ADODB.Connection")
            
            if iTrusted = "Y" then
                   objCon.ConnectionString = "Provider=sqloledb;" & _
                                   "Data Source=" & sServerName & ";" & _
                                   "Initial Catalog=" & "msdb" & ";" & _
                                   "Integrated Security=SSPI"
            else
                   objCon.ConnectionString = "Provider=sqloledb;" & _
                                   "Data Source=" & sServerName & ";" & _
                                   "User Id=" & sUserName & ";" & ";" & _
                                   "Password=" & sPassword & ";" & _
                                   "Initial Catalog=" & "msdb"
            end if
            
            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 where name like 'TE_%' order by name"
            
            Set objRsPackageNames = CreateObject("ADODB.Recordset")
            Set objRsPackageNames = objCmd.Execute
            
            'ScriptDTSPackage "alantest", frmInitial.txtFolder, frmInitial.txtServer, frmInitial.chkTrusted, frmInitial.txtUsername, frmInitial.txtPassword
            
            Do While Not objRsPackageNames.EOF
                   ScriptDTSPackage2 objRsPackageNames("Name"), sPath, sServerName, iTrusted, 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 objDTSGlobalVariable
    Dim objDTSCustomTask
    Dim objDTSProperty
     
    Dim objDTSDynamicPropertiesTask
    Dim objDTSDynamicPropertiesTaskAssignment
     
    Dim i
    Dim s1
    Dim s2
     
            Set objFileScript = CreateObject("Scripting.FileSystemObject")
            Set objFStream = objFileScript.CreateTextFile(sFolder & sPackageName & "_properties.txt")
     
            ' load package
            Set objDTSPackage = CreateObject("DTS.Package2")
            If iTrusted = "Y" Then
                   objDTSPackage.LoadFromSQLServer sServer, , , 256, , , , sPackageName
            Else
                   objDTSPackage.LoadFromSQLServer sServer, sUID, sPWD, , , , , sPackageName
            End If
    'msgbox("var")        
            ' Global variables
            objFStream.WriteLine "************************"
            objFStream.WriteLine "Global Variables"
            objFStream.WriteLine "************************"
            For Each objDTSGlobalVariable In objDTSPackage.GlobalVariables
                    objFStream.WriteLine "<" & objDTSGlobalVariable.name & "=" & objDTSGlobalVariable.value & ">"
            Next
    'msgbox("con")        
            ' connections
            objFStream.WriteBlankLines 2
            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
    'msgbox("Tas")        
            ' 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>"
    ' msgbox("pump")
                   ' 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.SourceColumns.Count 
                                          objFStream.WriteLine "Source : " & objDTSTask.Properties("SourceObjectName") & "." & objDTSTransformation.SourceColumns(i).Name 
                                   Next
    
                                   For i = 1 To objDTSTransformation.DestinationColumns.Count 
                                          objFStream.WriteLine "Destination : " & objDTSTask.Properties("DestinationObjectName") & "." & objDTSTransformation.DestinationColumns(i).Name
                                   Next
                           Next
                           objFStream.WriteLine "<transformations=" & " end>"
    Code is too long to fit in 1 post. Next part continued on next post.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    .. code continues.
    Code:
                   ' 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
     
                   ' Activex script task
                   ElseIf objDTSTask.CustomTaskID = "DTSActiveScriptTask" Then
                           objFStream.WriteLine "<ActiveXScript>"
                           objFStream.WriteLine objDTSTask.Properties("ActiveXScript")
                           objFStream.WriteLine "<ActiveXScript end>"
     
                   ' Creae Proecss task
                   ElseIf objDTSTask.CustomTaskID = "DTSCreateProcessTask" Then
                           objFStream.WriteLine "<ProcessCommandLine>"
                           objFStream.WriteLine objDTSTask.Properties("ProcessCommandLine")
                           objFStream.WriteLine "<ProcessCommandLine end>"
     
                   ' Send Mail task
                   ElseIf objDTSTask.CustomTaskID = "DTSSendMailTask" Then
                           Set objDTSCustomTask = objDTSTask.CustomTask
                           
                           For Each objDTSProperty In objDTSCustomTask.Properties
                                   objFStream.WriteLine objDTSProperty.name & "=" & objDTSProperty.value
                           Next
     
                   ' FTP task
                   ElseIf objDTSTask.CustomTaskID = "DTSFTPTask" Then
                           
                           For Each objDTSProperty In objDTSTask.Properties
                                   objFStream.WriteLine objDTSProperty.name & "=" & objDTSProperty.value
                           Next
     
                   ' Trasnfer Objects task
                   ElseIf objDTSTask.CustomTaskID = "DTSTransferObjectsTask" Then
                           Set objDTSCustomTask = objDTSTask.CustomTask
                           
                           For Each objDTSProperty In objDTSCustomTask.Properties
                                   objFStream.WriteLine objDTSProperty.name & "=" & objDTSProperty.value
                           Next
     
     
                   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
    Select the code from the previous post and this one, and paste it in an ActiveX Script Task in a DTS. We called it exportDTS.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    And once it's in text files, you can use HD Search and Stats to find what you're looking for easily

    <link removed by gvee>
    Last edited by gvee; 04-29-09 at 11:28.
    George
    Home | Blog

  6. #6
    Join Date
    Jan 2002
    Posts
    77

    thanks

    Thanks for the great ideas guys

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by gvee
    <link removed by gvee>
    McAfee Siteadvisor marks this site with red. Be careful about downloading and installing stuff from them.
    Last edited by gvee; 04-29-09 at 11:28.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It does?

    the program I use has a readme file with the following URL in it: http://www.freebyte.com/harddisk_search_and_stats/ which resolves to the one posted above.

    I have removed the URL from my previous post just in case.
    And obviously, always be careful when downloading and installing any software unless you trust the publisher.
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •