Debug SSIS import from Excel to SQL Server
-
Hello experts
I am trying to debug an SSIS import from an Excel spreadsheet to a SQL Server table. It works fine on one PC, but falls over partway through on another machine which has a more recent version of Office. As in, it fails at about line 50,000 in a 120,000 line file.
Here are the relevant specs for each of the PCs:
Item Mark's PC Paul's PC Processor Intel® Core™ i5-5200 CPU @ 2.20 GHz Intel Core i7-5600u CPU @2.60 GHz RAM 8.00 GB (7.88 GB usable) 16384 MB Excel version 14.0.7248.5000 (32-bit) (Office Professional Plus 2010) 16.0.4498.1000 (64-bit) (Office Professional Plus 2016) Excel install style 32-bit 64-bit SSMS version 18.5 17.6 SQL Integration Services ? ? SQL Integration Services ? ? Microsoft Data Access Components (MDAC) 10.0.18362.1 6.1.7601.17514 Microsoft .NET Framework 4.0.30319.42000 4.0.30319.42000 Import Wizard version and bitness ? ? Excel OLE DB driver version and bitness ? ? The two things that stick out the most to me are
- The working machine has Office 2010 as opposed to 2016
- Although both machines have a 64-bit Windows installation, the working PC is running a 32-bit version of Office whereas the failing PC is running a 64-bit version of Office
I really want to get the failing PC working without having to backgrade the Office version. Personally, I suspect that the problem stems from the OLE DB driver for Excel (or is it an ODBC driver?). Presumably the import is using a 64-bit version of the driver on the failing machine, and a 32-bit version on the good one. That leaves me with the following questions:
- How do I get to see the installed OLE DB drivers and versions on a PC?
- How do I install both 32-bit and 64-bit OLE DB drivers for Office apps (or ODBC drivers for that matter) on the same machine, given that it is impossible to install 32-bit and 64-bit Office versions side by side?
- I gather that there are 32-bit and 64-bit versions of the Import and Export wizards. I presume that they will respectively use 32-bit and 64-bit OLE DB drivers – is this correct?
- How do I see / control which version of the import / export wizard I am calling from SSMS?
- Am I actually restricted to using OLE DB drivers? If not, how do I select ODBC ones?
I apologise in advance for the length and messiness of this question. There seem to be are a large number of possibly relevant factors. Also, I hope that the above table dispalys properly!
Yours hopefully
MarkD
-
Rather than attempting to answer all of your questions, I'll bite off a chunk.
To get both 32- and 64-bit version of the ACE driver (which is used to import .xlsx files) installed, I think you'll have to do something like this:
- Replace 32-bit Office with 64-bit Office
- Install the 64-bit version of the 2016 ACE driver
- Install the 32-bit version of the 2010 ACE driver
The 2010 32-bit driver does not seem to care whether 64-bit Office components are already installed.
I use full-blown SSIS rather than anything provided by SSMS, so I'm not sure about a lot of the other stuff. But are you able to post any error messages you receive?
Regarding your question (3), the wizards themselves do not have bitness (as far as I know). But they can execute in either 32- or 64-bit mode (SSIS packages are executed by a program called DTExec.exe, which has 32- and 64-bit versions). Usually, the 64-bit version is the default, though I don't know how to control it in SSMS. Whichever version of DTExec is executed corresponds with the version of the ACE driver which gets used.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Thanks Phil, for the quick response.
I found this article yesterday, which indicates that the import/export wizards do indeed have bitness.
OK... Suppose I create and save an SSIS package with all the data and logic flows, connection managers etc.
When is the choice made as to whether the chosen driver (for Excel in this case) is a 32-bit or a 64-bit version? Is it made when the package is created (in which case, assuming I had created it with the wizard, the bitness of the wizard would presumably decide)? Or is it when the package is actually executed, in which case the bitness of the dtexec version would decide? Or some combination?
Understanding this would clarify a lot.
Thanks
MarkD
-
I checked the link, but I still think that I am right. That's just the sort of guy I am 🙂
Packages do not have bitness.
My educated guess is that those links to 32-bit or 64-bit versions of the wizards are merely dictating which version of DTExec.exe gets used when executing the resulting package. And the version of DTExec drives the bitness of ACE driver which is used.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - Phil Parkin wrote:
- Replace 32-bit Office with 64-bit Office
- Install the 64-bit version of the 2016 ACE driver
- Install the 32-bit version of the 2010 ACE driver
<li style="list-style-type: none;">
- <li style="list-style-type: none;">
You can install the opposite driver using the command line and specifying either the /quiet or /passive arguments, regardless of which version of Office has been installed. If you don't have Office installed and you install the x64 version - you would use the command line and argument to install the x86 version.
Since SSMS is an x86 application - it only has access to the x86 drivers - and by extension, the import wizard is going to be the same since it is installed as a component of SSMS.
I am still trying to figure out why you would be building packages that are distributed to multiple workstations - and manually run by the end users. How are these executed by the end users?
If these are stored in SQL Server somewhere (preferably the Integration Services Catalog) - it would be much easier to setup a scheduled agent job that searches a specific UNC folder for the existence of a file and if that file is found it gets processed.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
You can install the opposite driver using the command line and specifying either the /quiet or /passive arguments, regardless of which version of Office has been installed.
Great tip! I never knew.
Since SSMS is an x86 application - it only has access to the x86 drivers - and by extension, the import wizard is going to be the same since it is installed as a component of SSMS.
Well yes, but that's not really the point. The package which the import wizard generates has no bitness and is executed by DTExec.exe, which has both 32- and 64-bit versions.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Hi Phil
For my own instruction, I downloaded both 32-bit and 64-bit versions of the the 2010 and 2016 Access Redistributable .exe installation files. I presume that these are the right ones - at least, that is what the MS SSIS help pages pointed to.
I have 32-bit Office installed, and as I feared, both of the 64-bit installs failed with a message saying that I had to uninstall 32-bit Office first! Aint gonna happen - at least, not for the time being. As I said in my first post though, my current problem is with someone else's machine which has a 64-bit version of Office 2016. I will have to contact them and get them to try a few things - it may be a couple of days before I can come back with something.
MarkD
-
Hi Jeffrey, just saw your posting now, along with Phil's reply.
You can install the opposite driver using the command line and specifying either the /quiet or /passive arguments, regardless of which version of Office has been installed. If you don't have Office installed and you install the x64 version - you would use the command line and argument to install the x86 version.
Just tried it (accessdatabaseengine_X64.exe /passive for the 2016 version) and it seemed to work - at least, it didn't genertate any errors. I don't know how to check whether the driver was installed though, which brings me back to my very first question - How do I get to see the installed OLE DB drivers and versions on a PC?
Re installing the package in SSISDB catalog - great idea, but I am going to have to speak sweetly to one of the DBAs to make it happen. While I am developing this package I am storing it on the common network drive in a protected folder.
Many thanks for all help so far.
MarkD
- Phil Parkin wrote:
Well yes, but that's not really the point. The package which the import wizard generates has no bitness and is executed by DTExec.exe, which has both 32- and 64-bit versions.
Actually - that is the whole point. It all depends on what version is executing as to which version of the driver is needed. The version called from SSMS will be the x86 version - because SSMS is a 32-bit application. In fact, this is the same issue with developing packages that read/write Access or Excel - if you install Office x64 then you cannot develop those packages until you install the x86 version of the drivers, nor can you use the Import/Export wizard.
In fact - the x64 version of DTExec is not installed when you install SSMS. It is only installed when you install Integration Services - and then will only be called from SQL Server unless you use the command line.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs - Mark Dalley wrote:
Just tried it (accessdatabaseengine_X64.exe /passive for the 2016 version) and it seemed to work - at least, it didn't genertate any errors. I don't know how to check whether the driver was installed though, which brings me back to my very first question - How do I get to see the installed OLE DB drivers and versions on a PC?
A simple way to test is to try to use the Import/Export Wizard on the system where Office x64 was installed. You can also open up the ODBC Data Source Administrator application for 32-bit or 64-bit and see if you can create a data source.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs - Jeffrey Williams wrote:
In fact - the x64 version of DTExec is not installed when you install SSMS. It is only installed when you install Integration Services - and then will only be called from SQL Server unless you use the command line.
I probably should have checked my own PC before. For 2017 (with SSIS installed too) there are 32- and 64-bit versions of the import wizard (so my earlier statement was incorrect, apologies), which presumably use 32- or 64-bit drivers, respectively, during development.
As to what gets installed with SSMS, I don't have access to a machine which has only SSMS on it, so cannot verify one way or the other.
But if you develop a package in SSDT, which is 32-bit, and then execute it, it will by default execute in 64-bit mode (in a 64-bit o/s, of course). I was assuming a similar convention was in place for SSMS – just because SSMS is a 32-bit app does not prevent it running another 64-bit app. But if that assumption is incorrect, fair enough.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
It all depends on how you execute the package - if you try to run the package within SSDT in debug mode it runs as a 32-bit application. In fact, you cannot even setup an Excel source or destination if you don't have the 32-bit driver installed.
On my workstation, I have Office x64 and was not able to run the import/export wizard from SSMS - or use the Excel source or destination until I had installed that driver.
I do not install SSIS (Integration Services) on my workstations. That is installed on the server(s) and used when the package/projects are deployed to the catalog.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
I decided to check this out by running a package in debug mode in VS2019.
When I do this, I see two debug processes, one 32-bit and one 64-bit, suggesting both are available:
Also within VS, if you navigate to Project/Properties/Configuration Properties/Debugging, you will see the following screen:
By default, Run64BitRuntime is set to True. Packages will run in 32-bit during debugging only if this value is set to false (or you are running in a 32-bit o/s).
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I have been having another play with the 64-bit Access database engine 2016 install. It turns out that it didn't actually work properly. I have discovered the following:
- If you want to install a version of contrary bitness to that of your Office insdtallation, the /passive switch doesn't work. You have to use the /quiet switch - at least for the 2016 version.
- To get an idea of what is going on in a quiet install, use the /log:logfilename switch in additon to /quiet. You must leave space before each of the forward slashes, otherwise the switches won't be recognized.
- A successful install of both sets of drivers will leave you with two entries in your programs and features panel for the Microsoft Access database engine 2016, both with the same version number (16.0.4519.1000 in my case).
- Likewise, the install will populate both of the 32-bit and 64-bit ODBC admin drivers panels with four drivers bearing the same version number (64-bit panel shown):
-
That is why I stated either the /quiet or /passive arguments - which depend on the version you are installing. For the 2010 version you have to use the /quiet argument.
@Phil - the setting you show is only valid if the SSIS 64-bit runtime is installed. If it is not installed - that parameter is ignored. Regardless - unless you have the 32-bit ACE driver installed you cannot build a package in SSDT using the Excel file connection. Nor can you utilize the Import/Export wizard - both are executed in the 32-bit subsystem in Windows and only have access to 32-bit drivers.
The opposite is true also - on a server where only Integration Services has been installed, you cannot run a package with the 32-bit runtime and access the Excel file connection unless the 32-bit ACE driver is installed. Simply put - a 32-bit application cannot access or use 64-bit drivers and a 64-bit application cannot access or use 32-bit drivers.
Back to the OP's original question...I believe the issue you were having is related to the driver. Have you been able to test and validate that the package is now working on both workstations?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply