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 it's not importing them. Any help would be greatly appreciated
Thanks in advance...

Here is the code snippet (I know it's not in an elegant state at the
moment, just trying to get it to work for now):

if Ucase(right(DTSGlobalVariables("gv_FileName").valu e,3)) = "XLS"
then

oConnection.DataSource = sFileName

Set Excel_Application = CreateObject("Excel.Application")

' Open the workbook specified
Set Excel_WorkBook = Excel_Application.Workbooks.Open(sFilename)

' Find out how many sheets are in the workbook
iSheetCounter = Excel_WorkBook.WorkSheets.Count
msgbox "SheetCount is: " & CStr(iSheetCounter)

For Each Excel_WorkSheet in Excel_WorkBook.WorkSheets
' Find the WorkSheet specified
DTSGlobalVariables("gv_TableName").Value = Excel_WorkSheet.Name
msgbox "Excel file name is: " & sFileName & " " & "Table name is:
" & DTSGlobalVariables("gv_TableName").Value

if Excel_Worksheet.RANGE("A1").Value <> "" then
' Derive the new table names
sSourceTable = DTSGlobalVariables("gv_TableName").Value & "$'"

' Get reference to the DataPump Task
Set oDataPump =
oPkg.Tasks("DTSTask_TransferClientDataTask").Custo mTask

msgbox "Current table name: " & oDataPump.SourceObjectName

' Set the new values
oDataPump.SourceObjectName = sSourceTable

msgbox "NEW table name: " & oDataPump.SourceObjectName

objStartTask.ExecutionStatus = DTSStepExecStat_Waiting

End if
Next
End If

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 (AT) comcast (DOT) net wrote:

Look at this and see if it will help
http://www.sqldts.com/default.aspx?246

"amyathome (AT) comcast (DOT) 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:

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").Custom Task

' 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").V alue)

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").valu e,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 (AT) comcast (DOT) net wrote:

Hello amyathome (AT) comcast (DOT) 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

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 (AT) comcast (DOT) 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

Yes, I checked in Disconnected Edit to be sure that step is enabled and
it is.

What other info can I post about the package to help guide me to the
source of the problem?


Allan Mitchell wrote:

Hello amyathome (AT) comcast (DOT) net,

eMail me your package. You have made a good few additions to the code so
I would like to look at it in the designer it is easier.


Thanks


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com