Excel and DTS

Excel and DTS

Post by Andy Rittin » Sat, 04 Nov 2000 04:00:00



Ok I went down to the server and logged on with the SQL Server Agent account
instead of using terminal server to log in with that account.  I can access
and read the excel file when I execute it from DTS but the package fails
when scheduled.  I'm wondering if the SQLCmdExec account has anything to do
with scheduled jobs?


> It shouldn't have to be installed on the executing server since you're
using
> the OLE DB provider.  Have you tried to sign into the server with the
> account starting SQL Server agent and see if he has permissions to the
file.
> Can you see the file when you interactively execute the package with your
> login permissions?

> --

> Brian Knight
> http://www.swynk.com/friends/knight
> http://www.sqlservercentral.com



> > Does Excel need to be installed on the Server reading Excel files with
> DTS?

> > We're trying to read these excel files on Novell Servers without much
> > success.  We can get it to work when we run DTS from our client machines
> > which have Excel but when I use terminal server into our server or
> schedule
> > it as a job it fails.  Our server doesn't have Excel installed on it.

> > I know SECURITY is a flag here but the Server Agent is in the same
Domain
> > group as we are and was given the same novell login script.  Also we're
> > using UNC naming and the UNC paths I type into explorer while logged in
> > locally on the server as the Server Agent get me to the files I want to
> > read.

> > This is the error from the job history:

> > Error string:  '\\SERVER\VOL1\FINBUDG\Budget 2001\Dept2001\Engineering
> > Departments\Expense\Budget2001_FirstLastName.xls' 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.      Error source:  Microsoft JET
> > Database Engine

> > putting this string into explorer on the server get's me to the file.

 
 
 

Excel and DTS

Post by Mike » Sat, 04 Nov 2000 04:00:00


Andy:

On the scheduler when setting up a job step, select Operating System Command
(CmdExec) and put this in the Command window with your appropriate
information:

dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name
/Mpackage-password

You can open up a DOS window and copy the very same thing to the prompt and
run it from there.  This helps to see if it will work without monkeying
around with the job interface.

Regards,

Mike


> Ok I went down to the server and logged on with the SQL Server Agent
account
> instead of using terminal server to log in with that account.  I can
access
> and read the excel file when I execute it from DTS but the package fails
> when scheduled.  I'm wondering if the SQLCmdExec account has anything to
do
> with scheduled jobs?



> > It shouldn't have to be installed on the executing server since you're
> using
> > the OLE DB provider.  Have you tried to sign into the server with the
> > account starting SQL Server agent and see if he has permissions to the
> file.
> > Can you see the file when you interactively execute the package with
your
> > login permissions?

> > --

> > Brian Knight
> > http://www.swynk.com/friends/knight
> > http://www.sqlservercentral.com



> > > Does Excel need to be installed on the Server reading Excel files with
> > DTS?

> > > We're trying to read these excel files on Novell Servers without much
> > > success.  We can get it to work when we run DTS from our client
machines
> > > which have Excel but when I use terminal server into our server or
> > schedule
> > > it as a job it fails.  Our server doesn't have Excel installed on it.

> > > I know SECURITY is a flag here but the Server Agent is in the same
> Domain
> > > group as we are and was given the same novell login script.  Also
we're
> > > using UNC naming and the UNC paths I type into explorer while logged
in
> > > locally on the server as the Server Agent get me to the files I want
to
> > > read.

> > > This is the error from the job history:

> > > Error string:  '\\SERVER\VOL1\FINBUDG\Budget 2001\Dept2001\Engineering
> > > Departments\Expense\Budget2001_FirstLastName.xls' 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.      Error source:  Microsoft
JET
> > > Database Engine

> > > putting this string into explorer on the server get's me to the file.


 
 
 

Excel and DTS

Post by Andy Rittin » Tue, 07 Nov 2000 04:00:00


I ended up using robocopy.exe (nt resource kit) to move all the excel files
off the novell network file server and put them onto the local HD.  I did
that with the the server and user account that was failing on he dts
packages.  It can read the files fine now (for some reason).  Unfortunately
time limitations didn't let me figure out the cause. The agent had all
permissions to the file on the network drive....

thanks


> Andy:

> On the scheduler when setting up a job step, select Operating System
Command
> (CmdExec) and put this in the Command window with your appropriate
> information:

> dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name
> /Mpackage-password

> You can open up a DOS window and copy the very same thing to the prompt
and
> run it from there.  This helps to see if it will work without monkeying
> around with the job interface.

> Regards,

> Mike



> > Ok I went down to the server and logged on with the SQL Server Agent
> account
> > instead of using terminal server to log in with that account.  I can
> access
> > and read the excel file when I execute it from DTS but the package fails
> > when scheduled.  I'm wondering if the SQLCmdExec account has anything to
> do
> > with scheduled jobs?



> > > It shouldn't have to be installed on the executing server since you're
> > using
> > > the OLE DB provider.  Have you tried to sign into the server with the
> > > account starting SQL Server agent and see if he has permissions to the
> > file.
> > > Can you see the file when you interactively execute the package with
> your
> > > login permissions?

> > > --

> > > Brian Knight
> > > http://www.swynk.com/friends/knight
> > > http://www.sqlservercentral.com



> > > > Does Excel need to be installed on the Server reading Excel files
with
> > > DTS?

> > > > We're trying to read these excel files on Novell Servers without
much
> > > > success.  We can get it to work when we run DTS from our client
> machines
> > > > which have Excel but when I use terminal server into our server or
> > > schedule
> > > > it as a job it fails.  Our server doesn't have Excel installed on
it.

> > > > I know SECURITY is a flag here but the Server Agent is in the same
> > Domain
> > > > group as we are and was given the same novell login script.  Also
> we're
> > > > using UNC naming and the UNC paths I type into explorer while logged
> in
> > > > locally on the server as the Server Agent get me to the files I want
> to
> > > > read.

> > > > This is the error from the job history:

> > > > Error string:  '\\SERVER\VOL1\FINBUDG\Budget

2001\Dept2001\Engineering

- Show quoted text -

Quote:> > > > Departments\Expense\Budget2001_FirstLastName.xls' 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.      Error source:  Microsoft
> JET
> > > > Database Engine

> > > > putting this string into explorer on the server get's me to the
file.

 
 
 

Excel and DTS

Post by m_c_b_a_.. » Sat, 18 Nov 2000 04:00:00


Well here was the problem.  I did not use SQL DTS scheduler to do the
jobs.  I still cannot get it workin in the query analyzer too.  But i
noticed it work from the NT's cmd.exe prompt if i execute dtsrun.exe
with its parameters.  But then i also noticed that it would fail there
too if the connection was lost to the Novell Server.  We have had
problems in the past and for our smtp connector that it lost and had to
reconnect all the time.  So by saying in NT box...
c:\explorer //moranfl/vol1/
             c:\dtns Sservername Npackagename Uuserid  << sa no password
forcing it to connect and run.  Now in order to schedule these commands
i used VB which u can select what time what commands pauses ect. If
interesed in the source just ask  [=



> I ended up using robocopy.exe (nt resource kit) to move all the excel
files
> off the novell network file server and put them onto the local HD.  I
did
> that with the the server and user account that was failing on he dts
> packages.  It can read the files fine now (for some reason).
Unfortunately
> time limitations didn't let me figure out the cause. The agent had all
> permissions to the file on the network drive....

> thanks



> > Andy:

> > On the scheduler when setting up a job step, select Operating System
> Command
> > (CmdExec) and put this in the Command window with your appropriate
> > information:

> > dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name
> > /Mpackage-password

> > You can open up a DOS window and copy the very same thing to the
prompt
> and
> > run it from there.  This helps to see if it will work without
monkeying
> > around with the job interface.

> > Regards,

> > Mike



> > > Ok I went down to the server and logged on with the SQL Server
Agent
> > account
> > > instead of using terminal server to log in with that account.  I
can
> > access
> > > and read the excel file when I execute it from DTS but the
package fails
> > > when scheduled.  I'm wondering if the SQLCmdExec account has
anything to
> > do
> > > with scheduled jobs?



> > > > It shouldn't have to be installed on the executing server since
you're
> > > using
> > > > the OLE DB provider.  Have you tried to sign into the server
with the
> > > > account starting SQL Server agent and see if he has permissions
to the
> > > file.
> > > > Can you see the file when you interactively execute the package
with
> > your
> > > > login permissions?

> > > > --

> > > > Brian Knight
> > > > http://www.swynk.com/friends/knight
> > > > http://www.sqlservercentral.com



> > > > > Does Excel need to be installed on the Server reading Excel
files
> with
> > > > DTS?

> > > > > We're trying to read these excel files on Novell Servers
without
> much
> > > > > success.  We can get it to work when we run DTS from our
client
> > machines
> > > > > which have Excel but when I use terminal server into our
server or
> > > > schedule
> > > > > it as a job it fails.  Our server doesn't have Excel
installed on
> it.

> > > > > I know SECURITY is a flag here but the Server Agent is in the
same
> > > Domain
> > > > > group as we are and was given the same novell login script.
Also
> > we're
> > > > > using UNC naming and the UNC paths I type into explorer while
logged
> > in
> > > > > locally on the server as the Server Agent get me to the files
I want
> > to
> > > > > read.

> > > > > This is the error from the job history:

> > > > > Error string:  '\\SERVER\VOL1\FINBUDG\Budget
> 2001\Dept2001\Engineering
> > > > > Departments\Expense\Budget2001_FirstLastName.xls' 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.      Error source:
Microsoft
> > JET
> > > > > Database Engine

> > > > > putting this string into explorer on the server get's me to
the
> file.

Sent via Deja.com http://www.deja.com/
Before you buy.
 
 
 

1. DTS problem SQL7->DTS->Excel->DTS->SQL7

I'm using DTS to export SQL data to an Excel file, let user make updates in
excel, then import back into SQL, deleting old data first.

Problem:
After user edits numeric cells in Excel, in DTS package/Preview shows new
values to be empty or null. Actual import shows same result where new values
in Excel become null in SQL.

I import same Excel file into an Access table. no problem.

2. Problems reading AP/PRO DAT tape to D3/Linux

3. Unable to establish connection with a pwd protected Excel ( in DTS)

4. SQLCONNECT

5. deleting rows from Excel via DTS

6. jdbc.sql.SQLException HUH?

7. Excel and DTS

8. MFC ODBC on XP

9. Appending not deleting in Excel using DTS

10. Column display null while exporting from Excel Through DTS

11. Determine cell colours in Excel for DTS Import

12. Export data from SQL to Excel using DTS Package

13. Excel and DTS