DTS runs OK, but not scheduled job

Hi,
When I run DTS manually, it works fine. But when I run the scheduled job, it
failes.
The error said cannot find a file specified. It imports Excel file to
SQL2000 Server database. I set same domain user id for DTS creater and Agent
executer and job owner.
I read several articles same problem like this, but I haven't get solution...
Thank you,
Masako
Where is located the EXCEL File?
It should be located on server and not on the your workstation.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> Hi,
> When I run DTS manually, it works fine. But when I run the scheduled job,
it
> failes.
> The error said cannot find a file specified. It imports Excel file to
> SQL2000 Server database. I set same domain user id for DTS creater and
Agent
> executer and job owner.
> I read several articles same problem like this, but I haven't get
solution...
> --
> Thank you,
|||Hi Uri,
Does it have to? The Excel file is located on another server.
I had no problem like this job flow previous SQL Server. We used to Windows
NT server + SQL7, now new server is Windows2000 english version + SQL2000
Japanese version.
"Uri Dimant" wrote:

> Masako
> Where is located the EXCEL File?
> It should be located on server and not on the your workstation.
>
> "Masako" <Masako@.discussions.microsoft.com> wrote in message
> news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> it
> Agent
> solution...
>
>
|||Maskao
Make sure that SQL Server Agent is running under Domain Account not a Local
account.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:CC48C0DC-A9F9-4E3A-8277-F6C55BF6C588@.microsoft.com...
> Hi Uri,
> Does it have to? The Excel file is located on another server.
> I had no problem like this job flow previous SQL Server. We used to
Windows[vbcol=seagreen]
> NT server + SQL7, now new server is Windows2000 english version + SQL2000
> Japanese version.
> "Uri Dimant" wrote:
job,[vbcol=seagreen]

DTS runs OK, but not scheduled job

Hi,
When I run DTS manually, it works fine. But when I run the scheduled job, it
failes.
The error said cannot find a file specified. It imports Excel file to
SQL2000 Server database. I set same domain user id for DTS creater and Agent
executer and job owner.
I read several articles same problem like this, but I haven't get solution..
.
Thank you,Masako
Where is located the EXCEL File?
It should be located on server and not on the your workstation.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> Hi,
> When I run DTS manually, it works fine. But when I run the scheduled job,
it
> failes.
> The error said cannot find a file specified. It imports Excel file to
> SQL2000 Server database. I set same domain user id for DTS creater and
Agent
> executer and job owner.
> I read several articles same problem like this, but I haven't get
solution...
> --
> Thank you,|||Hi Uri,
Does it have to? The Excel file is located on another server.
I had no problem like this job flow previous SQL Server. We used to Windows
NT server + SQL7, now new server is Windows2000 english version + SQL2000
Japanese version.
"Uri Dimant" wrote:

> Masako
> Where is located the EXCEL File?
> It should be located on server and not on the your workstation.
>
> "Masako" <Masako@.discussions.microsoft.com> wrote in message
> news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> it
> Agent
> solution...
>
>|||Maskao
Make sure that SQL Server Agent is running under Domain Account not a Local
account.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:CC48C0DC-A9F9-4E3A-8277-F6C55BF6C588@.microsoft.com...
> Hi Uri,
> Does it have to? The Excel file is located on another server.
> I had no problem like this job flow previous SQL Server. We used to
Windows[vbcol=seagreen]
> NT server + SQL7, now new server is Windows2000 english version + SQL2000
> Japanese version.
> "Uri Dimant" wrote:
>
job,[vbcol=seagreen]

DTS runs OK, but not scheduled job

Hi,
When I run DTS manually, it works fine. But when I run the scheduled job, it
failes.
The error said cannot find a file specified. It imports Excel file to
SQL2000 Server database. I set same domain user id for DTS creater and Agent
executer and job owner.
I read several articles same problem like this, but I haven't get solution...
--
Thank you,Masako
Where is located the EXCEL File?
It should be located on server and not on the your workstation.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> Hi,
> When I run DTS manually, it works fine. But when I run the scheduled job,
it
> failes.
> The error said cannot find a file specified. It imports Excel file to
> SQL2000 Server database. I set same domain user id for DTS creater and
Agent
> executer and job owner.
> I read several articles same problem like this, but I haven't get
solution...
> --
> Thank you,|||Hi Uri,
Does it have to? The Excel file is located on another server.
I had no problem like this job flow previous SQL Server. We used to Windows
NT server + SQL7, now new server is Windows2000 english version + SQL2000
Japanese version.
"Uri Dimant" wrote:
> Masako
> Where is located the EXCEL File?
> It should be located on server and not on the your workstation.
>
> "Masako" <Masako@.discussions.microsoft.com> wrote in message
> news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> > Hi,
> >
> > When I run DTS manually, it works fine. But when I run the scheduled job,
> it
> > failes.
> > The error said cannot find a file specified. It imports Excel file to
> > SQL2000 Server database. I set same domain user id for DTS creater and
> Agent
> > executer and job owner.
> >
> > I read several articles same problem like this, but I haven't get
> solution...
> >
> > --
> > Thank you,
>
>|||Maskao
Make sure that SQL Server Agent is running under Domain Account not a Local
account.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:CC48C0DC-A9F9-4E3A-8277-F6C55BF6C588@.microsoft.com...
> Hi Uri,
> Does it have to? The Excel file is located on another server.
> I had no problem like this job flow previous SQL Server. We used to
Windows
> NT server + SQL7, now new server is Windows2000 english version + SQL2000
> Japanese version.
> "Uri Dimant" wrote:
> > Masako
> > Where is located the EXCEL File?
> > It should be located on server and not on the your workstation.
> >
> >
> > "Masako" <Masako@.discussions.microsoft.com> wrote in message
> > news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> > > Hi,
> > >
> > > When I run DTS manually, it works fine. But when I run the scheduled
job,
> > it
> > > failes.
> > > The error said cannot find a file specified. It imports Excel file to
> > > SQL2000 Server database. I set same domain user id for DTS creater and
> > Agent
> > > executer and job owner.
> > >
> > > I read several articles same problem like this, but I haven't get
> > solution...
> > >
> > > --
> > > Thank you,
> >
> >
> >

dts package help

I'm creating my first dts package. I've specified my sql server connection and my excel file connection. I've added a bulk insert to import my excel data into a work table.

I've specified the row delimiter format as {LF} and Tab for column, but when I try to execute the dts package it fails and I get the following message:

Bulk Insert fails: Column is too long in the data file for row 1, column 3. Make sure the field terminator area specified correctly.
Bulk Insert data conversion error (truncation) for row 1, column 2 (LastName).

I don't understand why I'm getting a truncation error, there should be plenty of space for the insertion? I'm missing something simple I'm sure.

Any help is appreciated.Why are you specifying bulk insert with {LF} and Tab delimiters? Excel does not store data in that format, and that is why your DTS package can't import it using that format.|||This is the first DTS package I'm attempting to create, so I apologize if my questions are novice. When I added the bulk insert it asks to the specify a format for the row and column delimiter. Isn't the row delimiter a carriage return and the column delimiter a tab in an Excel file? What should I be using? I'm using DTS designer in SQL Server 2000.

I appreciate the help. Thank you.|||You probably want to create a connection to your Excel file as an Excel file, rather than as a text file. Excel files do not have row or column delimiters because the file format itself logically provides those delimiters.

-PatP|||I double checked and I did specify an Excel file as the connection, not a text file.

It's when I specify the Bulk Inset task that I have the option of setting a format type or format file. I've tried both options, but still get the same error?|||Assuming you are using SQL 2000, create an "idiot" spreadsheet with two or three columns and then create a DTS job to import it into a table using the Import Export Wizard. Save that job, and look at it to see how the Wizard did the import.

-PatP|||You want to use an Excel connection as the source, a Microsoft OLE DB Provider for SQL Server as the destination, and a Transform Data Task to move your records.

DTS Package And Schedule Job

Hi ,
I create a DTS package to run a SQL Task. This task will call a batch
file. This batch is to unzip the specified file. It works fine in the DTS
design enviroment but after I scheduled this package , it failed to run.
I try to schedule this batch is the windows scheduler , it works fine.
Any idea on it ?
Travis TanYour SQL Agent permissions are likely the culprit...
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:9D9913AE-214A-4D76-9BD5-F1B5251A7993@.microsoft.com...
> Hi ,
> I create a DTS package to run a SQL Task. This task will call a batch
> file. This batch is to unzip the specified file. It works fine in the DTS
> design enviroment but after I scheduled this package , it failed to run.
> I try to schedule this batch is the windows scheduler , it works fine.
> Any idea on it ?
> --
> Travis Tan

DTS Package And Schedule Job

Hi ,
I create a DTS package to run a SQL Task. This task will call a batch
file. This batch is to unzip the specified file. It works fine in the DTS
design enviroment but after I scheduled this package , it failed to run.
I try to schedule this batch is the windows scheduler , it works fine.
Any idea on it ?
--
Travis TanYour SQL Agent permissions are likely the culprit...
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:9D9913AE-214A-4D76-9BD5-F1B5251A7993@.microsoft.com...
> Hi ,
> I create a DTS package to run a SQL Task. This task will call a batch
> file. This batch is to unzip the specified file. It works fine in the DTS
> design enviroment but after I scheduled this package , it failed to run.
> I try to schedule this batch is the windows scheduler , it works fine.
> Any idea on it ?
> --
> Travis Tan

