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.
aneilgAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jason Yousef, MSSr. BI  DeveloperCommented:
Alright, simple...
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...
0
aneilgAuthor Commented:
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
0
Jason Yousef, MSSr. BI  DeveloperCommented:
Add a break point to check for the variable's value changes...
Anyway can you post your VB code? and a screen shot of the loop configuration?


you could use something like that

Imports System.IO.File


Dim Vars As Variables = Nothing  'var dispenser
Dts.VariableDispenser.LockForWrite("User::file_loaded")


Dts.VariableDispenser.GetVariables(Vars)


If File.Exists("c:\myfile.txt") Then
    'MessageBox.Show("File found.")
	Vars("file_loaded").Value = 1
Else
    'MessageBox.Show("not yet")
	Vars("file_loaded").Value = 0
End If

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

aneilgAuthor Commented:
Public Sub Copy_File()

        Dim sDateString As String
        Dim sDate As String
        Dim sPath As String

        Try

            strExpression = Dts.Variables("File_Loaded").Value.ToString


            '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
0
aneilgAuthor Commented:
for loop
loop.bmp
0
Jason Yousef, MSSr. BI  DeveloperCommented:
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
0
aneilgAuthor Commented:
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
0
aneilgAuthor Commented:
i did not have my variable name spelt correctly.
@[User::File_Loaded] = 0

the problem is now the for loop alwasy stays green
0
Jason Yousef, MSSr. BI  DeveloperCommented:
Hi configure your loop like the attached
forloop.jpg
0
Jason Yousef, MSSr. BI  DeveloperCommented:
I didn't see your sleep in the script
put it after the ELSE

System.Threading.Thread.Sleep(600000)
0
aneilgAuthor Commented:
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.
0
Jason Yousef, MSSr. BI  DeveloperCommented:
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
0
aneilgAuthor Commented:
perfect thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.