SSIS loading the same file over and over

OK, here is my problem.
In SSIS, I am using a foreach loop to iterate through a bunch of Excel files to load the data into one of my tables. There are two steps in the loop, one to load the file into the table, the other to move the file into a "Processed" folder. When I run the package it searches the directory for a file and finds "File A", loads it and moves it in the "Processed" folder. Then it loops and finds "File B", it reloads "File A" and moves "File B" into the "Processed" folder, and so on, loading "File A" such that if I have 10 files to load, it will load "File A" 10 times. How can I get the loop to release the previous data source in favor of the new one. The foreach loop gets the fully qualified path of the file and loads it into a variable ("Filename") and "Filename" is then used to populate the Excel File Path of the connection manager in the loop.

Any ideas?

asked Jul 19 '12 at 03:55 PM in Default

themacky
0 ● 1 ● 1

(comments are locked)

3 answers:

It sounds like the LoadFileToTable step isnt getting the new filename value. Are you using a variable, have you set it to be the new/next value?

How did you build this process? If it was from an example have you reviewed the steps they offered to make sure there isnt a tiny bit missing?

Pragmatics works have an example on their FAQ

answered Jul 20 '12 at 10:56 AM

Fatherjack ♦♦
38.8k ● 55 ● 69 ● 104

(comments are locked)

Since there are not much details given, there could only be some guess work. Have you put .xls" (could be something like "File*.xls" for precise search) to search for all the Excel files in the specified folder? Are you sure you have mapped the "Filename" variable correctly in the ForEachLoop container's "Variable Mapping"? Are you supplying the connection string values as Expression? Did you set the property DelayValidation=TRUE on the Data Flow task?

Moreover, you should debug your package with the use of Breakpoints preferably OnPreExecute breakpoint. It will help you sorting out the problem.

answered Jul 20 '12 at 10:39 AM

Usman Butt
13.8k ● 6 ● 8 ● 14

(comments are locked)

Instead of using variable,Configure your folder with Enumerator configuration in foreach loop editor and files
Make sure that uncheck the traverse subfolders

answered Jul 20 '12 at 07:43 PM