DTS problem, please help!

I have yet another weired problem for which I have no answer.

I have a DTS process setup that needs to fireup at night, which uploads data from text file into upload table.
It runs perfact.

The problem started when I tried to schedule it and then reschedule it for another timeperiod.

I reschduled this process for about 3 times for different timeperiod. Meaning I had originally set it to fire at 1:00 am then later changed to 2:00 and then to 3:00.

Technically, it should overwrite the last schedule time and fire at the new scheduled time, ie. 3:00 am daily.

But somehow DTS is keeping the history of all the previous scheduled times and is now firing at 1:00 am, 2:00 am and also at 3:00 am.

I also looked at the Versions option in that DTS process's right click menu and removed all, but the latest version. But still the same behaviour.

Any help on this is much appreciated.

Many Thanks!Did you actually change the SQL Agent job or did you just right-click the DTS package and schedule it? Check the jobs in SQL Agent to see if there are three of them in there.

dts package failure when setup as job

HI I have a dts package (SQL 2000) that I can execute from Enterprise manager
opened on a client machine connected to a server. When I schedule it as a
job though Enterprise manager on the client machine connected to the server
the job fails. The package connects to an SQL database on the server as well
as an oracle database on another server. On job failure the event is written
to the windows event log on the server. The error message is Source SQL
Server agent,
status failed, the job failed.
I am thinking it may have to do with credentials, any ideas how to
troubleshoot this?
thanks.
Paul G
Software engineer.
Paul,
Yes, when you run it from Enterprise Manager, you are running under your
credentials. A scheduled job has different credentials.
When you schedule the DTS package from SQL Agent, it will run either as the
server account or as the account defined in xp_sqlagent_proxy_account for
your server. The SQL Server 2000 Books Online article on
xp_sqlagent_proxy_account has a fairly clear explanation.
Once you determine which credentials your job is using, then you can have
appropriate access granted to that account.
RLF
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4E204B8C-1174-4713-A420-273E72F92178@.microsoft.com...
> HI I have a dts package (SQL 2000) that I can execute from Enterprise
> manager
> opened on a client machine connected to a server. When I schedule it as a
> job though Enterprise manager on the client machine connected to the
> server
> the job fails. The package connects to an SQL database on the server as
> well
> as an oracle database on another server. On job failure the event is
> written
> to the windows event log on the server. The error message is Source SQL
> Server agent,
> status failed, the job failed.
> I am thinking it may have to do with credentials, any ideas how to
> troubleshoot this?
> thanks.
> --
> Paul G
> Software engineer.
|||ok thanks for the information. Another problem is part of the package writes
to an Oracle database but the oracle client software is not installed on the
server.
Paul G
Software engineer.
"Russell Fields" wrote:

> Paul,
> Yes, when you run it from Enterprise Manager, you are running under your
> credentials. A scheduled job has different credentials.
> When you schedule the DTS package from SQL Agent, it will run either as the
> server account or as the account defined in xp_sqlagent_proxy_account for
> your server. The SQL Server 2000 Books Online article on
> xp_sqlagent_proxy_account has a fairly clear explanation.
> Once you determine which credentials your job is using, then you can have
> appropriate access granted to that account.
> RLF
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:4E204B8C-1174-4713-A420-273E72F92178@.microsoft.com...
>
>
|||Paul,
So that is the other part. When you run a DTS package from Enterprise
Manager in runs on the same machine that Enterprise Manager is running on.
So, if you are running EM on your desktop, that is where DTS runs as well.
Naturally if you have the Oracle client software on your desktop, it will
work for you.
If that is the case, then you will need to evaluate putting the Oracle
client software on the SQL Server. I don't have any experience with Oracle,
but plenty of people have successfully used DTS, Linked Servers, etc between
SQL Server and Oracle Server.
RLF
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:C5E14CA8-6327-4F64-854B-2C08274CE61D@.microsoft.com...[vbcol=seagreen]
> ok thanks for the information. Another problem is part of the package
> writes
> to an Oracle database but the oracle client software is not installed on
> the
> server.
> --
> Paul G
> Software engineer.
>
> "Russell Fields" wrote:
|||thanks for the additional information. I was wondering why it worked from my
client machine, but I do have the Oracle client software installed on it. I
had it on the server before but we ran into an issue with Appachi effecting
IIS so someone removed all of the Oracle software.
Paul G
Software engineer.
"Russell Fields" wrote:

> Paul,
> So that is the other part. When you run a DTS package from Enterprise
> Manager in runs on the same machine that Enterprise Manager is running on.
> So, if you are running EM on your desktop, that is where DTS runs as well.
> Naturally if you have the Oracle client software on your desktop, it will
> work for you.
> If that is the case, then you will need to evaluate putting the Oracle
> client software on the SQL Server. I don't have any experience with Oracle,
> but plenty of people have successfully used DTS, Linked Servers, etc between
> SQL Server and Oracle Server.
> RLF
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:C5E14CA8-6327-4F64-854B-2C08274CE61D@.microsoft.com...
>
>

dts package failure when setup as job

HI I have a dts package (SQL 2000) that I can execute from Enterprise manager
opened on a client machine connected to a server. When I schedule it as a
job though Enterprise manager on the client machine connected to the server
the job fails. The package connects to an SQL database on the server as well
as an oracle database on another server. On job failure the event is written
to the windows event log on the server. The error message is Source SQL
Server agent,
status failed, the job failed.
I am thinking it may have to do with credentials, any ideas how to
troubleshoot this?
thanks.
--
Paul G
Software engineer.Paul,
Yes, when you run it from Enterprise Manager, you are running under your
credentials. A scheduled job has different credentials.
When you schedule the DTS package from SQL Agent, it will run either as the
server account or as the account defined in xp_sqlagent_proxy_account for
your server. The SQL Server 2000 Books Online article on
xp_sqlagent_proxy_account has a fairly clear explanation.
Once you determine which credentials your job is using, then you can have
appropriate access granted to that account.
RLF
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4E204B8C-1174-4713-A420-273E72F92178@.microsoft.com...
> HI I have a dts package (SQL 2000) that I can execute from Enterprise
> manager
> opened on a client machine connected to a server. When I schedule it as a
> job though Enterprise manager on the client machine connected to the
> server
> the job fails. The package connects to an SQL database on the server as
> well
> as an oracle database on another server. On job failure the event is
> written
> to the windows event log on the server. The error message is Source SQL
> Server agent,
> status failed, the job failed.
> I am thinking it may have to do with credentials, any ideas how to
> troubleshoot this?
> thanks.
> --
> Paul G
> Software engineer.|||ok thanks for the information. Another problem is part of the package writes
to an Oracle database but the oracle client software is not installed on the
server.
--
Paul G
Software engineer.
"Russell Fields" wrote:
> Paul,
> Yes, when you run it from Enterprise Manager, you are running under your
> credentials. A scheduled job has different credentials.
> When you schedule the DTS package from SQL Agent, it will run either as the
> server account or as the account defined in xp_sqlagent_proxy_account for
> your server. The SQL Server 2000 Books Online article on
> xp_sqlagent_proxy_account has a fairly clear explanation.
> Once you determine which credentials your job is using, then you can have
> appropriate access granted to that account.
> RLF
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:4E204B8C-1174-4713-A420-273E72F92178@.microsoft.com...
> > HI I have a dts package (SQL 2000) that I can execute from Enterprise
> > manager
> > opened on a client machine connected to a server. When I schedule it as a
> > job though Enterprise manager on the client machine connected to the
> > server
> > the job fails. The package connects to an SQL database on the server as
> > well
> > as an oracle database on another server. On job failure the event is
> > written
> > to the windows event log on the server. The error message is Source SQL
> > Server agent,
> > status failed, the job failed.
> > I am thinking it may have to do with credentials, any ideas how to
> > troubleshoot this?
> > thanks.
> > --
> > Paul G
> > Software engineer.
>
>|||Paul,
So that is the other part. When you run a DTS package from Enterprise
Manager in runs on the same machine that Enterprise Manager is running on.
So, if you are running EM on your desktop, that is where DTS runs as well.
Naturally if you have the Oracle client software on your desktop, it will
work for you.
If that is the case, then you will need to evaluate putting the Oracle
client software on the SQL Server. I don't have any experience with Oracle,
but plenty of people have successfully used DTS, Linked Servers, etc between
SQL Server and Oracle Server.
RLF
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:C5E14CA8-6327-4F64-854B-2C08274CE61D@.microsoft.com...
> ok thanks for the information. Another problem is part of the package
> writes
> to an Oracle database but the oracle client software is not installed on
> the
> server.
> --
> Paul G
> Software engineer.
>
> "Russell Fields" wrote:
>> Paul,
>> Yes, when you run it from Enterprise Manager, you are running under your
>> credentials. A scheduled job has different credentials.
>> When you schedule the DTS package from SQL Agent, it will run either as
>> the
>> server account or as the account defined in xp_sqlagent_proxy_account for
>> your server. The SQL Server 2000 Books Online article on
>> xp_sqlagent_proxy_account has a fairly clear explanation.
>> Once you determine which credentials your job is using, then you can have
>> appropriate access granted to that account.
>> RLF
>>
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:4E204B8C-1174-4713-A420-273E72F92178@.microsoft.com...
>> > HI I have a dts package (SQL 2000) that I can execute from Enterprise
>> > manager
>> > opened on a client machine connected to a server. When I schedule it
>> > as a
>> > job though Enterprise manager on the client machine connected to the
>> > server
>> > the job fails. The package connects to an SQL database on the server
>> > as
>> > well
>> > as an oracle database on another server. On job failure the event is
>> > written
>> > to the windows event log on the server. The error message is Source
>> > SQL
>> > Server agent,
>> > status failed, the job failed.
>> > I am thinking it may have to do with credentials, any ideas how to
>> > troubleshoot this?
>> > thanks.
>> > --
>> > Paul G
>> > Software engineer.
>>|||thanks for the additional information. I was wondering why it worked from my
client machine, but I do have the Oracle client software installed on it. I
had it on the server before but we ran into an issue with Appachi effecting
IIS so someone removed all of the Oracle software.
--
Paul G
Software engineer.
"Russell Fields" wrote:
> Paul,
> So that is the other part. When you run a DTS package from Enterprise
> Manager in runs on the same machine that Enterprise Manager is running on.
> So, if you are running EM on your desktop, that is where DTS runs as well.
> Naturally if you have the Oracle client software on your desktop, it will
> work for you.
> If that is the case, then you will need to evaluate putting the Oracle
> client software on the SQL Server. I don't have any experience with Oracle,
> but plenty of people have successfully used DTS, Linked Servers, etc between
> SQL Server and Oracle Server.
> RLF
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:C5E14CA8-6327-4F64-854B-2C08274CE61D@.microsoft.com...
> > ok thanks for the information. Another problem is part of the package
> > writes
> > to an Oracle database but the oracle client software is not installed on
> > the
> > server.
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "Russell Fields" wrote:
> >
> >> Paul,
> >>
> >> Yes, when you run it from Enterprise Manager, you are running under your
> >> credentials. A scheduled job has different credentials.
> >>
> >> When you schedule the DTS package from SQL Agent, it will run either as
> >> the
> >> server account or as the account defined in xp_sqlagent_proxy_account for
> >> your server. The SQL Server 2000 Books Online article on
> >> xp_sqlagent_proxy_account has a fairly clear explanation.
> >>
> >> Once you determine which credentials your job is using, then you can have
> >> appropriate access granted to that account.
> >>
> >> RLF
> >>
> >>
> >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> >> news:4E204B8C-1174-4713-A420-273E72F92178@.microsoft.com...
> >> > HI I have a dts package (SQL 2000) that I can execute from Enterprise
> >> > manager
> >> > opened on a client machine connected to a server. When I schedule it
> >> > as a
> >> > job though Enterprise manager on the client machine connected to the
> >> > server
> >> > the job fails. The package connects to an SQL database on the server
> >> > as
> >> > well
> >> > as an oracle database on another server. On job failure the event is
> >> > written
> >> > to the windows event log on the server. The error message is Source
> >> > SQL
> >> > Server agent,
> >> > status failed, the job failed.
> >> > I am thinking it may have to do with credentials, any ideas how to
> >> > troubleshoot this?
> >> > thanks.
> >> > --
> >> > Paul G
> >> > Software engineer.
> >>
> >>
> >>
>
>

