?
Solved

ssis for loop

Posted on 2012-03-30
13
Medium Priority
?
455 Views
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.
0
Comment
Question by:aneilg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 21

Expert Comment

by:Jason Yousef, MS
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...
0
 

Author Comment

by:aneilg
ID: 37787124
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
 
LVL 21

Accepted Solution

by:
Jason Yousef, MS earned 1500 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
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 does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:aneilg
ID: 37787280
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
 

Author Comment

by:aneilg
ID: 37787288
for loop
loop.bmp
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
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
0
 

Author Comment

by:aneilg
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
0
 

Author Comment

by:aneilg
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
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 37787616
Hi configure your loop like the attached
forloop.jpg
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 37787674
I didn't see your sleep in the script
put it after the ELSE

System.Threading.Thread.Sleep(600000)
0
 

Author Comment

by:aneilg
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.
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
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

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
 

Author Closing Comment

by:aneilg
ID: 37855728
perfect thanks.
0

Featured Post

Basic Security of Your VPC

So, you’ve got this shiny new VPC and a fancy new application configured on your EC2 servers ready to go. This application is only accessible from your computer, which is great for security, but you need your users to be able to access it! So, what’s the easiest way to do this?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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.​
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

770 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