SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Import records in MS Access table to native SQL Server 2008 table


Import records in MS Access table to native SQL Server 2008 table

Author
Message
Mark Dalley
Mark Dalley
Say Hey Kid
Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)

Group: General Forum Members
Points: 705 Visits: 1673
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
ryan.mcatee
ryan.mcatee
SSC-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

Group: General Forum Members
Points: 125 Visits: 134
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.

Mark Dalley
Mark Dalley
Say Hey Kid
Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)

Group: General Forum Members
Points: 705 Visits: 1673
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
Chrissy321
Chrissy321
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3354 Visits: 4799
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.
Mark Dalley
Mark Dalley
Say Hey Kid
Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)

Group: General Forum Members
Points: 705 Visits: 1673
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search