import multiple accessfiles in dts

Hi,

How can i import multiple accessfiles in sql server 2000 by using a dts
package?

The dts package should import files with the most current date in a
directory (not today's date). The date can be found in the name, e.g.:
<companyname>_20041214.mdb.

Also i would like to use the companyname from the filename to fill in a
empty column called companyname, so i can import all files into one
normalized table.

The files are placed in on a root drive.

If someone can help me on this i would be very happy.

Thnx.Hi

The following should get you started:
http://www.sqldts.com/default.aspx?292
http://www.sqldts.com/default.aspx?248
http://www.sqldts.com/default.aspx?201

You can store the company name in a global variable, then use an activeX
transform to populate a column from that global variable.
http://www.sqldts.com/default.aspx?279,4

John

"Ezekil" <ezekil@.lycos.com> wrote in message
news:41c281c0$0$45956$a344fe98@.news.wanadoo.nl...
> Hi,
> How can i import multiple accessfiles in sql server 2000 by using a dts
> package?
> The dts package should import files with the most current date in a
> directory (not today's date). The date can be found in the name, e.g.:
> <companyname>_20041214.mdb.
> Also i would like to use the companyname from the filename to fill in a
> empty column called companyname, so i can import all files into one
> normalized table.
> The files are placed in on a root drive.
> If someone can help me on this i would be very happy.
> Thnx.

import accessfiles with stored procedures

Hi,
Could somebody tell me how to import access-files to a sql server database?
I would like to import all tables from access to the temp, do some
data-manipulation and then insert into a database. I know that dts can
import, but i want to address the tempdb so i don't have to create
temptables in my databases.
I hope someone can help me.Jason
One option is
INSERT INTO .....
SELECT *
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="d:\northwind.mdb";
User ID=Admin;Password='
)...Customers
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:esocaw2EFHA.3416@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Could somebody tell me how to import access-files to a sql server
database?
> I would like to import all tables from access to the temp, do some
> data-manipulation and then insert into a database. I know that dts can
> import, but i want to address the tempdb so i don't have to create
> temptables in my databases.
> I hope someone can help me.
>|||Hi Uri,
I've tried to use your example, but i'm getting errors which says that it
cannot find the path, but i'm sure the file is there?
Here's the statement:
SELECT *
FROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0','Data
Source="D:\klient.mdb";User ID=Admin;Password=')...sysdata
And here's the error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Could not find file 'D:\klient.mdb'.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:etAE$62EFHA.2568@.TK2MSFTNGP10.phx.gbl...
> Jason
> One option is
> INSERT INTO .....
> SELECT *
> FROM OPENDATASOURCE(
> 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="d:\northwind.mdb";
> User ID=Admin;Password='
> )...Customers
>
> "Jason" <jasonlewis@.hotrmail.com> wrote in message
> news:esocaw2EFHA.3416@.TK2MSFTNGP09.phx.gbl...
> database?
>|||Jason
Have you ran on the server or client machine?
Where is the file's location on the server?
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:OY5T7zAFFHA.1564@.TK2MSFTNGP09.phx.gbl...
> Hi Uri,
> I've tried to use your example, but i'm getting errors which says that it
> cannot find the path, but i'm sure the file is there?
> Here's the statement:
> SELECT *
> FROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0','Data
> Source="D:\klient.mdb";User ID=Admin;Password=')...sysdata
> And here's the error:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> [OLE/DB provider returned message: Could not find file 'D:\klient.mdb'.]
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: ].
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:etAE$62EFHA.2568@.TK2MSFTNGP10.phx.gbl...
>|||Hi Uri,
The files resides on a client pc.
Greetings,
J
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eU3lf$AFFHA.2156@.TK2MSFTNGP10.phx.gbl...
> Jason
> Have you ran on the server or client machine?
> Where is the file's location on the server?
>
> "Jason" <jasonlewis@.hotrmail.com> wrote in message
> news:OY5T7zAFFHA.1564@.TK2MSFTNGP09.phx.gbl...
it
can
>|||Then you need to specify an UNC path for the file, and the share need to acc
essible to SQL Server
and the account that SQL Server is using need to have permissions on the sha
re.
SQL Server is a *server* product, the queries are executed on the server, no
t the client. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jason" <jasonlewis@.hotrmail.com> wrote in message news:Oub81JCFFHA.3940@.tk2msftngp13.phx.g
bl...
> Hi Uri,
> The files resides on a client pc.
> Greetings,
> J
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eU3lf$AFFHA.2156@.TK2MSFTNGP10.phx.gbl...
> it
> can
>