July 15, 2003 at 10:25 am
We have 170 DTS packages on a SQL Server and i was wondering whether anyone knew of a "quick" way of interogating all of them to find out what DB's they access. Rather than opening up each individual one and searching through them. All responses are greatfullly appreciated as otherwise it will take until the next milennia to do!
July 18, 2003 at 8:00 am
This was removed by the editor as SPAM
July 21, 2003 at 5:57 am
Hi,
Sorry for the length of the post - but I found it necessary to include part of actual code.
I was thinking about writing an article based on my code but could not find time in the recent weeks to actually do it.
I have used in the past a script that I applied to the packages written by my team - myself and two other people.
I new what kind of task we were using and I was generating a text file which contain the information I needed,
and I needed to know almost everything about Packages which contain the following tasks:
ExecuteSQL
DataDrivenQuery
SendMail
ExecutePackage
DataPump
ActiveScript
FTPTask
Script is run once per each package and generates the text file - it's then easy to search text files for what you want using any
programmer's editor.
Actually the text file generated was sort of side effect of other things that I used to do inside my script - like automated editing or
generating Structure Storage files for placing them to Visual Source Safe
If you use or expect other tasks to be present in your packages - you need to use same approach.
As general concept:
I used VBScript within ther DataDrivenQuery task in a package that I created to do the job.
At source I created an excel spreadsheet (you can use SQL Table or flat file as long as you define the structure to hold the packages names - and any other info you need to know to use in your script LoadFromSqlServer method to load the package.
You mention on the server so this will do - oherwise you will use other method to load packages from somewhere else.
Inside the script I create the text file which filename corresponds to package name, into which I write the information I am interested in: about the package its properties, collections and individual tasks, for which I wrote the code in my script.
I used the package with this DDQ task to run it against a set of 30 packages - which apart from generting info in the text file, was editing the package in an automated fashion based on additional information in my source excel file and saving the new version of the packages back to SQL Server or as files Structured Storage Files (with extension DTS). Such run took approx 2-3 minutes - each package had on average 8-12 tasks.
I do not quote below all the details - but you will have an idea what needs to be done. If you are interested only and only at the DB
being used - than you can limit your digging deep into DTS object model.
You may still need to examine apart from the connections collection - ExecuteSQLTask, DatsDrivenQueryTask, DataPumpTask, DynamicPropertiesTask, ExecutePackageTask
I will provide below some specific examples for some of these tasks.
Create your output text file:
set fs = CreateObject( "Scripting.FileSystemObject" )
strFileOutSrce = strPath2Files & "" & RTRIM(DTSSource("PackageName")) & ".TXT"
' delete any existing output files
fs.DeleteFile strFileOutSrce
' create output file
Set fileOutSrce = fs.CreateTextFile(strFileOutSrce)
Read your source table record
.....
Get the necessary parameters you may need to set up
....
Load each package to memory:
Set objPackage = CreateObject("DTS.Package2")
' assuming no passwords - and you defined all necessary variables from your source record
' I found if I declare the variable 'pUnkPersistStgOfHost' eg somewhere above DIM pUnkPersistStgOfHost
' and do not assign any value to it the statements below will work
IF intDTSSQLStgFlag = DTSSQLStgFlag_UseTrustedConnection THEN
objPackage.LoadFromSQLServer strServerName, , , intDTSSQLStgFlag,,,, strPackageName, pUnkPersistStgOfHost
ELSE
objPackage.LoadFromSQLServer strServerName, strServerUserName, strServerPassword, intDTSSQLStgFlag,,,, strPackageName, pUnkPersistStgOfHost
END IF
'-----------------------------------
Traverse the Package object - its:
properties
'-----------------------------------------------------
For each objProperty in objPackage.Properties
strName= objProperty.Name
lngType= objProperty.Type
blnGet = objProperty.Get
blnSet = objProperty.Set
IF blnGet THEN
varValue = objProperty.Value
ELSE
varValue = "<< Cannot be read >>"
END IF
fileOutSrce.WriteLine WritePackagePropertyLines( _
strName, _
lngType, _
blnGet, _
blnSet, _
varValue _
)
NEXT
'-----------------------------------------------------
collections (especially connections collection) - as Tasks using connections refer to it by ConnectionID to the Connections
eg
SourceConnectionID, DestinationConnectionID in DataDrivenQueryTask,
or
ConnectionID in ExecuteSQL Task
SourceQueryConnectionID in DynamicPropertiesTaskAssignment element of the DynamicPropertiesTaskAssignments collection of
the DynamicPropertiesTask
'-----------------------------------------------------
For each objConnection in objPackage.Connections
strName = objConnection.Name
strDescription = objConnection.Description
intID = objConnection.ID
strDataSource = objConnection.DataSource
strCatalog = objConnection.Catalog
strProviderID = objConnection.ProviderID
strUserID = objConnection.UserID
blnUseTrustedConnection = objConnection.UseTrustedConnection
strLastOwnerTaskName = objConnection.LastOwnerTaskName
intConnectionTimeOut = objConnection.ConnectionTimeOut
fileOutSrce.WriteLine WriteConnectionsLines( _
strName, _
strDescription, _
intID, _
strDataSource, _
strCatalog, _
strProviderID, _
strUserID, _
blnUseTrustedConnection, _
strLastOwnerTaskName, _
intConnectionTimeOut _
)
fileOutSrce.WriteLine WriteFooterConnections(strPackage)
NEXT
'-----------------------------------------------------
' Traverse Global Variables
'-----------------------------------------------------
For each objGlobalVariable in objPackage.GlobalVariables
Set objGVProperties = objGlobalVariable.Properties
Set objGVPropertyName = objGVProperties("Name")
Set objGVPropertyValue = objGVProperties("Value")
strName = objGVProperties("Name").Value
strValue = objGVProperties("Value").Value
fileOutSrce.WriteLine WriteGlobalVariablesLines( _
strName, _
strValue _
)
Next
'-----------------------------------------------------
' then comes time to examine the tasks:
'-----------------------------------------------------
For Each objTask in objPackage.Tasks
' here follows example what I was doing with DynamicPropertiesTask -'
' I copied as it is - and you can see that I was not only writing to text file what I know about the task but - if needed I was updating the
' task ( this was based on other parameters in my source excel file) and saving a new version of the edited package later on
'-----------------------------------------------------
IF LEFT(objTask.Name, 33) = "DTSTask_DTSDynamicPropertiesTask_" THEN
fileOutSrce.WriteLine WriteTaskHeader(strPackageName, objTask.Name, objTask.Description)
set objCustomTask = objTask.CustomTask
Set DynamicPropertiesTaskAssignments = objCustomTask.Assignments
ka = 0
For each TaskAssignment in DynamicPropertiesTaskAssignments
' write as we speak
IF ka= 0 THEN
fileOutSrce.WriteLine WriteDynamicPropertiesTaskAssignmentHeader(objTask.Name, DynamicPropertiesTaskAssignments.Count)
END IF
strDestinationPropertyID = TaskAssignment.DestinationPropertyID
strSourceConstantValue = TaskAssignment.SourceConstantValue
strSourceDataFileFileName = TaskAssignment.SourceDataFileFileName
strSourceEnvironmentVariable = TaskAssignment.SourceEnvironmentVariable
strSourceGlobalVariable = TaskAssignment.SourceGlobalVariable
strSourceIniFileFileName = TaskAssignment.SourceIniFileFileName
strSourceIniFileKey = TaskAssignment.SourceIniFileKey
strSourceIniFileSection = TaskAssignment.SourceIniFileSection
intSourceQueryConnectionID = TaskAssignment.SourceQueryConnectionID
strSourceQuerySQL = TaskAssignment.SourceQuerySQL
intSourceType = TaskAssignment.SourceType
fileOutSrce.WriteLine WriteDynamicPropertiesTaskAssignmentLines( _
strDestinationPropertyID , _
intSourceType , _
DefineDTPSourceType(intSourceType), _
strSourceConstantValue , _
strSourceDataFileFileName , _
strSourceEnvironmentVariable, _
strSourceGlobalVariable , _
strSourceIniFileFileName , _
strSourceIniFileKey , _
strSourceIniFileSection , _
intSourceQueryConnectionID , _
strSourceQuerySQL _
)
fileOutSrce.WriteLine strDTP_ASS_SEPARATOR
ka = ka + 1
'change if necessary assignment which is based on Query
IF blnCommitChange THEN
IF intSourceType = DTSDynamicPropertiesSourceType_Query THEN
' STOP
strText2Change = TaskAssignment.SourceQuerySQL
TaskAssignment.SourceQuerySQL = GetNewValue(strText2Change, arrStrFindRepl, strNeverReplace)
END IF
IF intSourceType = DTSDynamicPropertiesSourceType_Constant THEN
'STOP
strConstValue =TaskAssignment.SourceConstantValue
'intVar = VarType(varValue)
'IF intVar <> vbNull and intVar <> vbEmpty THEN ' as otherwise CSTr() will end up in run-time error
strText2Change = CStr(strConstValue) ' it is string anyway
strText2Change = GetNewValue(strText2Change, arrStrFindRepl, strNeverReplace)
objGVPropertyValue.Value = strText2Change
END IF
IF intSourceType = DTSDynamicPropertiesSourceType_GlobalVariable THEN
strGVName = TaskAssignment.SourceGlobalVariable
For each objGlobalVariable in objPackage.GlobalVariables
Set objGVProperties = objGlobalVariable.Properties
Set objGVPropertyName = objGVProperties("Name")
Set objGVPropertyValue = objGVProperties("Value")
strName = objGVProperties("Name").Value
' if the global variable is this one
IF objGVPropertyName.Value = strGVName THEN
' we do not want to change a name of the global variable being used - but just the value of it
varValue = objGVProperties("Value").Value ' value of that global var
intVar = VarType(varValue)
IF intVar <> vbNull and intVar <> vbEmpty THEN ' as otherwise CSTr() will end up in run-time error
strText2Change = CStr(varValue)
strText2Change = GetNewValue(strText2Change, arrStrFindRepl, strNeverReplace)
blnUpdateDPT = TRUE
SELECT CASE intVar
CASE vbString var2Write = CStr(strText2Change)
CASE vbInteger var2Write = CInt(strText2Change)
CASE vbSingle var2Write = CSng(strText2Change)
CASE vbDouble var2Write = CDbl(strText2Change)
CASE vbDate var2Write = CDate(strText2Change)
CASE vbBoolean var2Write = CBool(strText2Change)
CASE vbCurrency var2Write = CCur(strText2Change)
CASE ELSE blnUpdateDPT = FALSE
END SELECT
IF blnUpdateDPT THEN
objGVPropertyValue.Value = var2Write
END IF
END IF
END IF
NEXT
END IF
END IF
NEXT
IF ka > 0 THEN
fileOutSrce.WriteLine WriteFooterDynamicPropertiesTask(objTask.Name, objTask.Description)
fileOutSrce.WriteLine strPKG_ITEM_SEPARATOR
END IF
END IF
'-----------------------------------------------------------------
'another extract about DataDrivenQueryTask
'-----------------------------------------------------
IF LEFT(objTask.Name, 31) = "DTSTask_DTSDataDrivenQueryTask_" THEN
fileOutSrce.WriteLine WriteTaskHeader(strPackageName, objTask.Name, objTask.Description)
set objCustomTask = objTask.CustomTask
Set taskTransforms = objCustomTask.Transformations
'strTrName = objTask.Transformations.Item(1).Name
kt = 0 ' normally in my DDQtasks would be only one transform objct in the collection - but it handles multiple transform
FOR each objTransform in taskTransforms
' strobjTransform = objTransform.Name - should be a name of the transformation
IF kt = 0 THEN
fileOutSrce.WriteLine WriteTransformationsHeader(objTransform.Name)
END IF
SET taskTransfSrceCols = objTransform.SourceColumns
SET taskTransfDestCols = objTransform.DestinationColumns
fileOutSrce.WriteLine WriteTransformationsLines(objTransform.Name , _
objTransform.TransformServerProperties("Language") , _
objTransform.TransformServerProperties("FunctionEntry"), _
taskTransfSrceCols.Count, _
taskTransfDestCols.Count _
)
fileOutSrce.WriteLine objTransform.TransformServerProperties("Text")
FOR jc = 1 TO taskTransfSrceCols.Count
IF jc = 1 THEN
fileOutSrce.WriteLine WriteTransformationsSourceColumnsHeader()
END IF
fileOutSrce.WriteLine WriteTransformationsColumnsLines( _
taskTransfSrceCols.Item(jc).Name , _
taskTransfSrceCols.Item(jc).Ordinal , _
taskTransfSrceCols.Item(jc).ColumnID , _
taskTransfSrceCols.Item(jc).DataType , _
taskTransfSrceCols.Item(jc).Flags , _
taskTransfSrceCols.Item(jc).Nullable , _
taskTransfSrceCols.Item(jc).NumericScale, _
taskTransfSrceCols.Item(jc).Precision , _
taskTransfSrceCols.Item(jc).Size _
)
fileOutSrce.WriteLine strPKG_ITEM_SEPARATOR
NEXT
FOR jc = 1 TO taskTransfDestCols.Count
IF jc = 1 THEN
fileOutSrce.WriteLine WriteTransformationsDestinColumnsHeader()
END IF
fileOutSrce.WriteLine WriteTransformationsColumnsLines( _
taskTransfDestCols.Item(jc).Name , _
taskTransfDestCols.Item(jc).Ordinal , _
taskTransfDestCols.Item(jc).ColumnID , _
taskTransfDestCols.Item(jc).DataType , _
taskTransfDestCols.Item(jc).Flags , _
taskTransfDestCols.Item(jc).Nullable , _
taskTransfDestCols.Item(jc).NumericScale, _
taskTransfDestCols.Item(jc).Precision , _
taskTransfDestCols.Item(jc).Size _
)
fileOutSrce.WriteLine strPKG_ITEM_SEPARATOR
NEXT
kt = kt + 1
NEXT
IF kt > 0 THEN
fileOutSrce.WriteLine WriteFooterTransformations(objTask.Name, objTask.Description)
fileOutSrce.WriteLine strPKG_ITEM_SEPARATOR
END IF
END IF
'----------------------------------
'for examining lookups collections
set objCustomTask = objTask.CustomTask
Set taskLookups = objCustomTask.Lookups
ic = 0
FOR each objLookup in taskLookups
IF ic =0 THEN
fileOutSrce.WriteLine WriteLookUpsHeader(strPackageName, objTask.Name, objTask.Description)
END IF
strLookUpName= objLookup.Name
intConnectionID= objLookup.ConnectionID
intMaxCacheRows= objLookup.MaxCacheRows
strLookUpQuery = objLookup.Query
fileOutSrce.WriteLine WriteLookUpsLines( strLookUpName, _
intConnectionID, _
intMaxCacheRows _
)
fileOutSrce.WriteLine strLookUpQuery
fileOutSrce.WriteLine WriteLookupsFooter(strLookUpName)
ic = ic + 1
NEXT
NEXT
' cleaning up
.....
' close the text file
fileOutSrce.Close
--------------------------------------------------------
You get the idea. You can expand it or limit it to what you want.
Ask if you need any more clarification or details.
The VBScript code extracts are from the real life working package
and I can provide example of the text files output of this code.
If somebody is interested.
For clarity I omitted on purpose any code which actually writes to the text file
i.e functions referenced above like
WritePackagePropertyLines()
WriteConnectionsLines()
WriteLookUpsLines()
WriteLookupsFooter()
and other
I hope that it will be readable as the preview mode only show the beginning of the long post.
And I did not have too much time to review and edit the text.
- Tom.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply