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?
insert into LookupFundTypes (FundType)
select 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.
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
Please remember to Mark As Answer if helpful
Regards, Christiandev (@chrisdev80), MCPD Web (2 & 4) & MCTS Windows (www.ScoreDonkey.com)
dwfreeman
Member
568 Points
1128 Posts
Scripting SQL Server imports
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?
Thanks
Mudasir.Khan
All-Star
15346 Points
3142 Posts
Re: Scripting SQL Server imports
May 21, 2012 05:26 PM|LINK
SSIS Packages can do this job easily
check
http://dwhanalytics.wordpress.com/2011/03/16/using-ssis-loading-multiple-excel-files/
dwfreeman
Member
568 Points
1128 Posts
Re: Scripting SQL Server imports
May 21, 2012 05:43 PM|LINK
Supposedly this should work:
insert into LookupFundTypes (FundType) select fundtype from opendatasource ('Microsoft.Jet.OLEDB.4.0','D:\tipconversion.mdb')...fundtypesbut 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.
TabAlleman
All-Star
15741 Points
2724 Posts
Re: Scripting SQL Server imports
May 21, 2012 06:02 PM|LINK
I think the connection string needs to be like this:
insert into LookupFundTypes (FundType)
select fundtype
from opendatasource ('Microsoft.Jet.OLEDB.4.0','Data Source="D:\tipconversion.mdb"; User ID=Admin; Password=')...fundtypes
christiandev
Star
8607 Points
1841 Posts
Re: Scripting SQL Server imports
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
Regards, Christiandev (@chrisdev80), MCPD Web (2 & 4) & MCTS Windows (www.ScoreDonkey.com)
dwfreeman
Member
568 Points
1128 Posts
Re: Scripting SQL Server imports
May 21, 2012 08:25 PM|LINK
Thanks Tab. I was able to make it work with that.