Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


For Each Loop Uploads the Same File Infinitely


For Each Loop Uploads the Same File Infinitely

Author
Message
BWFC
BWFC
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2414 Visits: 9613
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
psingla
psingla
Right there with Babe
Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)

Group: General Forum Members
Points: 728 Visits: 1249
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
Vikash Kumar Singh
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 243
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
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45055 Visits: 39901
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Vikash Kumar Singh
Vikash Kumar Singh
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 243
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
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45055 Visits: 39901
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
BWFC
BWFC
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2414 Visits: 9613
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search