We are upgrading our current SQL2000 Server to new hardware. Is there a way
to transfer DTS packages from one server to another. Thanks.
Open the package, in the package menu, select save as...you can save it to
file or directly to another server
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:uq4MB3IuFHA.3568@.TK2MSFTNGP10.phx.gbl...
> We are upgrading our current SQL2000 Server to new hardware. Is there a
> way
> to transfer DTS packages from one server to another. Thanks.
>
|||did you hear "Tom Reis" <reistom@.cdnet.cod.edu> say in
news:uq4MB3IuFHA.3568@.TK2MSFTNGP10.phx.gbl:
> We are upgrading our current SQL2000 Server to new hardware. Is there
> a way to transfer DTS packages from one server to another. Thanks.
>
http://www.sqldts.com/default.aspx?204
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs
|||HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default...b;en-us;314546
AMB
"Tom Reis" wrote:
> We are upgrading our current SQL2000 Server to new hardware. Is there a way
> to transfer DTS packages from one server to another. Thanks.
>
>
|||Hi,
Just open the DTS Package and save the package in text file it will be
saved as ii.DTS.
Copy that to another server and open the package and save in local
machine.
hope this help u
from
Doller
sqlsql
Showing posts with label packages. Show all posts
Showing posts with label packages. Show all posts
Thursday, March 29, 2012
DTS Scripts
We are upgrading our current SQL2000 Server to new hardware. Is there a way
to transfer DTS packages from one server to another. Thanks.Open the package, in the package menu, select save as...you can save it to
file or directly to another server
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:uq4MB3IuFHA.3568@.TK2MSFTNGP10.phx.gbl...
> We are upgrading our current SQL2000 Server to new hardware. Is there a
> way
> to transfer DTS packages from one server to another. Thanks.
>|||did you hear "Tom Reis" <reistom@.cdnet.cod.edu> say in
news:uq4MB3IuFHA.3568@.TK2MSFTNGP10.phx.gbl:
> We are upgrading our current SQL2000 Server to new hardware. Is there
> a way to transfer DTS packages from one server to another. Thanks.
>
http://www.sqldts.com/default.aspx?204
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/defaul...kb;en-us;314546
AMB
"Tom Reis" wrote:
> We are upgrading our current SQL2000 Server to new hardware. Is there a wa
y
> to transfer DTS packages from one server to another. Thanks.
>
>|||Hi,
Just open the DTS Package and save the package in text file it will be
saved as ii.DTS.
Copy that to another server and open the package and save in local
machine.
hope this help u
from
Doller
to transfer DTS packages from one server to another. Thanks.Open the package, in the package menu, select save as...you can save it to
file or directly to another server
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:uq4MB3IuFHA.3568@.TK2MSFTNGP10.phx.gbl...
> We are upgrading our current SQL2000 Server to new hardware. Is there a
> way
> to transfer DTS packages from one server to another. Thanks.
>|||did you hear "Tom Reis" <reistom@.cdnet.cod.edu> say in
news:uq4MB3IuFHA.3568@.TK2MSFTNGP10.phx.gbl:
> We are upgrading our current SQL2000 Server to new hardware. Is there
> a way to transfer DTS packages from one server to another. Thanks.
>
http://www.sqldts.com/default.aspx?204
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/defaul...kb;en-us;314546
AMB
"Tom Reis" wrote:
> We are upgrading our current SQL2000 Server to new hardware. Is there a wa
y
> to transfer DTS packages from one server to another. Thanks.
>
>|||Hi,
Just open the DTS Package and save the package in text file it will be
saved as ii.DTS.
Copy that to another server and open the package and save in local
machine.
hope this help u
from
Doller
DTS Scripts
We are upgrading our current SQL2000 Server to new hardware. Is there a way
to transfer DTS packages from one server to another. Thanks.Open the package, in the package menu, select save as...you can save it to
file or directly to another server
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:uq4MB3IuFHA.3568@.TK2MSFTNGP10.phx.gbl...
> We are upgrading our current SQL2000 Server to new hardware. Is there a
> way
> to transfer DTS packages from one server to another. Thanks.
>|||did you hear "Tom Reis" <reistom@.cdnet.cod.edu> say in
news:uq4MB3IuFHA.3568@.TK2MSFTNGP10.phx.gbl:
> We are upgrading our current SQL2000 Server to new hardware. Is there
> a way to transfer DTS packages from one server to another. Thanks.
>
http://www.sqldts.com/default.aspx?204
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
AMB
"Tom Reis" wrote:
> We are upgrading our current SQL2000 Server to new hardware. Is there a way
> to transfer DTS packages from one server to another. Thanks.
>
>|||Hi,
Just open the DTS Package and save the package in text file it will be
saved as ii.DTS.
Copy that to another server and open the package and save in local
machine.
hope this help u
from
Doller
to transfer DTS packages from one server to another. Thanks.Open the package, in the package menu, select save as...you can save it to
file or directly to another server
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:uq4MB3IuFHA.3568@.TK2MSFTNGP10.phx.gbl...
> We are upgrading our current SQL2000 Server to new hardware. Is there a
> way
> to transfer DTS packages from one server to another. Thanks.
>|||did you hear "Tom Reis" <reistom@.cdnet.cod.edu> say in
news:uq4MB3IuFHA.3568@.TK2MSFTNGP10.phx.gbl:
> We are upgrading our current SQL2000 Server to new hardware. Is there
> a way to transfer DTS packages from one server to another. Thanks.
>
http://www.sqldts.com/default.aspx?204
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
AMB
"Tom Reis" wrote:
> We are upgrading our current SQL2000 Server to new hardware. Is there a way
> to transfer DTS packages from one server to another. Thanks.
>
>|||Hi,
Just open the DTS Package and save the package in text file it will be
saved as ii.DTS.
Copy that to another server and open the package and save in local
machine.
hope this help u
from
Doller
DTS Scheduled job always fails
Hi,
I created and saved a DTS package to "SQL Server". When I "Execute Package" under the "Data Transformation -> Local Packages" folder in Enterprise Mgr, it executes perfectly.
However, when I Schedule it, the scheduled job Fails. The View Job History shows "The job failed. The Job was invoked by User sa. The last step to run was step 1 (ImportTest3)."
Could someone please help me with this? I don't understand why the manual execution works, but the scheduled job doesn't. Thanks for the help!Did you ever find a solution to this problem? I am having the same problem.
Originally posted by multiplex77
Hi,
I created and saved a DTS package to "SQL Server". When I "Execute Package" under the "Data Transformation -> Local Packages" folder in Enterprise Mgr, it executes perfectly.
However, when I Schedule it, the scheduled job Fails. The View Job History shows "The job failed. The Job was invoked by User sa. The last step to run was step 1 (ImportTest3)."
Could someone please help me with this? I don't understand why the manual execution works, but the scheduled job doesn't. Thanks for the help!|||You execute under different security contexts when executing a dts package manually versus scheduling it as a job. What is your dts script doing ?|||I set up a number of packages to process selected cubes in Analysis Services. The first job processes all dimensions and even that one fails.|||What message is returned for the failure ?|||ptcooper - In your case, you can execute the package and it succeeds - but when you schedule it as a job, it fails - is this true ?|||True, the problem is exactly as described by multiplex77. the only jobs that fail are the ones that are processing OLAP cubes/dimensions
Originally posted by rnealejr
ptcooper - In your case, you can execute the package and it succeeds - but when you schedule it as a job, it fails - is this true ?|||Who is the owner of the job ?|||The owner is sa and the password was specified when the DTS packages were saved. SLQ Server Agent is logged on as administrator.
Originally posted by rnealejr
Who is the owner of the job ?|||log the package and post the message(s) returned in the log.|||I don't see a way to log the package. I've never had to do that.
Originally posted by rnealejr
log the package and post the message(s) returned in the log.|||If the SQL Agent is running as the local administrator, is the local administrator a member of the local Olap Administrators group? Not sure if it defaults to this.|||The SQlSERVERAGENT does not log on as the local administrator. It logs on as a system wide administrator. We have been looking for the OLAP administrator group in the 'security' tab and the 'member of' tab for the administrator, but don't find it listed in any of the drop down lists. We have all the latest service packs installed.|||The Olap Administrators group is a local group created by the Analysis Services install. Anyone who is not a member of this group can not use the SQL Server tools to manage, modify, or otherwise peek at the cubes on that machine. Remember this only applies to the MMC tool. The OLAP Administrators group will not show up in the domain-wide dropdown lists, since it is local only to one machine. Does that help?|||Right click your "My Computer" -> Manage -> Local Users and Groups -> Groups -> Olap Admin ... Add the account that is used to run sql server agent.|||UPDATE: We discovered that the OLAP Administrator group had somehow been deleted from the Active directory. We tried re-installing service pack 3 for Analysis Services, recreating the OLAP Administrators group and setting the members, properties etc. None of this worked. We just re-installed Analysis Services and service pack 3 on the server and now the jobs will run from the workstation in EM|||The OLAP administrators group is not in AD. Never was. It is local to the Analysis Services machine.|||Our Analysis Services server is a Domain controller, and as such cannot use the Local Users.. snap in. You get directed to the Active Directory Users and Computers when you try opening it. (also ref. MS KB article 829738 'More Information')|||Once you installed it as a domain controller in ad, the local users/groups do not transfer - which is why the olap admin group never appeared. I am a little curious that other problems did not appear before this problem.
Thanks for posting the follow-up with the solution.
I created and saved a DTS package to "SQL Server". When I "Execute Package" under the "Data Transformation -> Local Packages" folder in Enterprise Mgr, it executes perfectly.
However, when I Schedule it, the scheduled job Fails. The View Job History shows "The job failed. The Job was invoked by User sa. The last step to run was step 1 (ImportTest3)."
Could someone please help me with this? I don't understand why the manual execution works, but the scheduled job doesn't. Thanks for the help!Did you ever find a solution to this problem? I am having the same problem.
Originally posted by multiplex77
Hi,
I created and saved a DTS package to "SQL Server". When I "Execute Package" under the "Data Transformation -> Local Packages" folder in Enterprise Mgr, it executes perfectly.
However, when I Schedule it, the scheduled job Fails. The View Job History shows "The job failed. The Job was invoked by User sa. The last step to run was step 1 (ImportTest3)."
Could someone please help me with this? I don't understand why the manual execution works, but the scheduled job doesn't. Thanks for the help!|||You execute under different security contexts when executing a dts package manually versus scheduling it as a job. What is your dts script doing ?|||I set up a number of packages to process selected cubes in Analysis Services. The first job processes all dimensions and even that one fails.|||What message is returned for the failure ?|||ptcooper - In your case, you can execute the package and it succeeds - but when you schedule it as a job, it fails - is this true ?|||True, the problem is exactly as described by multiplex77. the only jobs that fail are the ones that are processing OLAP cubes/dimensions
Originally posted by rnealejr
ptcooper - In your case, you can execute the package and it succeeds - but when you schedule it as a job, it fails - is this true ?|||Who is the owner of the job ?|||The owner is sa and the password was specified when the DTS packages were saved. SLQ Server Agent is logged on as administrator.
Originally posted by rnealejr
Who is the owner of the job ?|||log the package and post the message(s) returned in the log.|||I don't see a way to log the package. I've never had to do that.
Originally posted by rnealejr
log the package and post the message(s) returned in the log.|||If the SQL Agent is running as the local administrator, is the local administrator a member of the local Olap Administrators group? Not sure if it defaults to this.|||The SQlSERVERAGENT does not log on as the local administrator. It logs on as a system wide administrator. We have been looking for the OLAP administrator group in the 'security' tab and the 'member of' tab for the administrator, but don't find it listed in any of the drop down lists. We have all the latest service packs installed.|||The Olap Administrators group is a local group created by the Analysis Services install. Anyone who is not a member of this group can not use the SQL Server tools to manage, modify, or otherwise peek at the cubes on that machine. Remember this only applies to the MMC tool. The OLAP Administrators group will not show up in the domain-wide dropdown lists, since it is local only to one machine. Does that help?|||Right click your "My Computer" -> Manage -> Local Users and Groups -> Groups -> Olap Admin ... Add the account that is used to run sql server agent.|||UPDATE: We discovered that the OLAP Administrator group had somehow been deleted from the Active directory. We tried re-installing service pack 3 for Analysis Services, recreating the OLAP Administrators group and setting the members, properties etc. None of this worked. We just re-installed Analysis Services and service pack 3 on the server and now the jobs will run from the workstation in EM|||The OLAP administrators group is not in AD. Never was. It is local to the Analysis Services machine.|||Our Analysis Services server is a Domain controller, and as such cannot use the Local Users.. snap in. You get directed to the Active Directory Users and Computers when you try opening it. (also ref. MS KB article 829738 'More Information')|||Once you installed it as a domain controller in ad, the local users/groups do not transfer - which is why the olap admin group never appeared. I am a little curious that other problems did not appear before this problem.
Thanks for posting the follow-up with the solution.
Tuesday, March 27, 2012
DTS Remote Scheduling
Hello all,
I am moving data from sql to sql.
I have created my packages (structured storage files) via my computer
these packages are stored on the sql srever and I access them remotely
via my own computer.
My question is
1. How can I also REMOTELY schedule these packages or create jobs s
that these packages run out of office hours.
This is what I tried but it failed to work...
I open enterprise manager and clicked on to the server in question
I then clicked on 'MANAGEMENT' and 'JOBS'
I right clicked 'jobs' and clicked 'NEW JOB'
I then completed the four tabs (general, step, schedules
notifications).
After the package was supposed to have run, I viewed the job histor
and found out that it failed.
is there a way to be able to schedule a package (structured storag
file) from another computer
--
newomog
----
newomoge's Profile: http://www.msusenet.com/member.php?userid=253
View this thread: http://www.msusenet.com/t-187055672Why did it fail? The general reason that a DTS package will work when you
run it but fail when scheduled is due to permissions, specifically because
when run by a job (assuming the job is owned by a sysadmin) it will run as
the SQL Agent Service Account. Have a look at
INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q269074
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"newomoge" <newomoge.1r1e0s@.no-mx.msusenet.com> wrote in message
news:newomoge.1r1e0s@.no-mx.msusenet.com...
> Hello all,
> I am moving data from sql to sql.
> I have created my packages (structured storage files) via my computer.
> these packages are stored on the sql srever and I access them remotely
> via my own computer.
> My question is
> 1. How can I also REMOTELY schedule these packages or create jobs so
> that these packages run out of office hours.
> This is what I tried but it failed to work...
> I open enterprise manager and clicked on to the server in question
> I then clicked on 'MANAGEMENT' and 'JOBS'
> I right clicked 'jobs' and clicked 'NEW JOB'
> I then completed the four tabs (general, step, schedules,
> notifications).
> After the package was supposed to have run, I viewed the job history
> and found out that it failed.
> is there a way to be able to schedule a package (structured storage
> file) from another computer?
>
> --
> newomoge
> ---
> newomoge's Profile: http://www.msusenet.com/member.php?userid=2530
> View this thread: http://www.msusenet.com/t-1870556724
>sqlsql
I am moving data from sql to sql.
I have created my packages (structured storage files) via my computer
these packages are stored on the sql srever and I access them remotely
via my own computer.
My question is
1. How can I also REMOTELY schedule these packages or create jobs s
that these packages run out of office hours.
This is what I tried but it failed to work...
I open enterprise manager and clicked on to the server in question
I then clicked on 'MANAGEMENT' and 'JOBS'
I right clicked 'jobs' and clicked 'NEW JOB'
I then completed the four tabs (general, step, schedules
notifications).
After the package was supposed to have run, I viewed the job histor
and found out that it failed.
is there a way to be able to schedule a package (structured storag
file) from another computer
--
newomog
----
newomoge's Profile: http://www.msusenet.com/member.php?userid=253
View this thread: http://www.msusenet.com/t-187055672Why did it fail? The general reason that a DTS package will work when you
run it but fail when scheduled is due to permissions, specifically because
when run by a job (assuming the job is owned by a sysadmin) it will run as
the SQL Agent Service Account. Have a look at
INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q269074
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"newomoge" <newomoge.1r1e0s@.no-mx.msusenet.com> wrote in message
news:newomoge.1r1e0s@.no-mx.msusenet.com...
> Hello all,
> I am moving data from sql to sql.
> I have created my packages (structured storage files) via my computer.
> these packages are stored on the sql srever and I access them remotely
> via my own computer.
> My question is
> 1. How can I also REMOTELY schedule these packages or create jobs so
> that these packages run out of office hours.
> This is what I tried but it failed to work...
> I open enterprise manager and clicked on to the server in question
> I then clicked on 'MANAGEMENT' and 'JOBS'
> I right clicked 'jobs' and clicked 'NEW JOB'
> I then completed the four tabs (general, step, schedules,
> notifications).
> After the package was supposed to have run, I viewed the job history
> and found out that it failed.
> is there a way to be able to schedule a package (structured storage
> file) from another computer?
>
> --
> newomoge
> ---
> newomoge's Profile: http://www.msusenet.com/member.php?userid=2530
> View this thread: http://www.msusenet.com/t-1870556724
>sqlsql
DTS Remote Scheduling
Hello all,
I am moving data from sql to sql.
I have created my packages (structured storage files) via my computer.
these packages are stored on the sql srever and I access them remotely
via my own computer.
My question is
1. How can I also REMOTELY schedule these packages or create jobs so
that these packages run out of office hours.
This is what I tried but it failed to work...
I open enterprise manager and clicked on to the server in question
I then clicked on 'MANAGEMENT' and 'JOBS'
I right clicked 'jobs' and clicked 'NEW JOB'
I then completed the four tabs (general, step, schedules,
notifications).
After the package was supposed to have run, I viewed the job history
and found out that it failed.
is there a way to be able to schedule a package (structured storage
file) from another computer?
newomoge
newomoge's Profile: http://www.mswebservertalk.com/member.php?userid=2530
View this thread: http://www.mswebservertalk.com/t-1870556724
Why did it fail? The general reason that a DTS package will work when you
run it but fail when scheduled is due to permissions, specifically because
when run by a job (assuming the job is owned by a sysadmin) it will run as
the SQL Agent Service Account. Have a look at
INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/default...;en-us;Q269074
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"newomoge" <newomoge.1r1e0s@.no-mx.mswebservertalk.com> wrote in message
news:newomoge.1r1e0s@.no-mx.mswebservertalk.com...
> Hello all,
> I am moving data from sql to sql.
> I have created my packages (structured storage files) via my computer.
> these packages are stored on the sql srever and I access them remotely
> via my own computer.
> My question is
> 1. How can I also REMOTELY schedule these packages or create jobs so
> that these packages run out of office hours.
> This is what I tried but it failed to work...
> I open enterprise manager and clicked on to the server in question
> I then clicked on 'MANAGEMENT' and 'JOBS'
> I right clicked 'jobs' and clicked 'NEW JOB'
> I then completed the four tabs (general, step, schedules,
> notifications).
> After the package was supposed to have run, I viewed the job history
> and found out that it failed.
> is there a way to be able to schedule a package (structured storage
> file) from another computer?
>
> --
> newomoge
> newomoge's Profile: http://www.mswebservertalk.com/member.php?userid=2530
> View this thread: http://www.mswebservertalk.com/t-1870556724
>
I am moving data from sql to sql.
I have created my packages (structured storage files) via my computer.
these packages are stored on the sql srever and I access them remotely
via my own computer.
My question is
1. How can I also REMOTELY schedule these packages or create jobs so
that these packages run out of office hours.
This is what I tried but it failed to work...
I open enterprise manager and clicked on to the server in question
I then clicked on 'MANAGEMENT' and 'JOBS'
I right clicked 'jobs' and clicked 'NEW JOB'
I then completed the four tabs (general, step, schedules,
notifications).
After the package was supposed to have run, I viewed the job history
and found out that it failed.
is there a way to be able to schedule a package (structured storage
file) from another computer?
newomoge
newomoge's Profile: http://www.mswebservertalk.com/member.php?userid=2530
View this thread: http://www.mswebservertalk.com/t-1870556724
Why did it fail? The general reason that a DTS package will work when you
run it but fail when scheduled is due to permissions, specifically because
when run by a job (assuming the job is owned by a sysadmin) it will run as
the SQL Agent Service Account. Have a look at
INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/default...;en-us;Q269074
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"newomoge" <newomoge.1r1e0s@.no-mx.mswebservertalk.com> wrote in message
news:newomoge.1r1e0s@.no-mx.mswebservertalk.com...
> Hello all,
> I am moving data from sql to sql.
> I have created my packages (structured storage files) via my computer.
> these packages are stored on the sql srever and I access them remotely
> via my own computer.
> My question is
> 1. How can I also REMOTELY schedule these packages or create jobs so
> that these packages run out of office hours.
> This is what I tried but it failed to work...
> I open enterprise manager and clicked on to the server in question
> I then clicked on 'MANAGEMENT' and 'JOBS'
> I right clicked 'jobs' and clicked 'NEW JOB'
> I then completed the four tabs (general, step, schedules,
> notifications).
> After the package was supposed to have run, I viewed the job history
> and found out that it failed.
> is there a way to be able to schedule a package (structured storage
> file) from another computer?
>
> --
> newomoge
> newomoge's Profile: http://www.mswebservertalk.com/member.php?userid=2530
> View this thread: http://www.mswebservertalk.com/t-1870556724
>
DTS Remote Scheduling
Hello all,
I am moving data from sql to sql.
I have created my packages (structured storage files) via my computer.
these packages are stored on the sql srever and I access them remotely
via my own computer.
My question is
1. How can I also REMOTELY schedule these packages or create jobs so
that these packages run out of office hours.
This is what I tried but it failed to work...
I open enterprise manager and clicked on to the server in question
I then clicked on 'MANAGEMENT' and 'JOBS'
I right clicked 'jobs' and clicked 'NEW JOB'
I then completed the four tabs (general, step, schedules,
notifications).
After the package was supposed to have run, I viewed the job history
and found out that it failed.
is there a way to be able to schedule a package (structured storage
file) from another computer?
newomoge
---
newomoge's Profile: http://www.msmcse.ms/member.php?userid=2530
View this thread: http://www.msmcse.ms/t-1870556724Why did it fail? The general reason that a DTS package will work when you
run it but fail when scheduled is due to permissions, specifically because
when run by a job (assuming the job is owned by a sysadmin) it will run as
the SQL Agent Service Account. Have a look at
INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/defaul...b;en-us;Q269074
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"newomoge" <newomoge.1r1e0s@.no-mx.msmcse.ms> wrote in message
news:newomoge.1r1e0s@.no-mx.msmcse.ms...
> Hello all,
> I am moving data from sql to sql.
> I have created my packages (structured storage files) via my computer.
> these packages are stored on the sql srever and I access them remotely
> via my own computer.
> My question is
> 1. How can I also REMOTELY schedule these packages or create jobs so
> that these packages run out of office hours.
> This is what I tried but it failed to work...
> I open enterprise manager and clicked on to the server in question
> I then clicked on 'MANAGEMENT' and 'JOBS'
> I right clicked 'jobs' and clicked 'NEW JOB'
> I then completed the four tabs (general, step, schedules,
> notifications).
> After the package was supposed to have run, I viewed the job history
> and found out that it failed.
> is there a way to be able to schedule a package (structured storage
> file) from another computer?
>
> --
> newomoge
> ---
> newomoge's Profile: http://www.msmcse.ms/member.php?userid=2530
> View this thread: http://www.msmcse.ms/t-1870556724
>
I am moving data from sql to sql.
I have created my packages (structured storage files) via my computer.
these packages are stored on the sql srever and I access them remotely
via my own computer.
My question is
1. How can I also REMOTELY schedule these packages or create jobs so
that these packages run out of office hours.
This is what I tried but it failed to work...
I open enterprise manager and clicked on to the server in question
I then clicked on 'MANAGEMENT' and 'JOBS'
I right clicked 'jobs' and clicked 'NEW JOB'
I then completed the four tabs (general, step, schedules,
notifications).
After the package was supposed to have run, I viewed the job history
and found out that it failed.
is there a way to be able to schedule a package (structured storage
file) from another computer?
newomoge
---
newomoge's Profile: http://www.msmcse.ms/member.php?userid=2530
View this thread: http://www.msmcse.ms/t-1870556724Why did it fail? The general reason that a DTS package will work when you
run it but fail when scheduled is due to permissions, specifically because
when run by a job (assuming the job is owned by a sysadmin) it will run as
the SQL Agent Service Account. Have a look at
INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/defaul...b;en-us;Q269074
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"newomoge" <newomoge.1r1e0s@.no-mx.msmcse.ms> wrote in message
news:newomoge.1r1e0s@.no-mx.msmcse.ms...
> Hello all,
> I am moving data from sql to sql.
> I have created my packages (structured storage files) via my computer.
> these packages are stored on the sql srever and I access them remotely
> via my own computer.
> My question is
> 1. How can I also REMOTELY schedule these packages or create jobs so
> that these packages run out of office hours.
> This is what I tried but it failed to work...
> I open enterprise manager and clicked on to the server in question
> I then clicked on 'MANAGEMENT' and 'JOBS'
> I right clicked 'jobs' and clicked 'NEW JOB'
> I then completed the four tabs (general, step, schedules,
> notifications).
> After the package was supposed to have run, I viewed the job history
> and found out that it failed.
> is there a way to be able to schedule a package (structured storage
> file) from another computer?
>
> --
> newomoge
> ---
> newomoge's Profile: http://www.msmcse.ms/member.php?userid=2530
> View this thread: http://www.msmcse.ms/t-1870556724
>
DTS QUERY AND HELP
I’ve not had much experience with DTS packages before.I am using SQL Server 2000 and I have a table I wish to update.I have a spreadsheet with the updated records – this does not include all records and does not include all columns within the table,
I just wanted to know if it is still possible to update certain fields without having to include all the records and all the columns e.g.
Table 1
IDNAMESURNAME
1TEDSMITH
2FREDPERRY
3JOEYOUNG
Spreadsheet
IDSURNAME
2CASH
3WILLIS
Result
IDNAMESURNAME
1TEDSMITH
2FREDCASH
3JOEYOUNG
And the million-dollar question – please could you provide the steps to do this?
Take a look at the Data Driven Query Component|||Can anyone provide any more information on this for me?
Thanks
|||If you need th enext level of help thn its probably best to move this thread to the SSIS forum|||Look for "Building a Data Driven Query" in SQL Server 2000 Book Online, also found here: http://msdn2.microsoft.com/en-us/library/aa933507(sql.80).aspx
You'll want to follow the first example: "In the first example, every source row leads to the update of a destination row. Only one query type, an Update query, is used."
sqlsqlSunday, March 25, 2012
DTS Pkg Job error
I recently installed a new SQL Server and am having problems scheduling my DTS packages. They will work fine if I manually execute them, but none of them will fire from a job.
Regardless of the DTS package scheduled it fails with the following error:
Executed as user: STANDREWS\SYSTEM. The process could not be created for step 1
of job 0x8D49E4684F14CB49BFC9223F4E47FC16 (reason: The system cannot find the file
specified). The step failed.
I have tried all kinds of stuff to no avail.What does step 1 look like? Did you try specifying the entire path for dtsrun.exe?
Terri|||when they fire from a job, they are firing under the security context of the account that the SQL Agent uses.
Therefore i would conclude that your account has access to something that the SQL Agent account does not.
you could set the SQL Agent to use a specific machine account...|||Yeah, created the job in various fashions, by name, by GUID, etc, etc. I am not certain of the absolute file path though, where is that found?
I will also check the SQL Agent security.|||The absolute file path is the physical path to dtsrun.exe on the SQL Server. On my local SQL Server, e.g., it can be found here: C:\Program Files\Microsoft SQL Server\80\Tools\Binn\dtsrun.exe
I am bringing this up because I have come across this problem in the past.KB 322746 - PRB: Data Transformation Services Jobs May Fail Because the Path for Dtsrun.exe Cannot Be Found addresses this.
DTS permissions to create
How to I revoke the ability of non-sa non-dbo users from
creating DTS packages on a SQL 2000 server?
Thx!
You could try revoking or denying write permissions to msdb. this is where dts package information is stored I believe.
In theory the creation of a package should fail if it cannot be written to msdb.
"Angelina" wrote:
> How to I revoke the ability of non-sa non-dbo users from
> creating DTS packages on a SQL 2000 server?
> Thx!
>
|||There's no specific stored procedure in msdb that I can
explicity deny that creates the DTS packages?
>--Original Message--
>You could try revoking or denying write permissions to
msdb. this is where dts package information is stored I
believe.
>In theory the creation of a package should fail if it
cannot be written to msdb.[vbcol=seagreen]
>"Angelina" wrote:
from
>.
>
|||Angelina
To prevent users from adding DTS packages, you can deny EXEC permissions on
sp_add_dtspackage. Deny exec on sp_enum_dtspackages should stop users
browsing packages.
HTH,
Paul Ibison
|||Thanks Paul,
Do I Deny the permissions to the guest account.
There are no actual users in the msdb database besides
guest and dbo. Or can I remove the guest account
completely. What's recommended?
>--Original Message--
>Angelina
>To prevent users from adding DTS packages, you can deny
EXEC permissions on
>sp_add_dtspackage. Deny exec on sp_enum_dtspackages
should stop users
>browsing packages.
>HTH,
>Paul Ibison
>
>.
>
|||The rights are inherited by virtue of the guest user and the permissions of
the public role. The way I'd do it is remove (revoke and not deny) the exec
rights from the public role and then create your own role with exec rights
to the 2 procedures I mentioned. If a user needs to be able to use them,
then add him explicitly as a user in MSDB and into your role.
HTH,
Paul Ibison
creating DTS packages on a SQL 2000 server?
Thx!
You could try revoking or denying write permissions to msdb. this is where dts package information is stored I believe.
In theory the creation of a package should fail if it cannot be written to msdb.
"Angelina" wrote:
> How to I revoke the ability of non-sa non-dbo users from
> creating DTS packages on a SQL 2000 server?
> Thx!
>
|||There's no specific stored procedure in msdb that I can
explicity deny that creates the DTS packages?
>--Original Message--
>You could try revoking or denying write permissions to
msdb. this is where dts package information is stored I
believe.
>In theory the creation of a package should fail if it
cannot be written to msdb.[vbcol=seagreen]
>"Angelina" wrote:
from
>.
>
|||Angelina
To prevent users from adding DTS packages, you can deny EXEC permissions on
sp_add_dtspackage. Deny exec on sp_enum_dtspackages should stop users
browsing packages.
HTH,
Paul Ibison
|||Thanks Paul,
Do I Deny the permissions to the guest account.
There are no actual users in the msdb database besides
guest and dbo. Or can I remove the guest account
completely. What's recommended?
>--Original Message--
>Angelina
>To prevent users from adding DTS packages, you can deny
EXEC permissions on
>sp_add_dtspackage. Deny exec on sp_enum_dtspackages
should stop users
>browsing packages.
>HTH,
>Paul Ibison
>
>.
>
|||The rights are inherited by virtue of the guest user and the permissions of
the public role. The way I'd do it is remove (revoke and not deny) the exec
rights from the public role and then create your own role with exec rights
to the 2 procedures I mentioned. If a user needs to be able to use them,
then add him explicitly as a user in MSDB and into your role.
HTH,
Paul Ibison
DTS permissions to create
How to I revoke the ability of non-sa non-dbo users from
creating DTS packages on a SQL 2000 server?
Thx!You could try revoking or denying write permissions to msdb. this is where d
ts package information is stored I believe.
In theory the creation of a package should fail if it cannot be written to m
sdb.
"Angelina" wrote:
> How to I revoke the ability of non-sa non-dbo users from
> creating DTS packages on a SQL 2000 server?
> Thx!
>|||There's no specific stored procedure in msdb that I can
explicity deny that creates the DTS packages?
>--Original Message--
>You could try revoking or denying write permissions to
msdb. this is where dts package information is stored I
believe.
>In theory the creation of a package should fail if it
cannot be written to msdb.
>"Angelina" wrote:
>
from[vbcol=seagreen]
>.
>|||Angelina
To prevent users from adding DTS packages, you can deny EXEC permissions on
sp_add_dtspackage. Deny exec on sp_enum_dtspackages should stop users
browsing packages.
HTH,
Paul Ibison|||Thanks Paul,
Do I Deny the permissions to the guest account.
There are no actual users in the msdb database besides
guest and dbo. Or can I remove the guest account
completely. What's recommended?
>--Original Message--
>Angelina
>To prevent users from adding DTS packages, you can deny
EXEC permissions on
>sp_add_dtspackage. Deny exec on sp_enum_dtspackages
should stop users
>browsing packages.
>HTH,
>Paul Ibison
>
>.
>|||The rights are inherited by virtue of the guest user and the permissions of
the public role. The way I'd do it is remove (revoke and not deny) the exec
rights from the public role and then create your own role with exec rights
to the 2 procedures I mentioned. If a user needs to be able to use them,
then add him explicitly as a user in MSDB and into your role.
HTH,
Paul Ibison
creating DTS packages on a SQL 2000 server?
Thx!You could try revoking or denying write permissions to msdb. this is where d
ts package information is stored I believe.
In theory the creation of a package should fail if it cannot be written to m
sdb.
"Angelina" wrote:
> How to I revoke the ability of non-sa non-dbo users from
> creating DTS packages on a SQL 2000 server?
> Thx!
>|||There's no specific stored procedure in msdb that I can
explicity deny that creates the DTS packages?
>--Original Message--
>You could try revoking or denying write permissions to
msdb. this is where dts package information is stored I
believe.
>In theory the creation of a package should fail if it
cannot be written to msdb.
>"Angelina" wrote:
>
from[vbcol=seagreen]
>.
>|||Angelina
To prevent users from adding DTS packages, you can deny EXEC permissions on
sp_add_dtspackage. Deny exec on sp_enum_dtspackages should stop users
browsing packages.
HTH,
Paul Ibison|||Thanks Paul,
Do I Deny the permissions to the guest account.
There are no actual users in the msdb database besides
guest and dbo. Or can I remove the guest account
completely. What's recommended?
>--Original Message--
>Angelina
>To prevent users from adding DTS packages, you can deny
EXEC permissions on
>sp_add_dtspackage. Deny exec on sp_enum_dtspackages
should stop users
>browsing packages.
>HTH,
>Paul Ibison
>
>.
>|||The rights are inherited by virtue of the guest user and the permissions of
the public role. The way I'd do it is remove (revoke and not deny) the exec
rights from the public role and then create your own role with exec rights
to the 2 procedures I mentioned. If a user needs to be able to use them,
then add him explicitly as a user in MSDB and into your role.
HTH,
Paul Ibison
DTS permissions to create
How to I revoke the ability of non-sa non-dbo users from
creating DTS packages on a SQL 2000 server?
Thx!There's no specific stored procedure in msdb that I can
explicity deny that creates the DTS packages?
>--Original Message--
>You could try revoking or denying write permissions to
msdb. this is where dts package information is stored I
believe.
>In theory the creation of a package should fail if it
cannot be written to msdb.
>"Angelina" wrote:
>> How to I revoke the ability of non-sa non-dbo users
from
>> creating DTS packages on a SQL 2000 server?
>> Thx!
>.
>|||Angelina
To prevent users from adding DTS packages, you can deny EXEC permissions on
sp_add_dtspackage. Deny exec on sp_enum_dtspackages should stop users
browsing packages.
HTH,
Paul Ibison|||Thanks Paul,
Do I Deny the permissions to the guest account.
There are no actual users in the msdb database besides
guest and dbo. Or can I remove the guest account
completely. What's recommended?
>--Original Message--
>Angelina
>To prevent users from adding DTS packages, you can deny
EXEC permissions on
>sp_add_dtspackage. Deny exec on sp_enum_dtspackages
should stop users
>browsing packages.
>HTH,
>Paul Ibison
>
>.
>|||The rights are inherited by virtue of the guest user and the permissions of
the public role. The way I'd do it is remove (revoke and not deny) the exec
rights from the public role and then create your own role with exec rights
to the 2 procedures I mentioned. If a user needs to be able to use them,
then add him explicitly as a user in MSDB and into your role.
HTH,
Paul Ibison
creating DTS packages on a SQL 2000 server?
Thx!There's no specific stored procedure in msdb that I can
explicity deny that creates the DTS packages?
>--Original Message--
>You could try revoking or denying write permissions to
msdb. this is where dts package information is stored I
believe.
>In theory the creation of a package should fail if it
cannot be written to msdb.
>"Angelina" wrote:
>> How to I revoke the ability of non-sa non-dbo users
from
>> creating DTS packages on a SQL 2000 server?
>> Thx!
>.
>|||Angelina
To prevent users from adding DTS packages, you can deny EXEC permissions on
sp_add_dtspackage. Deny exec on sp_enum_dtspackages should stop users
browsing packages.
HTH,
Paul Ibison|||Thanks Paul,
Do I Deny the permissions to the guest account.
There are no actual users in the msdb database besides
guest and dbo. Or can I remove the guest account
completely. What's recommended?
>--Original Message--
>Angelina
>To prevent users from adding DTS packages, you can deny
EXEC permissions on
>sp_add_dtspackage. Deny exec on sp_enum_dtspackages
should stop users
>browsing packages.
>HTH,
>Paul Ibison
>
>.
>|||The rights are inherited by virtue of the guest user and the permissions of
the public role. The way I'd do it is remove (revoke and not deny) the exec
rights from the public role and then create your own role with exec rights
to the 2 procedures I mentioned. If a user needs to be able to use them,
then add him explicitly as a user in MSDB and into your role.
HTH,
Paul Ibison
DTS Permissions
Hello all
Hope someone can help, I have set up a new user and restricted his acces to the db,
how can I allow him to read DTS packages but not change or execute them?
Thanks
Richard
Hey Richie,Which version of SQL Server are you using?|||IF those DTS package is owned by SA and scheduled as a job then you need to give this user permission in TARGETSERVERROLE role.
DTS Permissions
Hello all
Hope someone can help, I have set up a new user and restricted his acces to the db,
how can I allow him to read DTS packages but not change or execute them?
Thanks
Richard
Hey Richie,Which version of SQL Server are you using?|||IF those DTS package is owned by SA and scheduled as a job then you need to give this user permission in TARGETSERVERROLE role.
Thursday, March 22, 2012
DTS Packages Unspecified Error
WE have dts packages created with ftp component in Sql 2000 dts designer. At the same time client where we are developing the package has sql 2005 components. When we move this package to sql 2000 production server - we can execute nor we can open the package it will give unspecified error.
if we recreate the package on client which does not have sql 2005 component than only it works. So for any mistake the packge once opened in sql 2000 designer also with client having sql 2005 components it does not work. Only criteria this error happens when you have ftp component used in dts package.
Did anyone has seen this problem ? Any fixes or patches available to be applied ?
Thanks for the help.
Manoj
Hi
Microsoft already know this problem but dont release the hotfix, you need to contact them.
http://support.microsoft.com/kb/890841
I have the same problem and to fixe it, I recovered an old version of DTS through the server and made the modifications.
If you found a better way please contact me.
Thanks
|||Thanks
Looks like this hotfix should resolve the issue, I will touchbase with microsoft and will try to install the hotfix.
Let you know.
Manoj
|||I tried after applying the hotfix - it did not work.
Manoj
|||I'm having the same problem. Did you every get this fixed?|||DTS is provided for the backward compatibility in SQL 2005, you might need to rewrite the same in SSIS to provide the functionality.sqlsqlDTS Packages Unspecified Error
WE have dts packages created with ftp component in Sql 2000 dts designer. At the same time client where we are developing the package has sql 2005 components. When we move this package to sql 2000 production server - we can execute nor we can open the package it will give unspecified error.
if we recreate the package on client which does not have sql 2005 component than only it works. So for any mistake the packge once opened in sql 2000 designer also with client having sql 2005 components it does not work. Only criteria this error happens when you have ftp component used in dts package.
Did anyone has seen this problem ? Any fixes or patches available to be applied ?
Thanks for the help.
Manoj
Hi
Microsoft already know this problem but dont release the hotfix, you need to contact them.
http://support.microsoft.com/kb/890841
I have the same problem and to fixe it, I recovered an old version of DTS through the server and made the modifications.
If you found a better way please contact me.
Thanks
|||Thanks
Looks like this hotfix should resolve the issue, I will touchbase with microsoft and will try to install the hotfix.
Let you know.
Manoj
|||I tried after applying the hotfix - it did not work.
Manoj
|||I'm having the same problem. Did you every get this fixed?|||DTS is provided for the backward compatibility in SQL 2005, you might need to rewrite the same in SSIS to provide the functionality.DTS Packages Unspecified Error
WE have dts packages created with ftp component in Sql 2000 dts designer. At the same time client where we are developing the package has sql 2005 components. When we move this package to sql 2000 production server - we can execute nor we can open the package it will give unspecified error.
if we recreate the package on client which does not have sql 2005 component than only it works. So for any mistake the packge once opened in sql 2000 designer also with client having sql 2005 components it does not work. Only criteria this error happens when you have ftp component used in dts package.
Did anyone has seen this problem ? Any fixes or patches available to be applied ?
Thanks for the help.
Manoj
Hi
Microsoft already know this problem but dont release the hotfix, you need to contact them.
http://support.microsoft.com/kb/890841
I have the same problem and to fixe it, I recovered an old version of DTS through the server and made the modifications.
If you found a better way please contact me.
Thanks
|||Thanks
Looks like this hotfix should resolve the issue, I will touchbase with microsoft and will try to install the hotfix.
Let you know.
Manoj
|||I tried after applying the hotfix - it did not work.
Manoj
|||I'm having the same problem. Did you every get this fixed?|||DTS is provided for the backward compatibility in SQL 2005, you might need to rewrite the same in SSIS to provide the functionality.dts packages take a long time to run
Hi there
I'm running a DTS package that imports data from various Excel spreadsheets
into a SQL Server database. Running the package from the command prompt, I
get information on each data pump task including each 1,000 rows imported
successfully. When all rows have been imported from a particular worksheet
(65536), DTS takes quite a bit of time before it initiates the data pump
task for the next worksheet. I notice that the CPU falls idle during this
time, but DTSrun.exe's memory usage remains high for some time afterward.
This apparent idle time more than doubles the total amount taken to complete
this DTS package. Do you know why this is so? I thought it might be time
taken to build the indexes on each imported worksheet (I need to create each
table from scratch), or alternatively the time taken to establish
communication with Excel. Any other ideas, and a suggestion on how to pick
up the pace?!
Best regards
Loane
You can use profiler to monitor what is going on - looking
at what the process is doing especially at the end of the
import and after the import, what the durations are for
different steps and processes in the package.
You could also monitor it more "manually" by querying
sysprocesses and watching the wait types, activities, etc.
If you are running SQL 2000, sp3 you can also use fn_get_sql
to see what exactly it's executing at different points.
-Sue
On Mon, 2 May 2005 22:26:21 +0200, "Loane Sharp"
<look_sharp_not@.hotmail.com> wrote:
>Hi there
>I'm running a DTS package that imports data from various Excel spreadsheets
>into a SQL Server database. Running the package from the command prompt, I
>get information on each data pump task including each 1,000 rows imported
>successfully. When all rows have been imported from a particular worksheet
>(65536), DTS takes quite a bit of time before it initiates the data pump
>task for the next worksheet. I notice that the CPU falls idle during this
>time, but DTSrun.exe's memory usage remains high for some time afterward.
>This apparent idle time more than doubles the total amount taken to complete
>this DTS package. Do you know why this is so? I thought it might be time
>taken to build the indexes on each imported worksheet (I need to create each
>table from scratch), or alternatively the time taken to establish
>communication with Excel. Any other ideas, and a suggestion on how to pick
>up the pace?!
>Best regards
>Loane
>
|||thanks, will try this one, it's been really niggling me
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:3end71hhr8drpdqhs7kg8d4k6d99415lo3@.4ax.com...
> You can use profiler to monitor what is going on - looking
> at what the process is doing especially at the end of the
> import and after the import, what the durations are for
> different steps and processes in the package.
> You could also monitor it more "manually" by querying
> sysprocesses and watching the wait types, activities, etc.
> If you are running SQL 2000, sp3 you can also use fn_get_sql
> to see what exactly it's executing at different points.
> -Sue
> On Mon, 2 May 2005 22:26:21 +0200, "Loane Sharp"
> <look_sharp_not@.hotmail.com> wrote:
>
I'm running a DTS package that imports data from various Excel spreadsheets
into a SQL Server database. Running the package from the command prompt, I
get information on each data pump task including each 1,000 rows imported
successfully. When all rows have been imported from a particular worksheet
(65536), DTS takes quite a bit of time before it initiates the data pump
task for the next worksheet. I notice that the CPU falls idle during this
time, but DTSrun.exe's memory usage remains high for some time afterward.
This apparent idle time more than doubles the total amount taken to complete
this DTS package. Do you know why this is so? I thought it might be time
taken to build the indexes on each imported worksheet (I need to create each
table from scratch), or alternatively the time taken to establish
communication with Excel. Any other ideas, and a suggestion on how to pick
up the pace?!
Best regards
Loane
You can use profiler to monitor what is going on - looking
at what the process is doing especially at the end of the
import and after the import, what the durations are for
different steps and processes in the package.
You could also monitor it more "manually" by querying
sysprocesses and watching the wait types, activities, etc.
If you are running SQL 2000, sp3 you can also use fn_get_sql
to see what exactly it's executing at different points.
-Sue
On Mon, 2 May 2005 22:26:21 +0200, "Loane Sharp"
<look_sharp_not@.hotmail.com> wrote:
>Hi there
>I'm running a DTS package that imports data from various Excel spreadsheets
>into a SQL Server database. Running the package from the command prompt, I
>get information on each data pump task including each 1,000 rows imported
>successfully. When all rows have been imported from a particular worksheet
>(65536), DTS takes quite a bit of time before it initiates the data pump
>task for the next worksheet. I notice that the CPU falls idle during this
>time, but DTSrun.exe's memory usage remains high for some time afterward.
>This apparent idle time more than doubles the total amount taken to complete
>this DTS package. Do you know why this is so? I thought it might be time
>taken to build the indexes on each imported worksheet (I need to create each
>table from scratch), or alternatively the time taken to establish
>communication with Excel. Any other ideas, and a suggestion on how to pick
>up the pace?!
>Best regards
>Loane
>
|||thanks, will try this one, it's been really niggling me
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:3end71hhr8drpdqhs7kg8d4k6d99415lo3@.4ax.com...
> You can use profiler to monitor what is going on - looking
> at what the process is doing especially at the end of the
> import and after the import, what the durations are for
> different steps and processes in the package.
> You could also monitor it more "manually" by querying
> sysprocesses and watching the wait types, activities, etc.
> If you are running SQL 2000, sp3 you can also use fn_get_sql
> to see what exactly it's executing at different points.
> -Sue
> On Mon, 2 May 2005 22:26:21 +0200, "Loane Sharp"
> <look_sharp_not@.hotmail.com> wrote:
>
dts packages take a long time to run
Hi there
I'm running a DTS package that imports data from various Excel spreadsheets
into a SQL Server database. Running the package from the command prompt, I
get information on each data pump task including each 1,000 rows imported
successfully. When all rows have been imported from a particular worksheet
(65536), DTS takes quite a bit of time before it initiates the data pump
task for the next worksheet. I notice that the CPU falls idle during this
time, but DTSrun.exe's memory usage remains high for some time afterward.
This apparent idle time more than doubles the total amount taken to complete
this DTS package. Do you know why this is so? I thought it might be time
taken to build the indexes on each imported worksheet (I need to create each
table from scratch), or alternatively the time taken to establish
communication with Excel. Any other ideas, and a suggestion on how to pick
up the pace?!
Best regards
LoaneYou can use profiler to monitor what is going on - looking
at what the process is doing especially at the end of the
import and after the import, what the durations are for
different steps and processes in the package.
You could also monitor it more "manually" by querying
sysprocesses and watching the wait types, activities, etc.
If you are running SQL 2000, sp3 you can also use fn_get_sql
to see what exactly it's executing at different points.
-Sue
On Mon, 2 May 2005 22:26:21 +0200, "Loane Sharp"
<look_sharp_not@.hotmail.com> wrote:
>Hi there
>I'm running a DTS package that imports data from various Excel spreadsheets
>into a SQL Server database. Running the package from the command prompt, I
>get information on each data pump task including each 1,000 rows imported
>successfully. When all rows have been imported from a particular worksheet
>(65536), DTS takes quite a bit of time before it initiates the data pump
>task for the next worksheet. I notice that the CPU falls idle during this
>time, but DTSrun.exe's memory usage remains high for some time afterward.
>This apparent idle time more than doubles the total amount taken to complet
e
>this DTS package. Do you know why this is so? I thought it might be time
>taken to build the indexes on each imported worksheet (I need to create eac
h
>table from scratch), or alternatively the time taken to establish
>communication with Excel. Any other ideas, and a suggestion on how to pick
>up the pace?!
>Best regards
>Loane
>|||thanks, will try this one, it's been really niggling me
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:3end71hhr8drpdqhs7kg8d4k6d99415lo3@.
4ax.com...
> You can use profiler to monitor what is going on - looking
> at what the process is doing especially at the end of the
> import and after the import, what the durations are for
> different steps and processes in the package.
> You could also monitor it more "manually" by querying
> sysprocesses and watching the wait types, activities, etc.
> If you are running SQL 2000, sp3 you can also use fn_get_sql
> to see what exactly it's executing at different points.
> -Sue
> On Mon, 2 May 2005 22:26:21 +0200, "Loane Sharp"
> <look_sharp_not@.hotmail.com> wrote:
>
>
I'm running a DTS package that imports data from various Excel spreadsheets
into a SQL Server database. Running the package from the command prompt, I
get information on each data pump task including each 1,000 rows imported
successfully. When all rows have been imported from a particular worksheet
(65536), DTS takes quite a bit of time before it initiates the data pump
task for the next worksheet. I notice that the CPU falls idle during this
time, but DTSrun.exe's memory usage remains high for some time afterward.
This apparent idle time more than doubles the total amount taken to complete
this DTS package. Do you know why this is so? I thought it might be time
taken to build the indexes on each imported worksheet (I need to create each
table from scratch), or alternatively the time taken to establish
communication with Excel. Any other ideas, and a suggestion on how to pick
up the pace?!
Best regards
LoaneYou can use profiler to monitor what is going on - looking
at what the process is doing especially at the end of the
import and after the import, what the durations are for
different steps and processes in the package.
You could also monitor it more "manually" by querying
sysprocesses and watching the wait types, activities, etc.
If you are running SQL 2000, sp3 you can also use fn_get_sql
to see what exactly it's executing at different points.
-Sue
On Mon, 2 May 2005 22:26:21 +0200, "Loane Sharp"
<look_sharp_not@.hotmail.com> wrote:
>Hi there
>I'm running a DTS package that imports data from various Excel spreadsheets
>into a SQL Server database. Running the package from the command prompt, I
>get information on each data pump task including each 1,000 rows imported
>successfully. When all rows have been imported from a particular worksheet
>(65536), DTS takes quite a bit of time before it initiates the data pump
>task for the next worksheet. I notice that the CPU falls idle during this
>time, but DTSrun.exe's memory usage remains high for some time afterward.
>This apparent idle time more than doubles the total amount taken to complet
e
>this DTS package. Do you know why this is so? I thought it might be time
>taken to build the indexes on each imported worksheet (I need to create eac
h
>table from scratch), or alternatively the time taken to establish
>communication with Excel. Any other ideas, and a suggestion on how to pick
>up the pace?!
>Best regards
>Loane
>|||thanks, will try this one, it's been really niggling me
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:3end71hhr8drpdqhs7kg8d4k6d99415lo3@.
4ax.com...
> You can use profiler to monitor what is going on - looking
> at what the process is doing especially at the end of the
> import and after the import, what the durations are for
> different steps and processes in the package.
> You could also monitor it more "manually" by querying
> sysprocesses and watching the wait types, activities, etc.
> If you are running SQL 2000, sp3 you can also use fn_get_sql
> to see what exactly it's executing at different points.
> -Sue
> On Mon, 2 May 2005 22:26:21 +0200, "Loane Sharp"
> <look_sharp_not@.hotmail.com> wrote:
>
>
DTS PACKAGES SECURITY ISSUE
Hi,
I need to give access to a user to use enterprise manager to create DTS
packages within a single database. However I have a problem that he can still
see other DTS packages and open them. IS there any way of restricting the DTS
packages so that he can only see what he had created and not others?
Thanks
Anp
Hi
It is not a good idea to develop DTS packages against a live system. You may
want to create a safe environment on a different server for this person to
work with.
John
"Anup" wrote:
> Hi,
> I need to give access to a user to use enterprise manager to create DTS
> packages within a single database. However I have a problem that he can still
> see other DTS packages and open them. IS there any way of restricting the DTS
> packages so that he can only see what he had created and not others?
> Thanks
> Anp
|||Additionally, you can secure DTS packages with DTS package
passwords. You can find explanations of this in books online
under the topic: Handling Package Security in DTS
-Sue
On Wed, 17 Aug 2005 23:11:01 -0700, "Anup"
<Anup@.discussions.microsoft.com> wrote:
>Hi,
>I need to give access to a user to use enterprise manager to create DTS
>packages within a single database. However I have a problem that he can still
>see other DTS packages and open them. IS there any way of restricting the DTS
>packages so that he can only see what he had created and not others?
>Thanks
>Anp
sqlsql
I need to give access to a user to use enterprise manager to create DTS
packages within a single database. However I have a problem that he can still
see other DTS packages and open them. IS there any way of restricting the DTS
packages so that he can only see what he had created and not others?
Thanks
Anp
Hi
It is not a good idea to develop DTS packages against a live system. You may
want to create a safe environment on a different server for this person to
work with.
John
"Anup" wrote:
> Hi,
> I need to give access to a user to use enterprise manager to create DTS
> packages within a single database. However I have a problem that he can still
> see other DTS packages and open them. IS there any way of restricting the DTS
> packages so that he can only see what he had created and not others?
> Thanks
> Anp
|||Additionally, you can secure DTS packages with DTS package
passwords. You can find explanations of this in books online
under the topic: Handling Package Security in DTS
-Sue
On Wed, 17 Aug 2005 23:11:01 -0700, "Anup"
<Anup@.discussions.microsoft.com> wrote:
>Hi,
>I need to give access to a user to use enterprise manager to create DTS
>packages within a single database. However I have a problem that he can still
>see other DTS packages and open them. IS there any way of restricting the DTS
>packages so that he can only see what he had created and not others?
>Thanks
>Anp
sqlsql
Subscribe to:
Posts (Atom)