Help with executing child package

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Help with executing child package

Author

Topic

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2006-11-20 : 14:57:07

Hello Guys -

I have this script that is part of a master dts that executes other child packages. This activex step of the master dts sends the date variables used on the child packages and execute them.
For some odd reason, when I run the activex by itself, it says it runs successfully but the child package doesn't run successfully/at all.

I have this same script for another type of process that passes name of clients instead of dates, could this be somehow related to the issue ?

'**********************************************************************
' Visual Basic ActiveX Script
'**********************************************************************

Function Main()
Dim objTodaysDate
Dim objYesterdaysDate
Dim objChildPackage

'Create local ADO Recordset
' Set objTodaysDate = DTSGlobalVariables("TodaysDate").Value

'Create child package and load from SQL Server
Set objChildPackage = CreateObject("DTS.Package")
objChildPackage.LoadFromSQLServer "SERVER1", "USER1", "Pa$$w0rd", DTSSQLStgFlag_Default,"","","","Child_DTS_Import_Package"

'Loop through recordset
' Do While not objTodaysDate.eof
' Do While not objYesterdaysDate.eof
' Msgbox DTSGlobalVairables("TodaysDate").Value

'Pass global variable to child package and execute
objChildPackage.GlobalVariables.Item("TodaysDate") = DTSGlobalVariables("TodaysDate").Value
objChildPackage.GlobalVariables.Item("DateToImport") = DTSGlobalVariables("TodaysDate").Value

objChildPackage.Execute

'Move to next record

' objYesterdaysDate.MoveNext
' objTodaysDate.MoveNext
' Loop
' Loop

'Destroy object
objChildPackage.UnInitialize
Set objChildPackage = Nothing
Set objTodaysDate = Nothing
Set objYesterdaysDate= Nothing

'Return Success
Main = DTSTaskExecResult_Success

End Function

---
"There's no sexy way to carry a lunch bag." My boss

X002548
Not Just a Number

15586 Posts

Posted - 2006-11-20 : 15:00:56

Wouldn't you be better served by using bcp or bulk insert, then use t-sql?

It'll be much fatser

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2006-11-21 : 09:27:28

X002548,
Not sure exactly what you mean. I used a master DTS to pass variables down to a bunch of DTSs.

I found the issue, nothing is wrong with the script, the issue was in the child package that wasn't finding the file to load, but not error messages were fired up. Now, how can I implement this same script to give me error messages. Even though the child package failed, the activex step didn't fail to me.

---
"There's no sexy way to carry a lunch bag." My boss

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-11-22 : 08:33:59

SQLDts.com is (unsurprisingly!) a good resource for this kind of thing. The following link includes an example of testing a child package for errors: [url]http://www.sqldts.com/default.aspx?215[/url].

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources