Allan Mitchell wrote:
> Hello amyathome@comcast.net,
>
> ShouldILoop should return true then move on. It should not tell you about
> all the possible files in the directory in one go.
>
> Is the Data Pump task enabled?
>
>
>
> Allan Mitchell
> www.SQLDTS.com
> www.SQLIS.com
> www.Konesans.com
>
> > I can see that ShouldILoop returns true because I can see all of the
> > message boxes I set coming up with the appropriate information for
> > each instance.
> >
> > I can even see the message box that tells me it set the execution
> > status for the task but it doesn't appear to perform the data pump
> > task for some reason.
> >
> > Any thoughts as to where I can troubleshoot this?
> >
> > Allan Mitchell wrote:
> >
> >> Hello amyathome@comcast.net,
> >>
> >> So does it go through once?
> >>
> >> If it does then what is the final step in the chain after you have
> >> imported the first file? This is where you should be setting the
> >> loop back to waiting.
> >>
> >> Have you tested the value of ShouldILoop. Maybe ShouldILoop is
> >> returning False and therefore will not loop around.
> >>
> >> Allan
> >>
> >>> Here is the script (I used a lot of it from SQLDTS.com) - I'm hoping
> >>> sometime (maybe Allen Mitchell???) can help me. I'm still having
> >>> the same problem - it seems to set the properties correctly but it
> >>> never loops back around, even though I can see it setting the
> >>> ExecutionStatus.
> >>>
> >>> I've seen a few other threads describing my problem exactly but
> >>> there were no replies??
> >>>
> >>> Function Main()
> >>>
> >>> Dim pkg
> >>> Dim oConnection
> >>> Dim stpEnterLoop
> >>> Dim stpFinished
> >>> Dim tskDataPump
> >>> Dim oDataPump
> >>> set pkg = DTSGlobalVariables.Parent
> >>> set stpEnterLoop = pkg.Steps("DTSStep_TransferClientDataTask")
> >>> set stpFinished = pkg.Steps("DTSStep_DTSActiveScriptTask_2")
> >>> set oConnection = pkg.Connections("Connection 1")
> >>> ' set tskDataPump= pkg.Tasks("DTSTask_TransferClientDataTask")
> >>> set oDataPump =
> >>> pkg.Tasks("DTSTask_TransferClientDataTask").CustomTask
> >>> ' We want to continue with the loop only if there is
> >>> ' one or more text file in the directory. If the function
> >>> ShouldILoop
> >>> ' returns true then we disable the step that takes us out of the
> >>> package
> >>> ' and continue processing
> >>> if ShouldILoop = True then
> >>> stpEnterLoop.DisableStep = False
> >>> stpFinished.DisableStep = True
> >>> ' Set new Filename/tabname
> >>> oConnection.DataSource = DTSGlobalVariables("gv_FileName").Value
> >>> oDataPump.SourceObjectName = "'" &
> >>> DTSGlobalVariables("gv_TableName").Value & "$'"
> >>> msgbox "Filename: " & DTSGlobalVariables("gv_FileName").Value & "
> >>> and Table Name: " & "'" & DTSGlobalVariables("gv_TableName").Value &
> >>> "$'"
> >>> stpEnterLoop.ExecutionStatus = DTSStepExecStat_Waiting
> >>>
> >>> msgbox "Execution status is : " & stpEnterLoop.ExecutionStatus
> >>> 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
> >>> Dim Excel_Application
> >>> Dim Excel_WorkBook
> >>> Dim Excel_WorkSheet
> >>> Dim iSheetCounter
> >>> set pkg = DTSGlobalVariables.Parent
> >>> set fso = CREATEOBJECT("Scripting.FileSystemObject")
> >>> set fold = fso.GetFolder(DTSGlobalVariables("ImportFolder").Value)
> >>> counter = fold.files.count
> >>>
> >>> ' So long as there are text files in the directory carry on
> >>>
> >>> if counter >= 1 then
> >>> 'msgbox "Number of Excel files: " & cstr(counter)
> >>> for each fil in fold.Files
> >>> DTSGlobalVariables("gv_FileName") = fil.Name
> >>> if Ucase(right(DTSGlobalVariables("gv_FileName").value,3)) =
> >>> "XLS" then
> >>> msgbox "We have an Excel file!: " &
> >>> DTSGlobalVariables("gv_FileName").value
> >>> DTSGlobalVariables("gv_FileName").Value = fil.path
> >>> Set Excel_Application = CreateObject("Excel.Application")
> >>> ' Open the workbook specified
> >>> Set Excel_WorkBook = Excel_Application.Workbooks.Open( fil.path)
> >>> ' Find out how many sheets are in the workbook
> >>> iSheetCounter = Excel_WorkBook.WorkSheets.Count
> >>> 'msgbox "Number of Excel tabs: " & cstr(iSheetCounter)
> >>> For Each Excel_WorkSheet in Excel_WorkBook.WorkSheets
> >>> ' Find the WorkSheet specified
> >>> if Excel_Worksheet.RANGE("A1").Value <> "" then
> >>> ' Derive the new table name
> >>> 'msgbox "inner loop-----FileName: " &
> >>> DTSGlobalVariables("gv_FileName") & " Worksheet Name: " &
> >>> Excel_WorkSheet.Name
> >>> DTSGlobalVariables("gv_TableName").Value = Excel_WorkSheet.Name
> >>> ShouldILoop = CBool(True)
> >>> else
> >>> msgbox "Excel file worksheet does not have any values.
> >>> Worksheet: " & DTSGlobalVariables("gv_TableName").Value & " Range:
> >>> "
> >>> & Excel_Worksheet.RANGE("A1").Value
> >>> End if
> >>> Next
> >>> else
> >>> msgbox "This is not an Excel file: " &
> >>> DTSGlobalVariables("gv_FileName").value
> >>> end if
> >>> Next
> >>> else
> >>> ShouldILoop = CBool(False)
> >>> End if
> >>> End Function
> >>> amyathome@comcast.net wrote:
> >>>
> >>>> Thanks so much for the link. I actually looked at this and it
> >>>> indicates setting the executionstatus at the beginning of the loop
> >>>> is key; however, I'm not sure how I can set it at the beginning
> >>>> before I "reset" the source file/table?
> >>>>
> >>>> UnderCover wrote:
> >>>>
> >>>>> Look at this and see if it will help
> >>>>> http://www.sqldts.com/default.aspx?246
> >>>>> "amyathome@comcast.net" wrote:
> >>>>>> Sorry, I should mention (and not assume the reader knows) that
> >>>>>> this is wrapped in a FOR loop using the FileSysObject to grab the
> >>>>>> files within my directory.
> >>>>>>
> >>>>>> amyathome@comcast.net wrote:
> >>>>>>
> >>>>>>> I am not a newbie to SQL Server but a newbie to creating DTS
> >>>>>>> packages. I have a directory which will contain an undetermined
> >>>>>>> number of Excel spreadsheets (with an undetermined amount of
> >>>>>>> tabs within each) that I will have to on a regular basis import
> >>>>>>> into SQL. I have it grabbing the first file, the transformation
> >>>>>>> works great but then it doesn't loop around for the remaining
> >>>>>>> files. The strange thing is, I put msgbox in there to make sure
> >>>>>>> it was actually looping through the files/tabs and it is, and I
> >>>>>>> set the executionstatus right after that, so I'm not sure why