Solved

ssis if statement

Posted on 2008-10-01
9
999 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
[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
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Retrieve a Registry value using SCCM 2012. 2 40
T-SQL: Only Wanting One Record 8 56
Use SSRS to email customers? 4 21
SQL: Transformation or Pivot 3 27
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

740 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