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

Import records in MS Access table to native SQL Server 2008 table Expand / Collapse
Author
Message
Posted Thursday, March 7, 2013 4:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 2:51 AM
Points: 41, Visits: 925
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
Post #1427891
Posted Thursday, March 7, 2013 6:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 7, 2013 6:39 AM
Points: 47, Visits: 133
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.
Post #1427936
Posted Thursday, March 7, 2013 8:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 2:51 AM
Points: 41, Visits: 925
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
Post #1428021
Posted Thursday, March 7, 2013 8:33 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 4:49 PM
Points: 648, Visits: 3,800
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.
Post #1428027
Posted Friday, March 8, 2013 5:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 2:51 AM
Points: 41, Visits: 925
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
Post #1428524
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse