July 2, 2002 at 3:00 pm
Setting:
1. Sql 2K
2. DTS Package A calls DTS Package B using the DTS “Execute Package task”
3. I have set an “inner global variable” in Package A and Package B uses this setting successfully.
What I want to do, though, is to call package B in a loop with a global variable that is incremented in Package A. Can I do this using “inner” variables or do I need to use an “Outer global variables”?
TIA,
Bill
July 11, 2002 at 6:59 pm
Using an ActiveX task...set up variables to reference each task object (a and b) and then set your package b global variable equal to package a global variable inside your loop then execute package b...here is an example...
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim responsesuccess
responsesuccess = 0
responsesuccess = ImportFile(DTSGlobalVariables("gvfilepath").Value)
If responsesuccess <> 0 Then
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If
End Function
Function ImportFiles(filepath, fileext)
'
'Accepts: filepath - string variable to hold full path to file (pass in global variable "gvfilepath")
'fileext - global variable holding extension of files we are looking to import, eg. ".asc"
'
'Returns: 1 if successful
' 0 if fails.
ImportFiles = 0
Dim fso
Dim objFolder
Dim colFiles
Dim objFile
Dim objPackage
Dim objConn
Dim i'counter used for test purposes
blnLogIntotblCDRImportBatch = 1
Set objPackage = CreateObject("DTS.Package")
objPackage.LoadFromSQLServer "your server name here","","","256",,,,"name of package that does the actual import of the file"
Set fso = CreateObject("Scripting.FileSystemObject")
Set objfolder = fso.GetFolder(filepath)
Set colFiles = objfolder.Files
i = 0
If colfiles.Count > 0 Then
For Each objfile in colfiles
If UCase(Left(objfile.name,2)) = "XX" Then
If UCase(Right(objFile.name,4)) = ".ASC" Then
'Check file size - if greater than 0 then import to db table:
If objFile.Size > 0 Then
'Run package here and load table:
objPackage.GlobalVariables.Item("gvcurrentimportfile").Value = filepath & "\" & objfile.Name
objPackage.GlobalVariables.Item("gvfileid").Value = i
Set objConn = objPackage.Connections("Connection 1")
objConn.DataSource = objPackage.GlobalVariables.Item("gvcurrentimportfile").Value
objPackage.Execute
i = i + 1
End If
End If
End If
Next
End If
'MsgBox "Total Files Processed: " & Cstr(i)
objPackage.Uninitialize()
Set objPackage = Nothing
Set fso = Nothing
Set objfolder = Nothing
Set colFiles = Nothing
Set objFile = Nothing
Set objConn = Nothing
ImportFiles = 1
End Function 'ImportFiles()
Michael Weiss
Michael Weiss
July 11, 2002 at 7:00 pm
Sorry...should have added that you will need to modify this code to do what you want...
quote:
Using an ActiveX task...set up variables to reference each task object (a and b) and then set your package b global variable equal to package a global variable inside your loop then execute package b...here is an example...'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim responsesuccess
responsesuccess = 0
responsesuccess = ImportFile(DTSGlobalVariables("gvfilepath").Value)
If responsesuccess <> 0 Then
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If
End Function
Function ImportFiles(filepath, fileext)
'
'Accepts: filepath - string variable to hold full path to file (pass in global variable "gvfilepath")
'fileext - global variable holding extension of files we are looking to import, eg. ".asc"
'
'Returns: 1 if successful
' 0 if fails.
ImportFiles = 0
Dim fso
Dim objFolder
Dim colFiles
Dim objFile
Dim objPackage
Dim objConn
Dim i'counter used for test purposes
blnLogIntotblCDRImportBatch = 1
Set objPackage = CreateObject("DTS.Package")
objPackage.LoadFromSQLServer "your server name here","","","256",,,,"name of package that does the actual import of the file"
Set fso = CreateObject("Scripting.FileSystemObject")
Set objfolder = fso.GetFolder(filepath)
Set colFiles = objfolder.Files
i = 0
If colfiles.Count > 0 Then
For Each objfile in colfiles
If UCase(Left(objfile.name,2)) = "XX" Then
If UCase(Right(objFile.name,4)) = ".ASC" Then
'Check file size - if greater than 0 then import to db table:
If objFile.Size > 0 Then
'Run package here and load table:
objPackage.GlobalVariables.Item("gvcurrentimportfile").Value = filepath & "\" & objfile.Name
objPackage.GlobalVariables.Item("gvfileid").Value = i
Set objConn = objPackage.Connections("Connection 1")
objConn.DataSource = objPackage.GlobalVariables.Item("gvcurrentimportfile").Value
objPackage.Execute
i = i + 1
End If
End If
End If
Next
End If
'MsgBox "Total Files Processed: " & Cstr(i)
objPackage.Uninitialize()
Set objPackage = Nothing
Set fso = Nothing
Set objfolder = Nothing
Set colFiles = Nothing
Set objFile = Nothing
Set objConn = Nothing
ImportFiles = 1
End Function 'ImportFiles()
Michael Weiss
Michael Weiss
Michael Weiss
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply