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

avatar image

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

avatar image

Fatherjack ♦♦
43.8k 79 102 118

(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

avatar image

Usman Butt
14.9k 6 13 21

(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

avatar image

276 9 11 14

(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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Jul 19, 2012 at 03:55 PM

Seen: 1455 times

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

Copyright 2018 Redgate Software. Privacy Policy