January 28, 2004 at 8:00 am
We're currently using DTS packages that we hold on the local server, however we run these on several sites and use structured storage files to transfer around as well as version control with VSS. To simplify load-up on site I'm considering using structured storage to run the packages from as well. Does anybody have opinions on whether this is a particularly good or bad thing to do and are there any major problems to consider.
All thoughts on this gratefully received.
January 28, 2004 at 11:28 am
I have done both. They seem to work quite nicely either way.
In a structured storage file, there are multiple versions of a package in a single storage file. And there may be multiple logical packages in a single storage file. I learned this recently at a job I finished recently. I am considering using this feature to put multiple packages that are logically together. Try doing that when you save to SQL Server.
What also can happen (this happened to me). I was editing a version that was in a SQL Server. I got it fixed the way I liked it. Then I saved it to the storage file to which it belonged (say, the package is MyPackage on Server1. I saved it to MyPackage.dts). The package already existed, I expect the file to delete and replace with my new version of MyPackage. What it did was to create a new package in MyPackage.dts called MyPackage. This was problematic since my dtsrun script identified the file and the package name. dtsrun gave an error to the effect that it had no clue which MyPackage to use.
Russ Loski, MCSD
Russel Loski, MCSE Business Intelligence, Data Platform
February 17, 2004 at 8:05 am
Well I'm getting the hang of this. If you set a global variable to space and then use dynamic properties to set the version id to that then it will always run the latest version. However, do you know a way to dynamically set the filename in an execute package task?
Julie
February 17, 2004 at 11:21 am
This is rather fun and relatively easy to do:
You need four tasks
ActiveX to get the file list
Dynamic Properties task (step called LoadPackageName to work with the code below)
ActiveX script to get the next item in the list and to exit the loop
These tasks must be linked using a work flow (I lean toward on success).
I hope these will work for you.
Russel Loski, MCSD
' Script to Loop through files base on the file list
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
dim fso
dim oDict
dim oFiles
dim oFile
dim sName
dim sExt
dim iExtPos
' **********************************************************
' **********************************************************
' You can build your list using stored procedures (I do that
' in another project) or even hard code the list. Just put
' your list in the FileList global variable and set the other
' pointers.
' **********************************************************
set fso = createobject ("Scripting.FileSystemObject")
set oDict = createobject("Scripting.Dictionary")
set oFiles = fso.GetFolder("\\MyServer\MyDrive\Jobs").Files
for each oFile in oFiles
sName = oFile.Name
iExtPos = instr(sName, ".")
if iExtPos > 0 then
sExt = lcase(mid(sName, iExtPos + 1))
if sExt = "dts" then
if (mid(sName, 5, 1) = "2" and (mid(sName, 6,1) = "3" or mid(sName, 6, 1) = "4")) then
if oFile.DateLastModified > cdate(DTSGlobalVariables("RunDate").Value) then
oDict (sName) = oFile.Path
end if
end if
end if
end if
next
DTSGlobalVariables("CurrentRow").Value = 0
DTSGlobalVariables("CountFiles").Value = oDict.Count
DTSGlobalVariables("FileList").Value = oDict.Items
set oFiles = nothing
set fso = nothing
set oDict = nothing
if DTSGlobalVariables("CountFiles").Value < 1 then
' This will force the execution to stop if you have a workflow
' based on success.
Main = DTSTaskExecResult_Failure
else
Dim iList
iList = DTSGlobalVariables("FileList").Value
DTSGlobalVariables("DTSPackage").Value = iList( DTSGlobalVariables("CurrentRow").Value)
DTSGlobalVariables("CurrentRow").Value = DTSGlobalVariables("CurrentRow").Value + 1
Main = DTSTaskExecResult_Success
end if
End Function
Dynamic Properties task (step called LoadPackageName)
Run package task
Active x script to restart the loop and go to the next item in the list
' DTS Restart the loop
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
if DTSGlobalVariables("CountFiles").Value > DTSGlobalVariables("CurrentRow").Value then
Dim iList
iList = DTSGlobalVariables("FileList").Value
DTSGlobalVariables("AccessFile").Value = iList( DTSGlobalVariables("CurrentRow").Value)
DTSGlobalVariables("CurrentRow").Value = DTSGlobalVariables("CurrentRow").Value + 1
'***************************************************
' The follow line starts the loop
'***************************************************
DTSGlobalVariables.Parent.Steps("LoadPackageName").ExecutionStatus = DTSStepExecStat_Waiting
else
set DTSGlobalVariables("FileList") = nothing
DTSGlobalVariables.Parent.Steps("Log_Completion").DisableStep = false
end if
Main = DTSTaskExecResult_Success
End Function
Russel Loski, MCSE Business Intelligence, Data Platform
February 18, 2004 at 2:46 am
That's really great, but unfortunately not exactly what I'm after. What I have is an execute package task. The directory that the .dts file is held in is passed as a global variable and can change, the package name and .dts name should be the same so I need to set the filename of the task to <global variable> + package + '.dts'.
I'm just having real trouble getting my head around the object model to do this as I'm only a dabbler in the activeX area.
Julie
February 18, 2004 at 4:51 am
Actually your problem is a lot simpler than I made it.
You need a second global variable. You need one activex script, one dynamic properties task and of course the execute package task.
In the active x script.
DTSGlobalVariables("PackageFullPath").Value = DTSGlobalVariables("PackagePath").Value + DTSGlobalVariables("PackageName").Value + ".dts"
In the dynamic properties task set the FileName property of the Execute properties task to the value in the PackageFullPath global variable and the PackageName to the value in the PackageName global variable.
I hope this helps.
Russ
Russel Loski, MCSE Business Intelligence, Data Platform
February 18, 2004 at 6:00 am
I seem to have found the solution, not as difficult as your first solution but not as simple as the second. I have several packages that are run from the first so each one needs setting.
Anyway this appears to work although I wonder if there may be a slightly more elegant solution.
Julie
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
'***Get the package object for current package
set oPackage = DTSGlobalVariables.Parent
'**** Search through tasks to find the ones that run packages
'**** When the package name is found run the SetFile process to set the full file name
for each oTask in oPackage.Tasks
for each oproperty in otask.properties
if oproperty.name = "PackageName" then
PackageFullPath = DTSGlobalVariables("gvPackagePath").Value + oproperty.Value + ".dts"
setFile oTask, PackageFullPath
End If
next
next
Main = DTSTaskExecResult_Success
End Function
sub SetFile (oTask, PackageFullPath)
'** Set the FileName property to the full file name
for each oproperty in oTask.Properties
if oproperty.name = "FileName" then
oproperty.value = PackageFullPath
end if
next
end sub
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply