January 6, 2010 at 6:30 am
I wrote a DTS pkg a few years ago to search DTS pkgs for an obsolete server name (when we migrated the pkgs from SQL 2000 to new SQL 2000 servers and then again in the migration to SQL 2008). This pkg should only have one script task and a few pkg global variables. It will search connections and pkg log locations for the old server name that you provide.
I don't think it will search/find usage of the old server name in "execute package" tasks however, so keep that in mind. Also, if you protect your pkgs with passwords you'll need to add code for that. We don't use passwords on the DTS pkgs here.
Here is how to create this pkg:
(1) Create a new empty DTS pkg.
(2) Create the following string pkg global variables:
DTSPkgSourceServer - Set this to the server or server\instance which contains the DTS pkgs to search.
DTSPkgNamePattern - Set it to % to search all pkgs. Otherwise set it to a pattern like MyPkgs% to select certain pkgs to search.
ShowProgress - Set it to Y (to get alert box for progress) or N (to get no progress alert). The alert prompts don't seem to work for this any way.
SearchString - Set it to the server name to check (e.g, MyOldServer).
ReportFileSpec - Set it to the name of a file that you want to write the results to (e.g., c:\temp\SearchForMyOldServer.txt).
(3) Create an active X script task using the following code:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Option Explicit
Function Main()
' Initialize variables.
Dim cn
Dim ActiveConnection
Dim ConnectionLoop
Dim TaskLoop
Dim PkgRec
Dim provStr
Dim SelectStr
Dim DTSPkgSourceServer
Dim objPkg
Dim objExecPkgTask
Dim objActiveScriptTask
Dim objCreateProcessTask
Dim objDDQTask
Dim objDataPumpTask
Dim objExecSQLTask
Dim objSendMailTask
Dim TaskName
Dim PkgName
Dim DTSPkgNamePattern
Dim PkgGUID
Dim PkgVersionID
Dim Q
Dim ShowProgress
Dim SearchString
Dim LogServerName
Dim ReportFile
Dim ReportFileSpec
Dim objFSO
Q = "'"
DTSPkgSourceServer=DtsGlobalVariables.Item("DTSPkgSourceServer").Value
DTSPkgNamePattern = DtsGlobalVariables.Item("DTSPkgNamePattern").Value & "%"
ShowProgress = DtsGlobalVariables.Item("ShowProgress").Value
SearchString = DtsGlobalVariables.Item("SearchString").Value
ReportFileSpec = DtsGlobalVariables.Item("ReportFileSpec").Value
Set objFSO = CreateObject("Scripting.FileSystemObject")
If (objFSO is Nothing) Then
'FileSystemobject had problems opening.
Main = DTSTaskExecResult_Failure
Exit function
End If
Set ReportFile = objFSO.CreateTextFile (ReportFileSpec,True)
ReportFile.WriteLine("Report: DTS Pkgs Containing String " & "<" & SearchString & ">")
Set cn = CreateObject("ADODB.Connection")
' Specify the OLE DB provider.
cn.Provider = "sqloledb"
' Specify connection string on Open method.
provStr = "Server=" & DTSPkgSourceServer & ";Database=MSDB;Trusted_Connection=yes"
cn.Open provStr
set PkgRec = CreateObject("ADODB.Recordset")
With PkgRec
Set .ActiveConnection = cn
'.Open "select DISTINCT name,id FROM sysdtspackages where name LIKE " & "'" & DTSPkgNamePattern & "'"
.Open _
"select T1.* from " & _
" dbo.sysdtspackages AS T1 " & _
" INNER JOIN " & _
" (SELECT name, id, MAX(createdate) AS createdate " & _
" from dbo.sysdtspackages " & _
" WHERE name LIKE " & Q & _
DTSPkgNamePattern & Q & _
" AND name NOT LIKE " & Q & "%<%" & Q & _
" AND name NOT LIKE " & Q & "%Staging_Truncate_And_Populate_FILE_CIIS_NATS_STAGING%" & Q & _
" GROUP BY name, id ) AS T2 " & _
" ON T1.id=T2.id and T1.createdate=T2.createdate " & _
" ORDER BY T1.name"
Set ActiveConnection = Nothing
End With
While (Not PkgRec.EOF)
PkgName = PkgRec.Fields("name").Value
PkgGUID = PkgRec.Fields("id").Value
PkgVersionID = PkgRec.Fields("Versionid").Value
Set objPkg = CreateObject("DTS.Package")
'objPkg.LoadFromSQLServer "","","","256",,PkgGUID,PkgVersionID, PkgName
objPkg.LoadFromSQLServer DTSPkgSourceServer,"","","256",,"","", PkgName,-1
LogServerName = objPkg.LogServerName
ReportFile.WriteLine(" ")
If INSTR(1,LogServerName,SearchString,vbTextCompare) >= 1 Then
ReportFile.WriteLine( _
"Pkg " & PkgName & ": " _
& "String <" & SearchString & "> found in Pkg log server name " _
& "<" & LogServerName & ">." )
End If
For Each ConnectionLoop in objPkg.Connections
If UCASE(ConnectionLoop.ProviderId)="SQLOLEDB" _
AND INSTR(1,ConnectionLoop.DataSource,SearchString,vbTextCompare) >= 1 Then
ReportFile.WriteLine( _
"Pkg " & PkgName & ": " _
& "String <" & SearchString & "> found in Connection <" & ConnectionLoop.Name & ">" _
& " with value " & ConnectionLoop.DataSource & ".")
End If
Next
For Each TaskLoop in objPkg.Tasks
If INSTR(1,TaskLoop.Name,"ExecutePackageTask",vbTextCompare) >= 1 Then
'********************************
'DTS Exec pkg task found.
'********************************
Set objExecPkgTask = objPkg.Tasks(TaskLoop.Name).CustomTask
If INSTR(1,objExecPkgTask.ServerName,SearchString,vbTextCompare) >= 1 Then
ReportFile.WriteLine( _
"Pkg " & PkgName & ": " _
& "String <" & SearchString & "> found in ServerName " _
& "<" & objExecPkgTask.ServerName & ">" _
& " in task <" & TaskLoop.Name & ">." )
End If
End If
If INSTR(1,TaskLoop.Name,"ActiveScriptTask",vbTextCompare) >= 1 Then
'********************************
'DTS Active Script task found.
'********************************
Set objActiveScriptTask = objPkg.Tasks(TaskLoop.Name).CustomTask
If INSTR(1,objActiveScriptTask.ActiveXScript,SearchString,vbTextCompare) >= 1 Then
ReportFile.WriteLine( _
"Pkg " & PkgName & ": " _
& "String <" & SearchString & "> found in ActiveXScript " _
& " in task <" & TaskLoop.Name & ">." )
End If
End If
If INSTR(1,TaskLoop.Name,"CreateProcessTask",vbTextCompare) >= 1 Then
'********************************
'DTS Create Process task found.
'********************************
Set objCreateProcessTask = objPkg.Tasks(TaskLoop.Name).CustomTask
If INSTR(1,objCreateProcessTask.ProcessCommandLine,SearchString,vbTextCompare) >= 1 Then
ReportFile.WriteLine( _
"Pkg " & PkgName & ": " _
& "String <" & SearchString & "> found in ProcessCommandLine " _
& "<" & objCreateProcessTask.ProcessCommandLine & "> " _
& " in task <" & TaskLoop.Name & ">." )
End If
End If
If INSTR(1,TaskLoop.Name,"DataPumpTask",vbTextCompare) >= 1 Then
'********************************
'DTS Data Pump task found.
'********************************
Set objDataPumpTask = objPkg.Tasks(TaskLoop.Name).CustomTask
If INSTR(1,objDataPumpTask.DestinationObjectName,SearchString,vbTextCompare) >= 1 Then
ReportFile.WriteLine( _
"Pkg " & PkgName & ": " _
& "String <" & SearchString & "> found in DestinationObjectName " _
& "<" & objDataPumpTask.DestinationObjectName & "> " _
& " in task <" & TaskLoop.Name & ">." )
End If
End If
If INSTR(1,TaskLoop.Name,"SendMailTask",vbTextCompare) >= 1 Then
'********************************
'DTS Data Pump task found.
'********************************
Set objSendMailTask = objPkg.Tasks(TaskLoop.Name).CustomTask
If INSTR(1,objSendMailTask.FileAttachments,SearchString,vbTextCompare) >= 1 Then
ReportFile.WriteLine( _
"Pkg " & PkgName & ": " _
& "String <" & SearchString & "> found in FileAttachments " _
& "<" & objSendMailTask.FileAttachments & "> " _
& " in task <" & TaskLoop.Name & ">." )
End If
End If
Next
objPkg.UnInitialize()
Set ObjPkg = Nothing
PkgRec.MoveNext
Wend
PkgRec.Close
Set PkgRec = Nothing
Set ObjPkg = Nothing
cn.Close
Set cn = Nothing
ReportFile.Close
Set objFSO = Nothing
Set ReportFile = Nothing
Main = DTSTaskExecResult_Success
End Function
(3) Save the pkg with a name like SearchMyDTSPkgs and then execute it. The search parameters are all in the global variables.
Enjoy! I hope it works for you. I tested it to check that it still works in SQL 2008. It should still work in SQL 2000 but I could not test that since I don't have that server any more.
Jeff
December 23, 2010 at 1:05 am
Hi Mike,
I would like to use your utility,it would be great if you could send it on mohitea@mail.dnb.co.in
Thanks in advance for inventing such a utility
waiting for your reply.
Regards,
Ashish
March 9, 2011 at 3:59 am
is there any similar script\tool to get text version for SSIS packages?
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply