Solved

ssis if statement

Posted on 2008-10-01
9
995 Views
Last Modified: 2013-11-10
I have an SSIS package in sql 2005.  I have a file watcher task that kicks off a for-each loop.  I want these tasks to run repeatedly until there are no more files.  It works for the most part, but I need to check the folder again after it does the for-each to see if any more files came in wile it was running BEFORE it executes the tasks outside of the for-each...follow?
0
Comment
Question by:PsychoDazey
  • 5
  • 4
9 Comments
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22617512
Creating another ForEach loop doesn't resolve your problem?!
Cheers!
Pedro
0
 
LVL 6

Author Comment

by:PsychoDazey
ID: 22617995
Where would I put it?  do you know how to write an expression that counts the files with an .mdb extension in a certain directory?
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22618305
copy past the current foreach loop and link the old to the new one.
to count the number of files, use a script task.
I can do it for you, fo you want?
Regards
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.

 
LVL 6

Author Comment

by:PsychoDazey
ID: 22618497
yes, please help me with the task...I want to count the 8.mdb files after it exits the foreach loop...if there are more files it should run the for-each again, if not it should run the last 2 tasks.

Thanks again!
0
 
LVL 22

Accepted Solution

by:
PedroCGD earned 500 total points
ID: 22619264
I made the project for you!
Change the paths of MDBfiles directory in each foreach loop
Helped?!
Regards

Inside srcipt task I added the follow code to count MDB files:
 
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
 
Public Class ScriptMain
    Public Sub Main()
        Dim i As Integer = CInt(Dts.Variables("uvCountMDBFiles").Value)
        Dts.Variables("uvCountMDBFiles").Value = i + 1
        MsgBox(i)
        Dts.TaskResult = Dts.Results.Success
    End Sub
End Class

Open in new window

LoadResponses2-dtsx.txt
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22626903
Improvements?
Regards!
pedro
0
 
LVL 6

Author Comment

by:PsychoDazey
ID: 22633434
I will be testing this afternoon, thanks again.
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22636928
improvements? :-)
Regards
0
 
LVL 6

Author Closing Comment

by:PsychoDazey
ID: 31502106
Hi Pedro-
I took what you gave me and modified it to work, i only used 1 for loop and counted the .mdb files each time it ran.  Thanks for the help, I appreciate it!
0

Featured Post

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.

Question has a verified solution.

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

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…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

776 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