dts package failure when setup as job

HI I have a dts package (SQL 2000) that I can execute from Enterprise manage
r
opened on a client machine connected to a server. When I schedule it as a
job though Enterprise manager on the client machine connected to the server
the job fails. The package connects to an SQL database on the server as wel
l
as an oracle database on another server. On job failure the event is writte
n
to the windows event log on the server. The error message is Source SQL
Server agent,
status failed, the job failed.
I am thinking it may have to do with credentials, any ideas how to
troubleshoot this?
thanks.
--
Paul G
Software engineer.Paul,
Yes, when you run it from Enterprise Manager, you are running under your
credentials. A scheduled job has different credentials.
When you schedule the DTS package from SQL Agent, it will run either as the
server account or as the account defined in xp_sqlagent_proxy_account for
your server. The SQL Server 2000 Books Online article on
xp_sqlagent_proxy_account has a fairly clear explanation.
Once you determine which credentials your job is using, then you can have
appropriate access granted to that account.
RLF
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4E204B8C-1174-4713-A420-273E72F92178@.microsoft.com...
> HI I have a dts package (SQL 2000) that I can execute from Enterprise
> manager
> opened on a client machine connected to a server. When I schedule it as a
> job though Enterprise manager on the client machine connected to the
> server
> the job fails. The package connects to an SQL database on the server as
> well
> as an oracle database on another server. On job failure the event is
> written
> to the windows event log on the server. The error message is Source SQL
> Server agent,
> status failed, the job failed.
> I am thinking it may have to do with credentials, any ideas how to
> troubleshoot this?
> thanks.
> --
> Paul G
> Software engineer.|||ok thanks for the information. Another problem is part of the package write
s
to an Oracle database but the oracle client software is not installed on the
server.
--
Paul G
Software engineer.
"Russell Fields" wrote:

