SaveMailInSentItemsFolder property value Change All Packages at One Time

• hll

  SSC-Addicted

  Points: 470

  More actions

  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

• philcart

  SSC-Forever

  Points: 47797

  More actions

  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