ssis for loop

Posted on 2012-03-30
Last Modified: 2012-04-17
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.
Question by:aneilg
  • 7
  • 6
LVL 21

Expert Comment

ID: 37786949
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...

Author Comment

ID: 37787124

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
LVL 21

Accepted Solution

huslayer earned 375 total points
ID: 37787225
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


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

Open in new window


Author Comment

ID: 37787280
Public Sub Copy_File()

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


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

            'sPath = strFolder & strPrefix


            If FileOrDirExists(sPath) Then


                getDatFileName = strPrefix & sDate & ".xls"

                FileCopy(FromPath & "\" & getDatFileName, ToPath & "\" & getDatFileName)

                'strExpression = True


                Dts.Variables("File_Loaded").Value = 1


                '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

Author Comment

ID: 37787288
for loop
LVL 21

Expert Comment

ID: 37787361
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline


Author Comment

ID: 37787381
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

Author Comment

ID: 37787517
i did not have my variable name spelt correctly.
@[User::File_Loaded] = 0

the problem is now the for loop alwasy stays green
LVL 21

Expert Comment

ID: 37787616
Hi configure your loop like the attached
LVL 21

Expert Comment

ID: 37787674
I didn't see your sleep in the script
put it after the ELSE


Author Comment

ID: 37787699
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.
LVL 21

Expert Comment

ID: 37787731
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

Let me know if you need more help

Author Closing Comment

ID: 37855728
perfect thanks.

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Help with SQL Query 23 39
SQL server 2008 SP4 29 34
Troubleshooting Methodology - steps 3 21
MySQL left join performance 4 14
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now