Re: Execute more than one package

#1

"Ed" <Ed (AT) discussions (DOT) microsoft.com> wrote

Quote:

Hi,
I need to run a dts that includes 13 others dts package. I paste the
following code, please advice if this is a good way to do it. Also, I am
not
sure if i need to unintialize the DTS.Package everytime?
Thanks
Ed

const DTSSQLStgFlag_UseTrustedConnection = 256
Dim myNum
Dim myPackagename(13)

myNum=0
myPackagename(0)="ImportMJERevenueMap"
myPackagename(1)="ImportPayerRevenueMap"
myPackagename(2)="ImportSoldToRevenueMap"
myPackagename(3)="ImportRevenue"
myPackagename(4)="ImportExpense"
myPackagename(5)="ImportDepreciation"
myPackagename(6)="ImportFSO_INST"
myPackagename(7)="ImportFSO_MTCE"
myPackagename(8)="ImportLeasePayment"
myPackagename(9)="ImportNonTelco"
myPackagename(10)="ImportOPSMatrics"
myPackagename(11)="ImportOutTasking"
myPackagename(12)="ImportProfitCenter"
myPackagename(13)="ImportTelcoReport"

Do While (myNum< 14)
MsgBox "Begin process..." +myPackagename(myNum)
Set oPKG = CreateObject("DTS.Package")

oPKG.LoadFromSQLServer

"ServerName","","",DTSSQLStgFlag_UseTrustedConnect ion,"","","",myPackagename

(myNum)

Quote:

oPKG.Execute
'MsgBox myPackagename(myNum)
oPKG.Uninitialize()
Set oPKG = Nothing
myNum = myNum + 1
Loop
Main = DTSTaskExecResult_Success

It should work, so nothing wrong with. Some personal observations, firstly I
would be tempted to drive this from a SQL table, as oppose to coding the
list inside the task or package. Use an Exec SQL Task to query the table,
and store the result rowset in a global variable. You can then just loop the
global variable recordset instead of the array. There is no error checking,
which may or may not be a problem. I would leave it like this and check for
errors afterwards. One nice way is to ensure all child packages have the log
to SQL Server option set, then just query the log tables looking for errors
afterwards, perhaps in another Exec SQL Task in your package and handle it
from there, but this really depends on your process and requirements.

--
Darren Green
http://www.sqldts.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Execute more than one package

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Re: Execute more than one package - 08-17-2004 , 10:11 AM