> Paul,
> Yes, when you run it from Enterprise Manager, you are running under your
> credentials. A scheduled job has different credentials.
> When you schedule the DTS package from SQL Agent, it will run either as th
e
> server account or as the account defined in xp_sqlagent_proxy_account for
> your server. The SQL Server 2000 Books Online article on
> xp_sqlagent_proxy_account has a fairly clear explanation.
> Once you determine which credentials your job is using, then you can have
> appropriate access granted to that account.
> RLF
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:4E204B8C-1174-4713-A420-273E72F92178@.microsoft.com...
>
>|||Paul,
So that is the other part. When you run a DTS package from Enterprise
Manager in runs on the same machine that Enterprise Manager is running on.
So, if you are running EM on your desktop, that is where DTS runs as well.
Naturally if you have the Oracle client software on your desktop, it will
work for you.
If that is the case, then you will need to evaluate putting the Oracle
client software on the SQL Server. I don't have any experience with Oracle,
but plenty of people have successfully used DTS, Linked Servers, etc between
SQL Server and Oracle Server.
RLF
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:C5E14CA8-6327-4F64-854B-2C08274CE61D@.microsoft.com...[vbcol=seagreen]
> ok thanks for the information. Another problem is part of the package
> writes
> to an Oracle database but the oracle client software is not installed on
> the
> server.
> --
> Paul G
> Software engineer.
>
> "Russell Fields" wrote:
>|||thanks for the additional information. I was wondering why it worked from m
y
client machine, but I do have the Oracle client software installed on it. I
had it on the server before but we ran into an issue with Appachi effecting
IIS so someone removed all of the Oracle software.
--
Paul G
Software engineer.
"Russell Fields" wrote:

> Paul,
> So that is the other part. When you run a DTS package from Enterprise
> Manager in runs on the same machine that Enterprise Manager is running on.
> So, if you are running EM on your desktop, that is where DTS runs as well.
> Naturally if you have the Oracle client software on your desktop, it will
> work for you.
> If that is the case, then you will need to evaluate putting the Oracle
> client software on the SQL Server. I don't have any experience with Oracl
e,
> but plenty of people have successfully used DTS, Linked Servers, etc betwe
en
> SQL Server and Oracle Server.
> RLF
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:C5E14CA8-6327-4F64-854B-2C08274CE61D@.microsoft.com...
>
>

DTS output file with "Append " mode?

I have set up an export which use DTS service to extract data from a table to a Flat ( text) format file.

