Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post May 21, 2012 08:25 PM by dwfreeman
May 21, 2012 04:41 PM|LINK
The wizards in SSMS make it fairly easy to import data from other databases such as MS Access. I have a need to perform some of these imports repeatedly and was wondering in it's possible to write a script that could be saved that will perform this type
of import. (From MSAccess to SQLServer). I can write some query code to INSERT via a SELECT but how do you connect to 2 databases at the same time?
May 21, 2012 05:26 PM|LINK
SSIS Packages can do this job easily
May 21, 2012 05:43 PM|LINK
Supposedly this should work:
insert into LookupFundTypes (FundType)
from opendatasource ('Microsoft.Jet.OLEDB.4.0','D:\tipconversion.mdb')...fundtypes
but I get an error that says the provider has not been registered. This is strange because the JET provider works fine in VS2010. I have win7 32 bit, SQLserver2008R2, Access2002.
May 21, 2012 06:02 PM|LINK
I think the connection string needs to be like this:
from opendatasource ('Microsoft.Jet.OLEDB.4.0','Data Source="D:\tipconversion.mdb"; User ID=Admin; Password=')...fundtypes
May 21, 2012 07:25 PM|LINK
There's a really good explanation here using SSIS. http://stackoverflow.com/questions/6709670/how-do-i-import-ms-access-data-into-sql-server-using-ssis-package
You can set up a SQL Job to call the SSIS package at certain times / days etc
May 21, 2012 08:25 PM|LINK
Thanks Tab. I was able to make it work with that.