For Each Loop Uploads the Same File Infinitely

Question

Post reply

For Each Loop Uploads the Same File Infinitely

Neil Burton

SSC-Insane

Points: 22908
More actions
September 21, 2013 at 3:44 am

#298954

I have set up an SSIS package to upload nearly 400 Excel files into a table. The problem I'm having is that the package only uploads the file that I have used to name the Excel source and it will do that over and over again.
I've followed the step by step instructions on any number of websites, each time starting from scratch and it still won't work. I've reverse engineered a package I used successfully to import CSV files and that hasn't solved it. I've checked my connection strings, variables and all the various settings and they all look like they should.
Does anybody have any suggestions? I'll happily post anything else you need to answer or help.
On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

psingla Hall of Fame Points: 3840 More actions · Answer 1

use breakpoints to debug connection settings

Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/

Vikash Kumar Singh SSC Eights! Points: 974 More actions · Answer 2

If you could share your design of SSIS that might be helpful to resolve this. A different approach that you can try is SSIS: How to load multiple excel files into multiple SQL tables?

Vikash Kumar Singh || www.singhvikash.in

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 3

Vikash Kumar Singh (9/21/2013)
If you could share your design of SSIS that might be helpful to resolve this. A different approach that you can try is SSIS: How to load multiple excel files into multiple SQL tables?

Hi Vikash,

I'm not sure that fine method will work for folks using 64 bit machines (which has become the norm rather than the exception) because the Jet drivers supposedly don't work with the 64 bit machines. I believe you need to use the Ace drivers for 64 bit machines. Do you have a code example for that?

Shifting gears a bit, your article has a date on it from Feb 2013 so it's a fairly recent article. Have you discovered a method to use the Jet drivers on 64 bit machines? If so, could you post how you pulled that off, please?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Vikash Kumar Singh SSC Eights! Points: 974 More actions · Answer 4

The solution is applicable to 32-bit machine.

To successfully use the ACE driver for 64-bit machine there are few things which I would like to clarify.

1. If you want to use 64-bit driver first go ahead and uninstall the 32-bit driver from the system/server.

2. After cleaning your 32-bit driver, you must download and install the 64-bit ACE driver.

There are not much changes with respect to solutions for 64-bit but confusion comes because either one of the server or machine of office suite we are using is 32-bit.

Secondly the installation of 64-bit driver on the machine with 32-bit driver always create a problem on the same machine.

I have a 64-bit machine but my office suite is 32-bit so I have not tested this.

Vikash Kumar Singh || www.singhvikash.in

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 5

Thanks, Vikash.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Neil Burton SSC-Insane Points: 22908 More actions · Answer 6

Thanks for your replies everybody. I finally got to the bottom of the problem and it can only be described as extremely annoying.

It turned out the package was technically running fine. It only appeared to be running the same file over and over . As I was watching it it kept showing the same number of rows in each file but not the file name. There was no earthly reason to expect that the files would have precisely the same number of rows in each one so I thought that it was running the same one over and over. The actual fault occurred when the package hit a file with the column headers in a different order or that contained completely different data.

We've had to solve it by lining up volunteers in front of Excel and running a macro over the files to standardise them. I know we probably could have coded this but the high-ups decided that given the time pressure and relative quietness at the moment that was the quickest and easiest way!

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537