Purpose: Parent package that can be easily created for each of our
clients data.
- sets global variables here and pass to child
- sets create table
- sets connection 1, usually text file
Child package designed to loop through any of our
clients data.(Would NOT Change)
- loops through files
- receives information from parent
I have two packages successfully talking to each other but I can not
make this versital and look at different client data. I can make all
the changes in the parent package and pass the information to the child
package but the DestinationObjectName and SourceObjectName do not
change and it fails.

By msgboxes I know I am passing and setting the information correctly
to the child package but the child package's datasource,
sourceobjectname and destinationobjectname does not change.

Child package code is in a few ActiveX scripts. I've include what I
think will be helpful

For those familiar, you may recognize code for looping from SQLDTS.com.

I could do a looping package for each client but it is time consuming
and difficult for other people to work with which is why I wanted a two
package approach. The parent package is easy...
If you need more info please let me know.

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

Option Explicit
Function Main()

dim fso
dim fold
dim stpContinuePkg
dim stpExitbadDirectory

dim pkg
dim oDataPump
dim desttable
Dim sSourceText

Set pkg = DTSGlobalVariables.Parent

desttable = DTSGlobalVariables("gv_DestinationTable").Value
sSourceText = DTSGlobalVariables("gv_FullFileName").Value

Set oDataPump = pkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask

oDataPump.SourceObjectName = sSourceText
oDataPump.DestinationObjectName = desttable

SET stpContinuePkg = pkg.Steps("DTSStep_DTSActiveScriptTask_3")
SET stpExitBadDirectory = pkg.Steps("DTSStep_DTSActiveScriptTask_2")

DTSGlobalVariables("gv_FileCheckErrors").Value = ""

SET fso = CREATEOBJECT("Scripting.FileSystemObject")


'Here we check to make sure the Source folder for the files exists

if fso.FolderExists(DTSGlobalVariables("gv_FileLocati on").Value) <>
"True" then
DTSGlobalVariables("gv_FileCheckErrors").Value = CSTR
(DTSGlobalVariables("gv_FileCheckErrors").Value) &_
" " & "Source File directory Not Found"
end if

'Here we check to make sure the Archive folder for the files exists

if fso.FolderExists(DTSGlobalVariables("gv_ArchiveLoc ation").Value)
<> "True" then
DTSGlobalVariables("gv_FileCheckErrors").Value =
CSTR(DTSGlobalVariables("gv_FileCheckErrors").Valu e) &_
" " & "Archive File directory Not Found"
end if


If len(DTSGlobalVariables("gv_FileCheckErrors").Value ) > 2 Then
stpContinuePkg.DisableStep = True
stpExitBadDirectory.DisableStep = False
Else
stpContinuePkg.DisableStep = False
stpExitBadDirectory.DisableStep = True

end if

Main = DTSTaskExecResult_Success
End Function

ActivexScript 2
Function Main()

dim pkg
dim conTextFile
dim stpEnterLoop
dim stpFinished
dim oDataPump

SET pkg = DTSGlobalVariables.Parent

SET stpEnterLoop = pkg.Steps("DTSStep_DTSDataPumpTask_1")
SET stpFinished = pkg.Steps("DTSStep_DTSActiveScriptTask_5")
SET conTextFile = pkg.Connections("Text File (Source)")


IF ShouldILoop = True then
stpEnterLoop.DisableStep = False
stpFinished.DisableStep = True
conTextFile.DataSource = DTSGlobalVariables("gv_FileFullName").Value
msgbox "go to data pump"
stpEnterLoop.ExecutionStatus = DTSStepExecStat_Waiting
else
stpEnterLoop.DisableStep =True
stpFinished.DisableStep = False
stpFinished.ExecutionStatus = DTSStepExecStat_Waiting
End if

Main = DTSTaskExecResult_Success
End Function


Function ShouldILoop

dim fso
dim fil
dim fold
dim pkg
dim counter

set pkg = DTSGlobalVariables.Parent
set fso = CREATEOBJECT("Scripting.FileSystemObject")

set fold = fso.GetFolder(DTSGlobalVariables("gv_FileLocation" ).Value)

counter = fold.files.count

IF counter >= 1 then
FOR EACH fil in fold.Files
DTSGlobalVariables("gv_FileFullName").Value = fil.path
ShouldILoop = CBool(True)
Next

else
ShouldILoop = CBool(False)
End if

End Function

Fails AT this point. It never makes it to data transformation process.
Let me know if you need more info.
Jen

The structure of the source and destination can obviously be different
but the old source table must be the same structure as the new one, and
the same applies for the destination table.

I found this on SQLDTS.com. Is there a way aroud this?


jennifer_kostuch (AT) yahoo (DOT) com wrote: