March 7, 2013 at 4:18 am
Hi SQL server experts
I am trying to use the SQL Server Import and Export Wizard to perform this simple task. The Access DB is on the same server, so I think it isn't a network problem.
Everything proceeds smoothly up to the final stage, which is preceded by the following:
Click Finish to perform the following actions:
Source Location : E:\RISC\Access Imports\RefDiagnosis_20130306.mdb
Source Provider : Microsoft.Jet.OLEDB.4.0
Destination Location : <server name>
Destination Provider : SQLNCLI10
Copy rows from `Diagnosis_Load` to [dbo].[Diagnosis_Load]
The new target table will be created.
The package will not be saved.
The package will be run immediately.
Provider mapping file : C:\Program Files (x86)\Microsoft SQL Server\100\DTS\MappingFiles\JetToMSSql9.xml
Progress was as follows:
Initialising data flow task - success
Initialising connections - success
Setting SQL command - success
Setting source connection - Error. Text in error message
Could not connect source component
Error 0xc020801: Source - Diagnosis_Load[1]: SSIS error code
DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquiteConnection method call failed.
Additional information:
Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)
What actually has gone wrong, and what do I do about it? The error message makes heavy weather of telling me.
PS. Something like this should just work IMHO. I feel embarrassed for SQL Server - how hard can it be for two mature MS technologies to talk to to one another?
Yours hopefully
Mark Dalley
March 7, 2013 at 6:24 am
Is this an issue with using the 64-bit transfer with a 32-bit odbc Jet driver? Maybe you could try to download the updated Microsoft Access Database Engine 2010. The description for this package:
Overview
This download will install a set of components that facilitate the transfer of data between existing Microsoft Office files such as Microsoft Office Access 2010 (*.mdb and *.accdb) files and Microsoft Office Excel 2010 (*.xls, *.xlsx, and *.xlsb) files to other data sources such as Microsoft SQL Server. Connectivity to existing text files is also supported. ODBC and OLEDB drivers are installed for application developers to use in developing their applications with connectivity to Office file formats.
March 7, 2013 at 8:29 am
Hi Ryan, thanks for responding.
I downloaded the 64-bit version of the package you mentioned. Before installing it, however, I decided to retry the import - which worked!! Very good, but Why?
Possible contributing factors... I noticed the the SQL server agent service was stopped, so I started it - how long it had been like this I don't know. But why would the agent be needed for running an import immediately, as I chose to do? I thought it was just for scheduling backups etc.
Also, in the wizard, there are places where you select more advanced options. After the first failure, I went into these and OK'ed out of them, but didn't alter anything.
When I deleted the imported table and retried the import, it failed again, twice, with SQL server agent running, so it probably wasn't the agent after all.
It eventually succeeded again, after I retried going through the steps, but this time at the Choose Data Source stage I clicked on Advanced, then clicked the Connect, Advance and All tabs, then OK'ed back out and carried on with subsequent steps.
This seems to be how to get it working, but again - why? Flaky, I call it. Brilliant though, I'm sure, in many ways...
Mark Dalley
March 7, 2013 at 8:33 am
I would export to to csv as an intermediary format. I agree it should work and you could probably make it work but if its a one time process I might do what I know will work and move on.
March 8, 2013 at 5:04 am
Thanks people I'll leave it at that.
The nice thing about this forum is that you know the answer to your question is out there somewhere in the brain pool, and that boosts one's confidence to tackle it.
Next time, though, I think I'll do it via plain text, or bulk copy or something. So much to learn.
Cheers
Mark Dalley
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply