Solved

ssis for loop

Posted on 2012-03-30
13
450 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
  • 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 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
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

792 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