importing data from access to sql 2005 nightly

I hope I am giving the right information here to get help with this.
I am trying to get information from two tables that are in an access
database over to sql 2005. I did this via DTS and saved the import options
as a file, but when I run this as a job I get the following error: Date
2/27/2008 11:14:08 AM
Log Job History (CombinationTable1)
Step ID 0
Server D86J0PD1
Job Name CombinationTable1
Step Name (Job outcome)
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
The job failed. The Job was invoked by User BIZCHAIR\DarleneMurphy. The
last step to run was step 1 (CombinationTable1).
I have also tried to run this in the following way:
USE Combination_Table
CREATE TABLE GG Products
AS
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'W:\MASTER DOCUMENTS\Databases\Channel_Advisor_Feed.mdb';'adm in';'', GG
Products)
go
Here I get errors regarding nulls and if I pointed to the correct database,
which I have:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
returned message "'w:\master documents\bizchair-2.mdb' is not a valid path.
Make sure that the path name is spelled correctly and that you are connected
to the server on which the file resides.".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
Any help with this problem and I hope I have given good information in order
to get help.
Thank you
Dee
Try 'w:\master documents\[bizchair-2].mdb'
Can you use SSIS?
"Dee" <Dee@.discussions.microsoft.com> wrote in message
news:C932BD77-BC9C-4CB8-8E1A-5AB32908C8C1@.microsoft.com...
>I hope I am giving the right information here to get help with this.
> I am trying to get information from two tables that are in an access
> database over to sql 2005. I did this via DTS and saved the import
> options
> as a file, but when I run this as a job I get the following error: Date
> 2/27/2008 11:14:08 AM
> Log Job History (CombinationTable1)
> Step ID 0
> Server D86J0PD1
> Job Name CombinationTable1
> Step Name (Job outcome)
> Duration 00:00:00
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> The job failed. The Job was invoked by User BIZCHAIR\DarleneMurphy. The
> last step to run was step 1 (CombinationTable1).
> I have also tried to run this in the following way:
> USE Combination_Table
> CREATE TABLE GG Products
> AS
> SELECT *
> FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'W:\MASTER DOCUMENTS\Databases\Channel_Advisor_Feed.mdb';'adm in';'', GG
> Products)
> go
>
> Here I get errors regarding nulls and if I pointed to the correct
> database,
> which I have:
> OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
> returned message "'w:\master documents\bizchair-2.mdb' is not a valid
> path.
> Make sure that the path name is spelled correctly and that you are
> connected
> to the server on which the file resides.".
> Msg 7303, Level 16, State 1, Line 2
> Cannot initialize the data source object of OLE DB provider
> "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
>
> Any help with this problem and I hope I have given good information in
> order
> to get help.
>
> Thank you
>
|||I know you already created the DTS package, but have a look at this
link:
http://www.microsoft.com/sql/solutions/migration/access/default.mspx
I use it to convert all of our old Access items with a very good
success rate (after cleaning up the data of course).
On Feb 27, 2:12Xpm, Dee <D...@.discussions.microsoft.com> wrote:
> I hope I am giving the right information here to get help with this.
> I am trying to get information from two tables that are in an access
> database over to sql 2005. XI did this via DTS and saved the import options
> as a file, but when I run this as a job I get the following error: Date X
> 2/27/2008 11:14:08 AM
> Log XJob History (CombinationTable1)
> Step ID X0
> Server XD86J0PD1
> Job Name XCombinationTable1
> Step Name X(Job outcome)
> Duration X00:00:00
> Sql Severity X0
> Sql Message ID X0
> Operator Emailed X
> Operator Net sent X
> Operator Paged X
> Retries Attempted X0
> Message
> The job failed. XThe Job was invoked by User BIZCHAIR\DarleneMurphy. XThe
> last step to run was step 1 (CombinationTable1).
> I have also tried to run this in the following way:
> USE Combination_Table
> CREATE TABLE GG Products
> AS
> SELECT *
> FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'W:\MASTER DOCUMENTS\Databases\Channel_Advisor_Feed.mdb';'adm in';'', GG
> Products)
> go
> Here I get errors regarding nulls and if I pointed to the correct database,
> which I have:
> OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
> returned message "'w:\master documents\bizchair-2.mdb' is not a valid path.. X
> Make sure that the path name is spelled correctly and that you are connected
> to the server on which the file resides.".
> Msg 7303, Level 16, State 1, Line 2
> Cannot initialize the data source object of OLE DB provider
> "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
> Any help with this problem and I hope I have given good information in order
> to get help.
> Thank you