4

  1. I have an SSIS package which checks for the unprocessed file present in a tracking table and then processed it. Till date only one file would come in and we would process it and as such the process was designed accordingly.

  2. However now multiple files can come in one go and we store those multiple files in the tracking table and we have a column which keeps a track of the unprocessed file.

I am trying to use the For Each loop to process all the unprocessed file. So I get the count of the unprocessed files and would like to simply tun the Point 1 by passing a parameter to the step 1 but I have not been successful in doing it using Foreach From Variable Enumerator. Am I missing something ?

Improve this question
7
  • Using a For Each Loop to iterate through table rows is all over the internet. I just referenced this link in a question yesterday: red-gate.com/simple-talk/sql/ssis/… Commented Mar 14, 2019 at 10:38
  • @Nick.McDermaid i think the same link is provided in the answer below (first link) :) Commented Mar 14, 2019 at 13:39
  • I have no idea what you are trying to say here: " tun the Point 1 by passing a parameter to the step 1" Commented Mar 14, 2019 at 15:40
  • @TabAlleman he need to get file pathes stored in db table and loop over result and process these files inside the loop Commented Mar 14, 2019 at 16:20
  • @Sunil is the issue solved? have you tried the provided answer? if so why not giving a feedback or accepting it? Commented Mar 19, 2019 at 14:39

1 Answer 1

Reset to default
5

You can do this using the following steps:

  1. Add an Execute SQL Task to get unprocessed files and store the resultset inside a variable of type System.Object
  2. Add a Foreach loop container, change the type to ADO enumerator and select the variable as source
  3. In the variable mapping tab map the result (each file path) to a variable of type string
  4. Inside the foreach loop container add a dataflow task that contains the Flat File source and implement the processing logic you need
  5. Add a flat file connection manager define the columns
  6. Click on the flat file connection manager, press F4 to show the property tab, go to expression.
  7. Select the connectionstring property and use the variable that holds the filepath as expression

Detailed articles

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.