DTS job fails when scheduled from SQL Agent

Folks,
I have a DTS job that imports data from text files (specified as odbc connections) from a remote server into a sql table on the same SQL server that the job has been created on.
The job runs fine if execute directly from the server. If I schedule the same job on the server (through jobs) executing under the same user, the job fails with..

Executed as user: mydomain\mylogin . ...art: DTSStep_DTSActiveScriptTask_1 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: [Microsoft][ODBC Text Driver] '(unknown)' 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 OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 1023 (3FF) Error string: Error source: Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 Error: -2147220440 (80040428); Provider Error: 0 (0) Error string: Package failed because Step 'DTSStep_DTSDataPumpTask_1' failed. Error source: Microsoft Data Transformation Services ... Process Exit Code 1. The step failed.

How come it loses the path to the file when I dont run it directly?
Cheers
MickDTS runs in the context of the client machine when you run it directly. That means that if you run it from Enterprise Manager on your local PC then it uses the settings, drive mappings and ODBC drivers of your workstation. When a DTS package is run by SQL Agent, it uses the settings from the Server. You have to ensure that the server has all the settings that your local machine does.

Be sure not to use mapped drives to specify file locations -- use UNC instead. This is because a mapped drive only exists in the context of a logged in user. SQL Agent is a service and thus is not logged in.

I hope this makes some sense; I still find this a difficult topic to explain clearly even after dealing with it for five years.

Regards,

hmscott|||Thanks for that,
Thing is I have done every step from package creation to scheduling ON the server itself through terminal services. I thought SQL Agent would be aware of these server-based system DSN's. Ill have a go at UNC then.
Cheers
ML|||http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074 - KBA to schedule DTS as a scheduled job and troubleshoot any issues.

HTH|||Thanks folks,
I used UNC text file sources instead of odbc text connections. Worked great.
Cheers
Mick

DTS job error

Hi,
I scheduled a DTS, with right click above it, for every 2
hours. A job is automatically created to control the
execution that a specified. I save the DTS within SQL
Server with dba user. If i change DBA password, my job
execution reports an error saying, login failed for user
dba ..., i don't have this user within any step regarding
the DTS, why this happens ? if i delete the job and
recreate it using right click above the DTS, then the job
start working fine. Any ideas ?
Thanks a lot
Mike
I would guess that you may have scheduled the packages using
the DTS schedule package functionality - you right click on
the package on select Schedule Package. In this case, the
job will create an encrypted string for the dtsrun utility -
the command in the job step looks like:
DTSRUN /~Zxxxxxxxxxxxx.
The login and password that is encrypted in this string is
based upon what login and password was used in Enterprise
Manager to register the server. If you change the password
that was used when scheduling the package, the password
embedded in the encrypted string is no longer valid.
You can either change the dtsrun commands in the jobs, just
entering the dtsrun parameters yourself.
Or you can use the dtsrunui utility to generate a new
encrypted command. From the advanced button on the dtsrunui
utility, there is an option to generate a command line. It
will be based on whatever login you use when the dtsrunui
utility starts.
-Sue
On Thu, 1 Apr 2004 06:48:24 -0800, "Mike"
<anonymous@.discussions.microsoft.com> wrote:

>Hi,
>I scheduled a DTS, with right click above it, for every 2
>hours. A job is automatically created to control the
>execution that a specified. I save the DTS within SQL
>Server with dba user. If i change DBA password, my job
>execution reports an error saying, login failed for user
>dba ..., i don't have this user within any step regarding
>the DTS, why this happens ? if i delete the job and
>recreate it using right click above the DTS, then the job
>start working fine. Any ideas ?
>Thanks a lot
>Mike
|||I guess you are saving the package in msdb.
It uses the username and password to load the package not to run it.
You can create the job yourself and code the dtsrun command so that you can change it. It means that the password will be in clear though.
I prefer to save packages to storage files and load them from there - it nmakes them easier to handle.
|||Thanks a lot Sue, that makes sense, but if i edit EM
properties and specify other user than the user that i
used to save the package, for instance sa, i get the error
login failed for user sa ... ?? This is strange. I've
got errors for all the users i used.
Any idea ?

>--Original Message--
>I would guess that you may have scheduled the packages
using
>the DTS schedule package functionality - you right click
on
>the package on select Schedule Package. In this case, the
>job will create an encrypted string for the dtsrun
utility -
>the command in the job step looks like:
>DTSRUN /~Zxxxxxxxxxxxx.
>The login and password that is encrypted in this string is
>based upon what login and password was used in Enterprise
>Manager to register the server. If you change the password
>that was used when scheduling the package, the password
>embedded in the encrypted string is no longer valid.
>You can either change the dtsrun commands in the jobs,
just
>entering the dtsrun parameters yourself.
>Or you can use the dtsrunui utility to generate a new
>encrypted command. From the advanced button on the
dtsrunui
>utility, there is an option to generate a command line. It
>will be based on whatever login you use when the dtsrunui
>utility starts.
>-Sue
>On Thu, 1 Apr 2004 06:48:24 -0800, "Mike"
><anonymous@.discussions.microsoft.com> wrote:
2
regarding
job
>.
>
|||Sorry, the login failed is for dba you and Nigel are
right, sql server use dba for load the DTS under msdb
database and then fails when i change the password.
Thanks a lot

