aneilg
asked on
ssis for loop
I’ve got a for loop and inside the loop I am looking for a file, there will only ever be one file each day. The problem is my is currently set to true == true. So it keeps looping. Basically how to I set a condition to say loop every 10 minutes and only loop until the file is found. So counter =1.
ASKER
Coolio
The sleeper works fine thanks.
But I cannot get my loop to stop. I’ve got a variable called file_loaded int32 = 0
My loop editor initexpression = @file_loaded = 0
Evalexpression = @file_loaded >=1
Then in my code I have a if file exisits, if so do my thing then Dts.Variables("File_Loaded ").Value = 1
But my foor loop just stays green
The sleeper works fine thanks.
But I cannot get my loop to stop. I’ve got a variable called file_loaded int32 = 0
My loop editor initexpression = @file_loaded = 0
Evalexpression = @file_loaded >=1
Then in my code I have a if file exisits, if so do my thing then Dts.Variables("File_Loaded
But my foor loop just stays green
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Public Sub Copy_File()
Dim sDateString As String
Dim sDate As String
Dim sPath As String
Try
strExpression = Dts.Variables("File_Loaded ").Value.T oString
'sPath = strFolder & strPrefix
'MsgBox(strExpression)
If FileOrDirExists(sPath) Then
'MsgBox(sPath)
getDatFileName = strPrefix & sDate & ".xls"
FileCopy(FromPath & "\" & getDatFileName, ToPath & "\" & getDatFileName)
'strExpression = True
'MsgBox(strExpression)
Dts.Variables("File_Loaded ").Value = 1
Else
'MsgBox(sPath & " does not exist.")
Exit Sub
End If
Catch ex As Exception
Dts.TaskResult = ScriptResults.Failure
End Try
Dts.TaskResult = ScriptResults.Success
End Sub
Dim sDateString As String
Dim sDate As String
Dim sPath As String
Try
strExpression = Dts.Variables("File_Loaded
'sPath = strFolder & strPrefix
'MsgBox(strExpression)
If FileOrDirExists(sPath) Then
'MsgBox(sPath)
getDatFileName = strPrefix & sDate & ".xls"
FileCopy(FromPath & "\" & getDatFileName, ToPath & "\" & getDatFileName)
'strExpression = True
'MsgBox(strExpression)
Dts.Variables("File_Loaded
Else
'MsgBox(sPath & " does not exist.")
Exit Sub
End If
Catch ex As Exception
Dts.TaskResult = ScriptResults.Failure
End Try
Dts.TaskResult = ScriptResults.Success
End Sub
ASKER
for loop
loop.bmp
loop.bmp
what's Variables("File_Loaded") value? from the variable's window? set it to 0
just to make sure also in the ELSE add
Dts.Variables("File_Loaded ").Value = 0
just to make sure also in the ELSE add
Dts.Variables("File_Loaded
ASKER
thanks for your help.
the value is set to 0.
the value is coming bak as 0
Dts.Variables("File_Loaded ").Value = 1
MsgBox("value " & strExpression)
it does not seem to be setting the variable to 1
the value is set to 0.
the value is coming bak as 0
Dts.Variables("File_Loaded
MsgBox("value " & strExpression)
it does not seem to be setting the variable to 1
ASKER
i did not have my variable name spelt correctly.
@[User::File_Loaded] = 0
the problem is now the for loop alwasy stays green
@[User::File_Loaded] = 0
the problem is now the for loop alwasy stays green
Hi configure your loop like the attached
forloop.jpg
forloop.jpg
I didn't see your sleep in the script
put it after the ELSE
System.Threading.Thread.Sl eep(600000 )
put it after the ELSE
System.Threading.Thread.Sl
ASKER
cool works up until a point, am i not understanding the for loop.
once the condition hits 1, should the loop not turn green and stop.
once the condition hits 1, should the loop not turn green and stop.
Not until you ask it to do so, but you don't want that..., believe you're moving the file, so it'll keep looking for the file again
in every iteration, we're initializing the variable = 0 again...
you can create another variable int32 @FileFound = 0
then in the EvalExpression use: @[File_Loaded] == @[FileFound]
so now it'll stop..
check these examples to understand it more
http://www.sqlis.com/post/For-Loop-Container-Samples.aspx
http://microsoft-ssis.blogspot.com/2011/02/how-to-configure-for-loop-container.html
Let me know if you need more help
in every iteration, we're initializing the variable = 0 again...
you can create another variable int32 @FileFound = 0
then in the EvalExpression use: @[File_Loaded] == @[FileFound]
so now it'll stop..
check these examples to understand it more
http://www.sqlis.com/post/For-Loop-Container-Samples.aspx
http://microsoft-ssis.blogspot.com/2011/02/how-to-configure-for-loop-container.html
Let me know if you need more help
ASKER
perfect thanks.
Just a script task inside the loop to delay for 10 mins, and change the variable's value to 1 and that would be your condition to stop the loop.
Sleep 600000 'that's 10 mins in miliseconds.
let me know if you need more help...