x

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?
more ▼

asked Jul 19, 2012 at 03:55 PM in Default

themacky gravatar image

themacky
0 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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

more ▼

answered Jul 20, 2012 at 10:56 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Jul 20, 2012 at 10:39 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(comments are locked)
10|1200 characters needed characters left
Instead of using variable,Configure your folder with Enumerator configuration in foreach loop editor and files
Make sure that uncheck the traverse subfolders
more ▼

answered Jul 20, 2012 at 07:43 PM

sunil2711 gravatar image

sunil2711
256 8 8 11

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x939

asked: Jul 19, 2012 at 03:55 PM

Seen: 882 times

Last Updated: Jul 20, 2012 at 07:43 PM