>--Original Message--
>Thanks a lot Sue, that makes sense, but if i edit EM
>properties and specify other user than the user that i
>used to save the package, for instance sa, i get the
error
>login failed for user sa ... ?? This is strange.
I've
>got errors for all the users i used.
>Any idea ?
>
>using
>on
>utility -
is
password
>just
>dtsrunui
It
>2
user
>regarding
>job
>.
>

DTS job error

Hi,
I scheduled a DTS, with right click above it, for every 2
hours. A job is automatically created to control the
execution that a specified. I save the DTS within SQL
Server with dba user. If i change DBA password, my job
execution reports an error saying, login failed for user
dba ..., i don't have this user within any step regarding
the DTS, why this happens ? if i delete the job and
recreate it using right click above the DTS, then the job
start working fine. Any ideas '
Thanks a lot
MikeI would guess that you may have scheduled the packages using
the DTS schedule package functionality - you right click on
the package on select Schedule Package. In this case, the
job will create an encrypted string for the dtsrun utility -
the command in the job step looks like:
DTSRUN /~Zxxxxxxxxxxxx.
The login and password that is encrypted in this string is
based upon what login and password was used in Enterprise
Manager to register the server. If you change the password
that was used when scheduling the package, the password
embedded in the encrypted string is no longer valid.
You can either change the dtsrun commands in the jobs, just
entering the dtsrun parameters yourself.
Or you can use the dtsrunui utility to generate a new
encrypted command. From the advanced button on the dtsrunui
utility, there is an option to generate a command line. It
will be based on whatever login you use when the dtsrunui
utility starts.
-Sue
On Thu, 1 Apr 2004 06:48:24 -0800, "Mike"
<anonymous@.discussions.microsoft.com> wrote:

>Hi,
>I scheduled a DTS, with right click above it, for every 2
>hours. A job is automatically created to control the
>execution that a specified. I save the DTS within SQL
>Server with dba user. If i change DBA password, my job
>execution reports an error saying, login failed for user
>dba ..., i don't have this user within any step regarding
>the DTS, why this happens ? if i delete the job and
>recreate it using right click above the DTS, then the job
>start working fine. Any ideas '
>Thanks a lot
>Mike|||I guess you are saving the package in msdb.
It uses the username and password to load the package not to run it.
You can create the job yourself and code the dtsrun command so that you can
change it. It means that the password will be in clear though.
I prefer to save packages to storage files and load them from there - it nma
kes them easier to handle.|||Thanks a lot Sue, that makes sense, but if i edit EM
properties and specify other user than the user that i
used to save the package, for instance sa, i get the error
login failed for user sa ... '? This is strange. I've
got errors for all the users i used.
Any idea ?

>--Original Message--
>I would guess that you may have scheduled the packages
using
>the DTS schedule package functionality - you right click
on
>the package on select Schedule Package. In this case, the
>job will create an encrypted string for the dtsrun
utility -
>the command in the job step looks like:
>DTSRUN /~Zxxxxxxxxxxxx.
>The login and password that is encrypted in this string is
>based upon what login and password was used in Enterprise
>Manager to register the server. If you change the password
>that was used when scheduling the package, the password
>embedded in the encrypted string is no longer valid.
>You can either change the dtsrun commands in the jobs,
just
>entering the dtsrun parameters yourself.
>Or you can use the dtsrunui utility to generate a new
>encrypted command. From the advanced button on the
dtsrunui
>utility, there is an option to generate a command line. It
>will be based on whatever login you use when the dtsrunui
>utility starts.
>-Sue
>On Thu, 1 Apr 2004 06:48:24 -0800, "Mike"
><anonymous@.discussions.microsoft.com> wrote:
>
2
regarding
job
>.
>|||Sorry, the login failed is for dba you and Nigel are
right, sql server use dba for load the DTS under msdb
database and then fails when i change the password.
Thanks a lot

>--Original Message--
>Thanks a lot Sue, that makes sense, but if i edit EM
>properties and specify other user than the user that i
>used to save the package, for instance sa, i get the
error
>login failed for user sa ... '? This is strange.
I've
>got errors for all the users i used.
>Any idea ?
>
>using
>on
>utility -
is
password
>just
>dtsrunui
It
>2
user
>regarding
>job
>.
>