Is there any way setup this simple DTS as a " APPEND" mode? so that next time's data will append to the flat file output?

because the default way I can do for this flat file is " overwrite " .
the second time's run will always overwrite the privious run result.
even save the DTS scipt to VB file still can't find where to setup output file write mode.

Appreciate any help

Regards

GuyangDid you ever get an answer on this? I have the same requirement

Originally posted by guyang2000
I have set up an export which use DTS service to extract data from a table to a Flat ( text) format file.

Is there any way setup this simple DTS as a " APPEND" mode? so that next time's data will append to the flat file output?

because the default way I can do for this flat file is " overwrite " .
the second time's run will always overwrite the privious run result.
even save the DTS scipt to VB file still can't find where to setup output file write mode.

Appreciate any help

Regards

Guyang|||If you transform the data to a excel sheet instead of a flat file its always appended. you might think of making the DTS package in such a way that first step will move data to a excel file(which is always appended) and the second step will transform data from that excel to a text file. this might solve your purpose.|||Thank-you but,

That won't work for me. The number of entries exceeds the limitations of Excel.

Currently my only viable option is to export subsets into a number of text files and concatenating them into one text file.

To me this is less than ideal.|||I was about to post the same question, but hoping there was an append mode for bcp. There is no switch listed for appending, but I really need it.

The alternative for me would be to post results to a temporary table, then output that table to a file, but I am hoping to avoid it.|||I am currently doing exactly that. But be careful if the order is important..

I am transferring a varchar value into an ntext value in another table (only way to maintain paragraph formatting) to generate LDIF files for an X500 directory. I have attempted to force a sort order through a series of SQL UPDATE statements.

Unfortunately, the sort order gets destroyed by the size of the export to the point that the text file does not match the order of the table. Indexing of this table is impossible.

Originally posted by bpdWork
I was about to post the same question, but hoping there was an append mode for bcp. There is no switch listed for appending, but I really need it.

The alternative for me would be to post results to a temporary table, then output that table to a file, but I am hoping to avoid it.|||My requirement is to build an SAP feed of billing data for a customer. Since ordering is very important (the file is actually broken up into blocks by cost center), I was thinking of a temp table cosisting of either an int or timestamp id, and a varchar(4000), which is much larger than any line can be. I simply output ordering by the id field.

I thought about building a text field and appending, but the added work of pointers, plus adding the end of row terminiators, etc. is a pain in the ass, and seems like a breeding ground for bugs. Sounds like our requirements are different though.|||Take a serious look at an ntext (blob) as opposed to a nvarchar(4000). You can maintain the format and end charachters in an ntext to text output.

Originally posted by bpdWork
My requirement is to build an SAP feed of billing data for a customer. Since ordering is very important (the file is actually broken up into blocks by cost center), I was thinking of a temp table cosisting of either an int or timestamp id, and a varchar(4000), which is much larger than any line can be. I simply output ordering by the id field.

I thought about building a text field and appending, but the added work of pointers, plus adding the end of row terminiators, etc. is a pain in the ass, and seems like a breeding ground for bugs. Sounds like our requirements are different though.

DTS import new record can be replicated

I had merge replication setup. The replication is work fine if I insert new records
from enterprise manager. But, if use DTS to import new record from Access,
those new records can not be replicated. However, I updated one of those new record content, this updated new record will be replicated.
I appreicate you input.
James Cheng
James,
I have seen this type of behaviour before when if you bulk insert the rows
and choose the defaults, then FIRE_TRIGGERS is false and consequently the
rows are not added to MSmerge_contents. Disabling Fast Load may cause the
triggers to fire on new inserts or run sp_addtabletocontents to include the
rows already existing and then resynchronise.
Regards,
Paul Ibison
|||check out
http://support.microsoft.com/default...&Product=sql2k
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"James Cheng" <JamesCheng@.discussions.microsoft.com> wrote in message
news:3FF3AE3C-F66D-468F-92A3-EA3E2F879599@.microsoft.com...
> I had merge replication setup. The replication is work fine if I insert
new records
> from enterprise manager. But, if use DTS to import new record from
Access,
> those new records can not be replicated. However, I updated one of those
new record content, this updated new record will be replicated.
> I appreicate you input.
> James Cheng