Solved

ssis if statement

Posted on 2008-10-01
9
991 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

762 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

18 Experts available now in Live!

Get 1:1 Help Now