[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

ssis for loop

Posted on 2012-03-30
13
Medium Priority
?
457 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

656 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