April 24, 2017 at 12:15 pm
I was trying (in vain) to get my head around SSIS the other day, and tried running the Import Wizard from within SQL Server 2016 (64-bit, if it matters). Since this is a new computer, I'm sure I'm missing a file or something, but I get the dreaded "The ACE.OLEDB.12.0' is not registered on the local machine." I saw Lowell's post, and tried registering the driver and restarting the SQL Server service (just for grins), but to no avail. Here's the code I got from Lowell--ENABLING USE OF DISTRIBUTED QUERIES
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
GO
USE [master]
GO
--ADD DRIVERS IN SQL INSTANCE
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO
But it still doesn't work. Do I need OLEDB.12.0 or OLEDB.16.0? I saw Koen's article, but I'm pretty sure I followed all the instructions (but that was before the coffee) What did i miss?
April 24, 2017 at 12:21 pm
Have you installed the latest version of the ACE drivers? You can find them here: https://www.microsoft.com/en-gb/download/details.aspx?id=13255
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 24, 2017 at 12:30 pm
Did that already too. =( That's why I'm completely perplexed by this one. What else did I miss?
April 24, 2017 at 12:38 pm
Presumably you were trying to import from Excel? If you are just starting out with SSIS & want to 'get some runs on the board', I suggest that you start with an import from a CSV file. You'll have no ACE problems if you do that.
You should also check what appears under the
Server Objects / Linked Servers / Providers
node in SSMS.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 24, 2017 at 1:33 pm
Just to make sure I wasn't doing something really stupid, I ran the Import wizard from SSMS, and tried to import the same data saved as a Text file. The Wizard goes through the first few steps and then I get "Connection String cannot be initialized" error. I was digging around and one article said to set "Allow Remote Connections = True", which was set already. <scratching my head>
Any thoughts about what else I could be missing?
April 24, 2017 at 1:54 pm
pietlinden - Monday, April 24, 2017 1:33 PMJust to make sure I wasn't doing something really stupid, I ran the Import wizard from SSMS, and tried to import the same data saved as a Text file. The Wizard goes through the first few steps and then I get "Connection String cannot be initialized" error. I was digging around and one article said to set "Allow Remote Connections = True", which was set already. <scratching my head>
Any thoughts about what else I could be missing?
That error doesn't exactly give the game away. Have you considered installing SSDT and developing a 'grown up' SSIS package? It's much more fun than using the wizard & you'll learn more in the process. The errors which you get will be more verbose and informative too.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 24, 2017 at 1:57 pm
I could do that. I was trying to make sure I had it all installed correctly before going on to harder things. SSIS is bad enough for me as is, no need to add connection errors etc to the mess.
April 24, 2017 at 2:06 pm
Sure, I understand. I remember how long it took me to get going with it. Have you looked at the relevant Stairway series? It might help walk you though.
IMO, diagnosing connection issues is easier outside of the wizard though.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 24, 2017 at 2:15 pm
EXECUTE master.dbo.xp_enum_oledb_providers
Sue
April 24, 2017 at 2:30 pm
Sue,
thanks for that. ACE.OLEDB.12.0 is listed. Can i use it with Access and Excel 2016?
April 24, 2017 at 2:34 pm
This may be a silly question but did you install the ACE drivers on the SQL Server instance machine or your local machine?
EDIT - never mind... saw your previous reply and that answered my question.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 24, 2017 at 2:41 pm
Got it to work finally. Had to insert a Data Conversion task in between the Source and Destination. This is going to be a long process, I think, but I got one working finally!
April 24, 2017 at 2:55 pm
Sue
April 24, 2017 at 3:40 pm
At least now I know I'm on the right track... next thing is to work my way through Devin Knight's mapping of SQL Server to SSIS data types. The good thing is that I'm only changing one thing at time... SSIS is a different beast, so any more than that is gonna give me a migraine.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply