So I've seen some posts about slow DTS performance. Many of them seem
to be related to the package execution times increasing over time.
That's not my situation.
I have this one DTS package that I had been running from SERVERA. It
normally runs in under 5 minutes. I had the package scheduled in a SQL
Server Agent 2000 job. The package was responsible for truncating a
table in a database on SERVERB and then copying data from a table in
an Oracle daabase on SERVERC.
I'm working on consolidating some SQL Server instances, so I figured
I'd add the Oracle client to SERVERA, move the DTS package to SERVERA
and recreate the job on SERVERA as well. So the DTS package runs
perfectly fine when I'm terninal serviced into the server and
executing the package from either the DTS designer or from the command
line. However, the package seems to hang when running the job from a
SQL Server Agent job.
I'm using the same user account when executing the job from terminal
session and job. The only difference I've seen so far is that SERVERB
is setup to run SQL Server and SQL Agent as DOMAIN\sqlservices, but
SERVERA is setup to run the services as sqlservices@.domain.com. I
can't imagine that would be the problem, though.
Do you have any suggestions as to what the problem could be. I can't
put the job on SERVERA into production until I figure why it doesn't
run like it does on SERVERB.
Thanks in advance,
Osolage
Hi
"Osolage" wrote:
> So I've seen some posts about slow DTS performance. Many of them seem
> to be related to the package execution times increasing over time.
> That's not my situation.
> I have this one DTS package that I had been running from SERVERA. It
> normally runs in under 5 minutes. I had the package scheduled in a SQL
> Server Agent 2000 job. The package was responsible for truncating a
> table in a database on SERVERB and then copying data from a table in
> an Oracle daabase on SERVERC.
> I'm working on consolidating some SQL Server instances, so I figured
> I'd add the Oracle client to SERVERA, move the DTS package to SERVERA
> and recreate the job on SERVERA as well. So the DTS package runs
> perfectly fine when I'm terninal serviced into the server and
> executing the package from either the DTS designer or from the command
> line. However, the package seems to hang when running the job from a
> SQL Server Agent job.
> I'm using the same user account when executing the job from terminal
> session and job. The only difference I've seen so far is that SERVERB
> is setup to run SQL Server and SQL Agent as DOMAIN\sqlservices, but
> SERVERA is setup to run the services as sqlservices@.domain.com. I
> can't imagine that would be the problem, though.
> Do you have any suggestions as to what the problem could be. I can't
> put the job on SERVERA into production until I figure why it doesn't
> run like it does on SERVERB.
> Thanks in advance,
> Osolage
It is not clear if you have specified SERVERB as part of the DTSRun command
or whether you have modified the job to run with distributed transactions. If
you have done the former then I would expect similar times and any
degredation would be network related.
John
Showing posts with label thats. Show all posts
Showing posts with label thats. Show all posts
Monday, March 19, 2012
DTS Package Logging - can u limit it?
Hello. When you turn Logging on, for a DTS package, it
appears to save the history forever? If that's true, is
there an option to clear DTS package logs, after like 30
days? or would you need to write something to do this
purge manually? THanks, Bruce
You can delete all the logs for a package from the Package Properties,
Logging tab.
You can also right click on local packages under the DTS node in
Enterprise Manager and select Logging. From here you have a bit more
control on selecting packages, selecting logs to delete, etc.
If you want to automate this somehow, you could code your own
maintenance of these using the DTS Object Model and schedule this
through a job. Take a look at the RemovePackageLogRecords method of
the PackageSQLServer object.
-Sue
On Fri, 29 Oct 2004 10:23:48 -0700, "Bruce de Freitas"
<bruce@.defreitas.com> wrote:
>Hello. When you turn Logging on, for a DTS package, it
>appears to save the history forever? If that's true, is
>there an option to clear DTS package logs, after like 30
>days? or would you need to write something to do this
>purge manually? THanks, Bruce
|||THanks Sue. Yes, I was looking for an option hopefully
built-in to SQL Server, to give me a set number of logs,
or a set number of days, like you can adjust the number of
SQL error log files. Yep, I'll check out the homegrown
approach... THanks, Bruce
>--Original Message--
>You can delete all the logs for a package from the
Package Properties,
>Logging tab.
>You can also right click on local packages under the DTS
node in
>Enterprise Manager and select Logging. From here you have
a bit more
>control on selecting packages, selecting logs to delete,
etc.
>If you want to automate this somehow, you could code your
own
>maintenance of these using the DTS Object Model and
schedule this
>through a job. Take a look at the
RemovePackageLogRecords method of
>the PackageSQLServer object.
>-Sue
>On Fri, 29 Oct 2004 10:23:48 -0700, "Bruce de Freitas"
><bruce@.defreitas.com> wrote:
>
>.
>
appears to save the history forever? If that's true, is
there an option to clear DTS package logs, after like 30
days? or would you need to write something to do this
purge manually? THanks, Bruce
You can delete all the logs for a package from the Package Properties,
Logging tab.
You can also right click on local packages under the DTS node in
Enterprise Manager and select Logging. From here you have a bit more
control on selecting packages, selecting logs to delete, etc.
If you want to automate this somehow, you could code your own
maintenance of these using the DTS Object Model and schedule this
through a job. Take a look at the RemovePackageLogRecords method of
the PackageSQLServer object.
-Sue
On Fri, 29 Oct 2004 10:23:48 -0700, "Bruce de Freitas"
<bruce@.defreitas.com> wrote:
>Hello. When you turn Logging on, for a DTS package, it
>appears to save the history forever? If that's true, is
>there an option to clear DTS package logs, after like 30
>days? or would you need to write something to do this
>purge manually? THanks, Bruce
|||THanks Sue. Yes, I was looking for an option hopefully
built-in to SQL Server, to give me a set number of logs,
or a set number of days, like you can adjust the number of
SQL error log files. Yep, I'll check out the homegrown
approach... THanks, Bruce
>--Original Message--
>You can delete all the logs for a package from the
Package Properties,
>Logging tab.
>You can also right click on local packages under the DTS
node in
>Enterprise Manager and select Logging. From here you have
a bit more
>control on selecting packages, selecting logs to delete,
etc.
>If you want to automate this somehow, you could code your
own
>maintenance of these using the DTS Object Model and
schedule this
>through a job. Take a look at the
RemovePackageLogRecords method of
>the PackageSQLServer object.
>-Sue
>On Fri, 29 Oct 2004 10:23:48 -0700, "Bruce de Freitas"
><bruce@.defreitas.com> wrote:
>
>.
>
Tuesday, February 14, 2012
DTS import
ive successfully imported the data i need into a table, but now i need to
append to that data another field from the original source into data thats
already there
eg original source was poor - had a diary function that could be either
ID,Account Manager,Diary or ID,Senior Account Manager,Diary
in sql i have ID,EnteredBy,Diary
i imported the data from Access no problem but i could only import the
account manager field - now i need to append in senior account manager to
the data thats already there
heh not sure if im making myself clear
markCould you supply a DDL of the source table and the SQL Server table and a
sample of the data and what you want it to look like?
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"mark" <mark@.remove.com> wrote in message
news:dwHqc.24$pz2.23@.newsfe3-win.server.ntli.net...
> ive successfully imported the data i need into a table, but now i need to
> append to that data another field from the original source into data thats
> already there
> eg original source was poor - had a diary function that could be either
> ID,Account Manager,Diary or ID,Senior Account Manager,Diary
> in sql i have ID,EnteredBy,Diary
> i imported the data from Access no problem but i could only import the
> account manager field - now i need to append in senior account manager to
> the data thats already there
> heh not sure if im making myself clear
> mark
>|||"mark" <mark@.remove.com> wrote in message news:<dwHqc.24$pz2.23@.newsfe3-win.server.ntli.net>...
> ive successfully imported the data i need into a table, but now i need to
> append to that data another field from the original source into data thats
> already there
> eg original source was poor - had a diary function that could be either
> ID,Account Manager,Diary or ID,Senior Account Manager,Diary
> in sql i have ID,EnteredBy,Diary
> i imported the data from Access no problem but i could only import the
> account manager field - now i need to append in senior account manager to
> the data thats already there
> heh not sure if im making myself clear
> mark
Well, I don't understand the detail of what you're trying to do.
There are three options spring to mind though.
All start with you adding your new field to the table.
1) With DTS you can do all sorts of things in script.
There's probably a way of updating the data directly via a DTS
package, but I reckon if you have to ask then this ain't the route you
want mate.
2) Whack the data into a new table. Put a bit of T-SQL together to
update
with this and then drop the new table.
3) Connect access to the SQL database using a dsn and attached tables
and write the update in access. This may be slow depending on your
connection and amount of data but I suggest it because maybe you're
more used to working in Access.
HTH
append to that data another field from the original source into data thats
already there
eg original source was poor - had a diary function that could be either
ID,Account Manager,Diary or ID,Senior Account Manager,Diary
in sql i have ID,EnteredBy,Diary
i imported the data from Access no problem but i could only import the
account manager field - now i need to append in senior account manager to
the data thats already there
heh not sure if im making myself clear
markCould you supply a DDL of the source table and the SQL Server table and a
sample of the data and what you want it to look like?
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"mark" <mark@.remove.com> wrote in message
news:dwHqc.24$pz2.23@.newsfe3-win.server.ntli.net...
> ive successfully imported the data i need into a table, but now i need to
> append to that data another field from the original source into data thats
> already there
> eg original source was poor - had a diary function that could be either
> ID,Account Manager,Diary or ID,Senior Account Manager,Diary
> in sql i have ID,EnteredBy,Diary
> i imported the data from Access no problem but i could only import the
> account manager field - now i need to append in senior account manager to
> the data thats already there
> heh not sure if im making myself clear
> mark
>|||"mark" <mark@.remove.com> wrote in message news:<dwHqc.24$pz2.23@.newsfe3-win.server.ntli.net>...
> ive successfully imported the data i need into a table, but now i need to
> append to that data another field from the original source into data thats
> already there
> eg original source was poor - had a diary function that could be either
> ID,Account Manager,Diary or ID,Senior Account Manager,Diary
> in sql i have ID,EnteredBy,Diary
> i imported the data from Access no problem but i could only import the
> account manager field - now i need to append in senior account manager to
> the data thats already there
> heh not sure if im making myself clear
> mark
Well, I don't understand the detail of what you're trying to do.
There are three options spring to mind though.
All start with you adding your new field to the table.
1) With DTS you can do all sorts of things in script.
There's probably a way of updating the data directly via a DTS
package, but I reckon if you have to ask then this ain't the route you
want mate.
2) Whack the data into a new table. Put a bit of T-SQL together to
update
with this and then drop the new table.
3) Connect access to the SQL database using a dsn and attached tables
and write the update in access. This may be slow depending on your
connection and amount of data but I suggest it because maybe you're
more used to working in Access.
HTH
Subscribe to:
Posts (Atom)