Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

For Each Loop Uploads the Same File Infinitely Expand / Collapse
Author
Message
Posted Saturday, September 21, 2013 3:44 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:37 AM
Points: 1,166, Visits: 3,832
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
Post #1497129
Posted Saturday, September 21, 2013 4:41 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 2:50 AM
Points: 608, Visits: 1,052
use breakpoints to debug connection settings

Pramod
SQL Server DBA | MCSA SQL Server 2012

in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/
Post #1497132
Posted Saturday, September 21, 2013 7:33 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:15 AM
Points: 79, 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
Post #1497173
Posted Sunday, September 22, 2013 11:50 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:48 PM
Points: 35,547, Visits: 32,133
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1497216
Posted Sunday, September 22, 2013 12:18 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:15 AM
Points: 79, 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
Post #1497219
Posted Sunday, September 22, 2013 1:12 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:48 PM
Points: 35,547, Visits: 32,133
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1497224
Posted Monday, September 23, 2013 5:52 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:37 AM
Points: 1,166, Visits: 3,832
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
Post #1497340
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse