is possible to know where the sql provider of a dts is pointed?

• Hector Angello Silva Carmona

  SSCrazy

  Points: 2720

  More actions

  April 14, 2005 at 2:40 pm

  #65642

  Hello, i need to check using some tsql code or something what is the server that the object Microsoft Ole db provider is pointed in a dts...i s possible know that?

  thanks a lot of...............................

• philcart

  SSC-Forever

  Points: 47794

  More actions

  April 15, 2005 at 1:09 am

  #552856

  You mean you want to find out what server the SQL connection in the DTS package is pointed to? You can't do this in T-SQL because the package is stored as a binary object.

  You could write a short ActiveX Script to open each package in turn and write the connection properties to a text file. I use this script to set the package logging properties for all my packages. It should give you a good start.

  Option Explicit
  '**********************************************************************
  '  Visual Basic ActiveX Script
  ' NOTE: THis task should be run manually as it contains MsgBox calls
  '************************************************************************
  Const TRUSTED_CONN = 256
  Const SERVER_NAME = "<YOUR SERVER>"
  Const DATABASE_NAME = "msdb"

  Function Main()
   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 oPkg ' DTS package object
   Dim iRowLoop ' loop counter
   Dim iRowCnt ' number of rows in query results
   Dim iSavCnt ' Number of successfully saved pkgs
   Dim iErrNum ' Error number
   Dim sErrDesc ' Error description
   Dim sMsg ' message string
   Dim sPkgname ' DTS Package name

   ' build SQL string to retrieve stored global variables using PkgID
   sSQL = "SELECT DISTINCT [name] FROM [msdb].[dbo].[sysdtspackages] "
   sSQL = sSQL & "WHERE [name] NOT LIKE '%___%'"
   ' use SQL-DMO to connect to server and retrieve stored global variables
   Set oSrvr = CreateObject("SQLDMO.SQLServer")
   oSrvr.LoginSecure = True
   oSrvr.Connect "SERVER_NAME"
   Set oDb = oSrvr.Databases(DATABASE_NAME)
   Set oQry = oDb.ExecuteWithResults(sSQL)
   iRowCnt = oQry.Rows
   If iRowCnt > 0 Then
    iSavCnt = 0
    For iRowLoop = 1 To iRowCnt
     sPkgName = oQry.GetColumnString(iRowLoop, 1)
     Set oPkg = CreateObject("DTS.Package")
     oPkg.LoadFromSQLServer SERVER_NAME, "", "", TRUSTED_CONN, "", "", "", sPkgName
     oPkg.LogToSQLServer = True
     oPkg.LogServerName = SERVER_NAME
     oPkg.LogServerFlags = TRUSTED_CONN   On Error Resume Next
     oPkg.SaveToSQLServer SERVER_NAME, "", "", TRUSTED_CONN
     iErrNum = Err.Number
     iErrDesc = Err.Description
     On Error Goto 0
     If iErrNum = 0 Then
      iSavCnt = iSavCnt + 1
     Else
      sMsg = "Error saving package " & sPkgName & vbCrLf
      sMsg = sMsg & "Error: " & CStr(iErrNum) & " - " & sErrDesc
      MsgBox sMsg
     End If
     Set oPkg = Nothing
    Next
    sMsg = "Successfully processed " & CStr(iSavCnt) & " of "
    sMsg = sMsg & CStr(iRowCnt) & " packages."
    MsgBox sMsg
   Else
    MsgBox "Now rows to process!! Check SQL statement at begining of script."
   End If
   Set oQry = Nothing
   Set oDb = Nothing
   Set oSrvr = Nothing
   Main = DTSTaskExecResult_Success
  End Function
  

   

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