Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 460
  • Last Modified:

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.
0
aneilg
Asked:
aneilg
  • 7
  • 6
1 Solution
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now