Post reply

SaveMailInSentItemsFolder property value Change All Packages at One Time

  • March 3, 2005 at 4:07 pm

    #86990

    Can someone tell me a way to make a wholesale change of all of our dts packages to reflect the value of "0" instead of "-1" in the SaveMailInSentItemsFolder property value .

    Thanks, Lloyd Langford

     

    To keep DTS from saving the item to the Sent Items folder, every DTS Send Mail Task must be modified in every package to disable the option.

    1.  Open the package

    2.  Click Package...Disconnected Edit

    3.  Select the appropriate Send Mail Task from the list

    4.  Change the SaveMailInSentItemsFolder property value to "0"

    5.  Save the package

  • March 3, 2005 at 4:30 pm

    #545365

    You can do it programmatically using some creative ActiveXScript.

    Here's a script I hacked together to alter the Datapump properties in our packages. It uses SQL-DMO to fetch a list of package names from the sysdtspackages table. The it loads each package, loops through the tasks within the package looking for the datapump tasks, alters the properties and saves the package. There's two MsgBox's that you'll need to remove if you want to run it "hands-off".

    You can just change the script so that it looks for the appropriate task and properties as they're listed in Disconnected edit.

    One important thing to note, editing packages this way will mean that you lose the package layout and any text annotations. If you need to retain these items then you'll have to edit the packages in normal way 

    Option Explicit
Function Main()
 Dim oPkg ' DTS package object
 Dim sPkgName
 Dim oTsk ' DTS task object
 Dim sTskName
 Dim oSrvr ' SQL-DMO server object
 Dim oDb ' SQL-DMO database object
 Dim oQry ' SQL-DMO Query results object
 Dim sSQL ' SQL string to execute
 Dim iRowLoop ' loop counter
 Dim iRowCnt ' number of rows in query results
 Dim iTskLoop
 Dim iTskCnt
 Dim sMsg
 Dim iFetchBuffer
 Dim iCommitSize
 ' build SQL string to retrieve stored global variables using PkgID
 sSQL = "SELECT DISTINCT [name] FROM [msdb].[dbo].[sysdtspackages] "
 sSQL = sSQL & "WHERE [name] LIKE '<pattern matching package name>'"
 ' use SQL-DMO to connect to server and retrieve package names
 Set oSrvr = CreateObject("SQLDMO.SQLServer")
 oSrvr.LoginSecure = True
 oSrvr.Connect "(local)" ' can be replaced with ".", or actual server name
 Set oDb = oSrvr.Databases(msdb)
 Set oQry = oDb.ExecuteWithResults(sSQL)
 iRowCnt = oQry.Rows
 If iRowCnt > 0 Then
  For iRowLoop = 1 To iRowCnt
   sPkgName = oQry.GetColumnString(iRowLoop, 1)
   Set oPkg = CreateObject("DTS.Package")
   oPkg.LoadFromSQLServer sSrvr, "", "", 256, "", "", "", sPkgName
   iTskCnt = oPkg.Tasks.Count
   'Find Task
   For iTskLoop = 1 To iTskCnt
    ' set reference to step in error
    Set oTsk = oPkg.Tasks(iTskLoop).CustomTask
    sTskName = oTsk.Name
    If InStr(1, sTskName, "DataPumpTask") > 0 Then
     iFetchBuffer = oTsk.Properties("FetchBufferSize").Value
     iCommitSize = oTsk.Properties("InsertCommitSize").Value
     sMsg = "Found DataPump in " & sPkgName & " task " & sTskName & vbCrLf
     sMsg = sMsg & "Fetch Buffer Size = " & iFetchBuffer & vbCrLf
     sMsg = sMsg & "Insert Commit Size = " & iCommitSize
     MsgBox  sMsg
     oTsk.Properties("FetchBufferSize").Value = 9000
     oTsk.Properties("InsertCommitSize").Value = 0
    End If
    Set oTsk = Nothing   
   Next
   oPkg.SaveToSQLServer sSrvr, "", "", 256
   Set oPkg = Nothing
  Next
 Else
  MsgBox "Now rows!!"
 End If
 Set oQry = Nothing
 Set oDb = Nothing
 Set oSrvr = Nothing
 Main = DTSTaskExecResult_Success
End Function

     

    --------------------
    Colt 45 - the original point and click interface

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply