Dts (ms Access To Ms Sql Server)

[ssrirao]

hi,
I have 10 tables in ms access.
currently what i am doing is ...
1) import data into ms sql server, hence creating 10 tables
2) i have 2 procedures which does some calculations (basically consolidation of data) based on 10 tables. And insert data or update data into 2 tables.

Can i automate this etl process..
take data from 10 ms access tables
consolidate and put the data into 2 ms sql server

I want this process to occur every weekends
Please guide if any body has done this kind of process

[ssrirao]

Note: Ms Sql Server 2000

[almaz]

Based on the title of the topic :), you've already found out that this kind of tasks can be easily done with DTS packages.
Please describe what problems do you have, as creating DTS packages for your case should be a pretty simple task: create package, add connection to MS Access, add one task to import data and another one to execute SPs, etc.

[ssrirao]

1) I have a ms access 2000 database with tables a, b, c, d, e, f, g, h, i, j (10 tables)
2) I have 2 ms sql server 2000 procedure which retrieves data from all these tables, does some calculations, and insert or updates the rows in ms sql server table x and y
3) I have 2 ms sql server 2000 tables

The procedure works perfectly if these tables are ms sql server table and if I execute those procedures manually (i.e. execute pro_1....)
How do I automate it? (Using DTS)
(Note: I am new to SQL Server :-))

[almaz]

1. Create required connections.
2. Create tasks to import 10 tables from MS Access to SQL Server (you will have to create 10 tables in MSSQL for storing imported data)
3. Create task(s) to execute stored procedures
4. If required - create task to cleanup the tables with imported data (you can do it from stored procedures).

[ssrirao]

I dont want to import the data into MS SQL SERVER.
My MS Access tables are very big containing millions of records.

(Problem with importing: Table space will exceed the disk space!, so no question of importing)
Without importing the data into Sql Server.
How, my procedure can refer the 10 MS Access tables as source and put the resultant value into 2 MS Sql Server tables?
Or is there any alternative?

[almaz]

You can add a linked server (see samples here ) or use one of the Rowset Functions in your stored procedures (consider using OpenQuery or OpenRowset Rowset Functions so that only aggregated data would be transmitted to SQL Server)

[ssrirao]

Thanks....this sovled my problem.
What is the solution if the .mdb Access file resides in a unix server.

[ssrirao]

This gives the error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

Even after registry entry.

What is the solution if the .mdb Access file resides in a unix server.

[navamnk]

FTP the mdb file to Windows at regular interval

[navamnk]

I think, the mdb file is access by some other application or one of the table is opened by you.

[ssrirao]

Thanks,
Srinidhi Rao