I created a DTS package to transfer a table to a text file. Every time when
I click start manually, it'll run and transfer the data to a local txt file
with no problem. But when I schedule this DTS package and run it from the
SQL Agent - Jobs. It always fail to run. I don't know why. Anybody can help
me?|
| I created a DTS package to transfer a table to a text file. Every time
when
| I click start manually, it'll run and transfer the data to a local txt
file
| with no problem. But when I schedule this DTS package and run it from the
| SQL Agent - Jobs. It always fail to run. I don't know why. Anybody can
help
| me?
--
We need more information to help you. What errors are returned when the job
fails? Post it and we'll give it a shot.
Thanks,
--
Eric Cardenas
SQL Server support|||JL
I think that you cerated DTS package on the workstation not on the server ,
then SQL Agent on your wokstation is running under differnent account than
server.
Check it out.
"JL" <ljmagzine@.hotmail.com> wrote in message
news:eGGt1AgvDHA.2304@.TK2MSFTNGP12.phx.gbl...
> I created a DTS package to transfer a table to a text file. Every time
when
> I click start manually, it'll run and transfer the data to a local txt
file
> with no problem. But when I schedule this DTS package and run it from the
> SQL Agent - Jobs. It always fail to run. I don't know why. Anybody can
help
> me?
>|||VXJpIGlzIGNvcnJlY3QuICBJIHJhbiBpbnRvIHRoaXMgcHJvYmxlbSBhbmQgaXQgaGFkIG1lIHN0
dW1wZWQgZm9yIGEgd2hpbGUuDQoNClRoZSBzb2x1dGlvbiBpcyB0byBjcmVhdGUgdGhlIERUUyBw
YWNrYWdlIGFuZCBKb2Igd2hpbGUgcGh5c2ljYWxseSBhdCB0aGUgU1FMIFNlcnZlciBib3guDQoN
Cg0KIkpMIiA8bGptYWd6aW5lQGhvdG1haWwuY29tPiB3cm90ZSBpbiBtZXNzYWdlIG5ld3M6ZUdH
dDFBZ3ZESEEuMjMwNEBUSzJNU0ZUTkdQMTIucGh4LmdibC4uLg0KPiBJIGNyZWF0ZWQgYSBEVFMg
cGFja2FnZSB0byB0cmFuc2ZlciBhIHRhYmxlIHRvIGEgdGV4dCBmaWxlLiBFdmVyeSB0aW1lIHdo
ZW4NCj4gSSBjbGljayBzdGFydCBtYW51YWxseSwgaXQnbGwgcnVuIGFuZCB0cmFuc2ZlciB0aGUg
ZGF0YSB0byBhIGxvY2FsIHR4dCBmaWxlDQo+IHdpdGggbm8gcHJvYmxlbS4gQnV0IHdoZW4gSSBz
Y2hlZHVsZSB0aGlzIERUUyBwYWNrYWdlIGFuZCBydW4gaXQgZnJvbSB0aGUNCj4gU1FMIEFnZW50
IC0gSm9icy4gSXQgYWx3YXlzIGZhaWwgdG8gcnVuLiBJIGRvbid0IGtub3cgd2h5LiBBbnlib2R5
IGNhbiBoZWxwDQo+IG1lPw0KPiANCj4gsqlsql
Showing posts with label created. Show all posts
Showing posts with label created. Show all posts
Thursday, March 29, 2012
DTS Scheduling in SQL 2005
Hi all,
I am trying to create scheduled job for DTS. I have created new DTS in SQL 2005 but not able to create job out of it.
I tried creating Credential and then to create proxy with this:
Use master
CREATE CREDENTIAL [MyCredential] WITH IDENTITY = 'yourdomain\myWindowAccount', secret = 'WindowLoginPassword'
Use msdb
Sp_add_proxy @.proxy_name='MyProxy', @.credential_name='MyCredential'
Sp_grant_login_to_proxy @.login_name=' devlogin', @.proxy_name='MyProxy'
Sp_grant_proxy_to_subsystem @.proxy_name='MyProxy', @.subsystem_name='SSIS'
but could not get the job to schedule it. when i try to search for the DTS package(TestRev) it could not find it:
Is there a way that i can just call TSQL statement to run that dam DTS because it is too confusing to create job for DTS.
ThanksI have created new DTS in SQL 2005No, you didn't. DTS is not supported in 2005. Please clarify, and maybe we can answer you question.|||I mean to SSIS packages and would like to schedule it to run in job,|||OK, the job scheduler is a bit confusing, but it is going to be around for a while so you might as well bite the bullet and jump in.
And scheduling a package is not very difficult.
In the job scheduler, name your package and then set the type to "SQL Server Integration Services Package".
Set the package source in the General tab at the bottom of the window. (Not the General tab on the left of the window. I agreed it was a bit confusing...)|||i tried that and it says that the specified package could not be loaded from SQL server Database.
I select SQL server and then name of server and then name but not able to locate it.
thanks|||Where did you save your package? If you saved it to a file, you will need to point to the file name.|||I just started playing with SSBIDS
What a gdman pig|||i saved it on Server.
I am trying to create scheduled job for DTS. I have created new DTS in SQL 2005 but not able to create job out of it.
I tried creating Credential and then to create proxy with this:
Use master
CREATE CREDENTIAL [MyCredential] WITH IDENTITY = 'yourdomain\myWindowAccount', secret = 'WindowLoginPassword'
Use msdb
Sp_add_proxy @.proxy_name='MyProxy', @.credential_name='MyCredential'
Sp_grant_login_to_proxy @.login_name=' devlogin', @.proxy_name='MyProxy'
Sp_grant_proxy_to_subsystem @.proxy_name='MyProxy', @.subsystem_name='SSIS'
but could not get the job to schedule it. when i try to search for the DTS package(TestRev) it could not find it:
Is there a way that i can just call TSQL statement to run that dam DTS because it is too confusing to create job for DTS.
ThanksI have created new DTS in SQL 2005No, you didn't. DTS is not supported in 2005. Please clarify, and maybe we can answer you question.|||I mean to SSIS packages and would like to schedule it to run in job,|||OK, the job scheduler is a bit confusing, but it is going to be around for a while so you might as well bite the bullet and jump in.
And scheduling a package is not very difficult.
In the job scheduler, name your package and then set the type to "SQL Server Integration Services Package".
Set the package source in the General tab at the bottom of the window. (Not the General tab on the left of the window. I agreed it was a bit confusing...)|||i tried that and it says that the specified package could not be loaded from SQL server Database.
I select SQL server and then name of server and then name but not able to locate it.
thanks|||Where did you save your package? If you saved it to a file, you will need to point to the file name.|||I just started playing with SSBIDS
What a gdman pig|||i saved it on Server.
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.
DTS schedule job fails urgent pls help!
Hi all,
We have created DTS jobs which copies data from one server to another and truncates the destination before copying the data from source. when we run the job manually it runs fine, but when schedule its failing with the below errors.
DTSRun: Loading...DTSRun: Executing...DTSRun
OnStart: DTSStep_DTSExecuteSQLTask_1DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1DTSRun OnStart: Copy Data
from CustomerMaster to
[OUTBOUND].[dbo].[CustomerMaster]
StepDTSRun OnError: Copy Data from CustomerMaster
to [OUTBOUND].[dbo].[CustomerMaster]
Step, Error = -2147008507 (80074005) Error string:
Unspecified error Error source: Microsoft Data
Transformation Services (DTS) Package Help file:
sqldts.hlp Help context: 1100Error Detail
Records:Error: -2147008507 (80074005); Provider
Error: 0 (0) Error string: Unspecified error
Error source: Microsoft Data Transformation Services
(DTS) Package Help file: sqldts.hlp Help context:
1100Error: -2147467259 (80004005); Provider Error:
0 (0) Error string: Timeout expired Error source:
Microsoft OLE DB Provider for SQL Server Help file:
Help context: 0DTSRun OnFinish: Copy Data from
CustomerMaster to
[OUTBOUND].[dbo].[CustomerMaster]
StepDTSRun: Package execution complete.
please suggest to over come this problem.
TIAMy guess is that the DTS job is scheduled to run as a different NT logon than the one you are using to test it interactively. The scheduled logon is probably missing one or more permissions needed for the DTS package.
-PatP
We have created DTS jobs which copies data from one server to another and truncates the destination before copying the data from source. when we run the job manually it runs fine, but when schedule its failing with the below errors.
DTSRun: Loading...DTSRun: Executing...DTSRun
OnStart: DTSStep_DTSExecuteSQLTask_1DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1DTSRun OnStart: Copy Data
from CustomerMaster to
[OUTBOUND].[dbo].[CustomerMaster]
StepDTSRun OnError: Copy Data from CustomerMaster
to [OUTBOUND].[dbo].[CustomerMaster]
Step, Error = -2147008507 (80074005) Error string:
Unspecified error Error source: Microsoft Data
Transformation Services (DTS) Package Help file:
sqldts.hlp Help context: 1100Error Detail
Records:Error: -2147008507 (80074005); Provider
Error: 0 (0) Error string: Unspecified error
Error source: Microsoft Data Transformation Services
(DTS) Package Help file: sqldts.hlp Help context:
1100Error: -2147467259 (80004005); Provider Error:
0 (0) Error string: Timeout expired Error source:
Microsoft OLE DB Provider for SQL Server Help file:
Help context: 0DTSRun OnFinish: Copy Data from
CustomerMaster to
[OUTBOUND].[dbo].[CustomerMaster]
StepDTSRun: Package execution complete.
please suggest to over come this problem.
TIAMy guess is that the DTS job is scheduled to run as a different NT logon than the one you are using to test it interactively. The scheduled logon is probably missing one or more permissions needed for the DTS package.
-PatP
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 question.
Hi,
i want to transfer data from an Access database to
a SQL Server database. I have created a DTS package
that does that job. The problem is that every time it is
executed, it transfers all the data of Access db, and
because it tries to add data that already exist in a table,
results in failure (because of keys...).
So, my question is if there is any way of transfering only
the updated data which (the way) is not based on checking
row by row the destination and source tables.
In example if from access could know which rows has updated
during last day.
Thanks in advance.Originally posted by johan_gr
Hi,
i want to transfer data from an Access database to
a SQL Server database. I have created a DTS package
that does that job. The problem is that every time it is
executed, it transfers all the data of Access db, and
because it tries to add data that already exist in a table,
results in failure (because of keys...).
So, my question is if there is any way of transfering only
the updated data which (the way) is not based on checking
row by row the destination and source tables.
In example if from access could know which rows has updated
during last day.
Thanks in advance.
If you have a date and time value it could be done in your query..
WHERE date = > 'dateyourchecking'
Ken|||GA_KEN is right; it will generally mean substantially more work for you than just using the DTS Wizard however. For each table that you want to port to SQL Server, you will have to write a query (after determining what logic to use to assure that duplicate records are not brought across). Although often times this is a pretty straightforward task, there are a lot of complicating factors. In some cases, it may be preferable to simply truncate (delete * from mytable in Access) the existing table and re-load it from scratch.
Good luck,
Hugh Scott|||but im not an expert on that, i dont have time for that kindof thing...
cant you just reimport the whole table from scratch?
or do some sort of 2-stage import, where you just import recently changed records and then you push these into the production server on the database side (by using a stored procedure-- insert into dbo.productionTbl select * from AccessImport where date > (Select Min(Date) from myDateCatcher)
i want to transfer data from an Access database to
a SQL Server database. I have created a DTS package
that does that job. The problem is that every time it is
executed, it transfers all the data of Access db, and
because it tries to add data that already exist in a table,
results in failure (because of keys...).
So, my question is if there is any way of transfering only
the updated data which (the way) is not based on checking
row by row the destination and source tables.
In example if from access could know which rows has updated
during last day.
Thanks in advance.Originally posted by johan_gr
Hi,
i want to transfer data from an Access database to
a SQL Server database. I have created a DTS package
that does that job. The problem is that every time it is
executed, it transfers all the data of Access db, and
because it tries to add data that already exist in a table,
results in failure (because of keys...).
So, my question is if there is any way of transfering only
the updated data which (the way) is not based on checking
row by row the destination and source tables.
In example if from access could know which rows has updated
during last day.
Thanks in advance.
If you have a date and time value it could be done in your query..
WHERE date = > 'dateyourchecking'
Ken|||GA_KEN is right; it will generally mean substantially more work for you than just using the DTS Wizard however. For each table that you want to port to SQL Server, you will have to write a query (after determining what logic to use to assure that duplicate records are not brought across). Although often times this is a pretty straightforward task, there are a lot of complicating factors. In some cases, it may be preferable to simply truncate (delete * from mytable in Access) the existing table and re-load it from scratch.
Good luck,
Hugh Scott|||but im not an expert on that, i dont have time for that kindof thing...
cant you just reimport the whole table from scratch?
or do some sort of 2-stage import, where you just import recently changed records and then you push these into the production server on the database side (by using a stored procedure-- insert into dbo.productionTbl select * from AccessImport where date > (Select Min(Date) from myDateCatcher)
DTS Question
I am using SQL Server 2000 and I have a created a DTS package that
copies Data from a Excel Spreasheet to a SQL Server table then executes
a user Stored Proc
What I am wanting to do is if for some reason the Stored Proc errors
out because of bad data , I would like to see the errors reported in a
file so that it can be reviewed and I was not sure if there is a way to
output the errors in DTS. When I run the Stored Proc theough Query
Analyzer I see the errors in Query Analyzer, I basically want to see
the same information after running the DTS. Can this be done, if so
how.
Any help in this regard is greatly appreciated.
ThanksI would use the DTSRUN command to execute the DTS package. On the DTSRUN
command you can use the /L option to create a log froma DTS package.
"shub" wrote:
> I am using SQL Server 2000 and I have a created a DTS package that
> copies Data from a Excel Spreasheet to a SQL Server table then executes
> a user Stored Proc
> What I am wanting to do is if for some reason the Stored Proc errors
> out because of bad data , I would like to see the errors reported in a
> file so that it can be reviewed and I was not sure if there is a way to
> output the errors in DTS. When I run the Stored Proc theough Query
> Analyzer I see the errors in Query Analyzer, I basically want to see
> the same information after running the DTS. Can this be done, if so
> how.
> Any help in this regard is greatly appreciated.
> Thanks
>|||Thanks Greg for your response. I will definitely look at that option.
Is there any way this option could be incorporated when executed
through enterprise manager.
Greg Larsen wrote:
> I would use the DTSRUN command to execute the DTS package. On the DTSRUN
> command you can use the /L option to create a log froma DTS package.
> "shub" wrote:
> > I am using SQL Server 2000 and I have a created a DTS package that
> > copies Data from a Excel Spreasheet to a SQL Server table then executes
> > a user Stored Proc
> > What I am wanting to do is if for some reason the Stored Proc errors
> > out because of bad data , I would like to see the errors reported in a
> > file so that it can be reviewed and I was not sure if there is a way to
> > output the errors in DTS. When I run the Stored Proc theough Query
> > Analyzer I see the errors in Query Analyzer, I basically want to see
> > the same information after running the DTS. Can this be done, if so
> > how.
> >
> > Any help in this regard is greatly appreciated.
> > Thanks
> >
> >|||I don't know of any way, sorry.
"shub" wrote:
> Thanks Greg for your response. I will definitely look at that option.
> Is there any way this option could be incorporated when executed
> through enterprise manager.
> Greg Larsen wrote:
> > I would use the DTSRUN command to execute the DTS package. On the DTSRUN
> > command you can use the /L option to create a log froma DTS package.
> >
> > "shub" wrote:
> >
> > > I am using SQL Server 2000 and I have a created a DTS package that
> > > copies Data from a Excel Spreasheet to a SQL Server table then executes
> > > a user Stored Proc
> > > What I am wanting to do is if for some reason the Stored Proc errors
> > > out because of bad data , I would like to see the errors reported in a
> > > file so that it can be reviewed and I was not sure if there is a way to
> > > output the errors in DTS. When I run the Stored Proc theough Query
> > > Analyzer I see the errors in Query Analyzer, I basically want to see
> > > the same information after running the DTS. Can this be done, if so
> > > how.
> > >
> > > Any help in this regard is greatly appreciated.
> > > Thanks
> > >
> > >
>|||Hi Greg,
Yes, if you open up your DTS package and go to Package => Properties you
will see a tab for 'Logging', in the 'Error Handling' section you can specify
a file to log to. Just remember that the file is always appended to and not
overwritten.
Ray
"shub" wrote:
> Thanks Greg for your response. I will definitely look at that option.
> Is there any way this option could be incorporated when executed
> through enterprise manager.
> Greg Larsen wrote:
> > I would use the DTSRUN command to execute the DTS package. On the DTSRUN
> > command you can use the /L option to create a log froma DTS package.
> >
> > "shub" wrote:
> >
> > > I am using SQL Server 2000 and I have a created a DTS package that
> > > copies Data from a Excel Spreasheet to a SQL Server table then executes
> > > a user Stored Proc
> > > What I am wanting to do is if for some reason the Stored Proc errors
> > > out because of bad data , I would like to see the errors reported in a
> > > file so that it can be reviewed and I was not sure if there is a way to
> > > output the errors in DTS. When I run the Stored Proc theough Query
> > > Analyzer I see the errors in Query Analyzer, I basically want to see
> > > the same information after running the DTS. Can this be done, if so
> > > how.
> > >
> > > Any help in this regard is greatly appreciated.
> > > Thanks
> > >
> > >
>|||Thanks Ray. I tried using that however when there are multiple errorrs
it is displaying only the first error. For example in my case the DTS
package executes a stored proc to add logins from the table but in some
cases because of typos the proc cannot grant access because it cannot
find the user account in the domain, but if there are multiple errors
it is displaying the very firts one but when I run the same stored proc
through Query analyzer I see all the errors and I need to see all the
errors so that it can be informed that there are wrong entries in the
table.
Any ideas? Here is the only error I am getting
Step 'DTSStep_DTSExecuteSQLTask_2' failed
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:The task reported failure on execution.
(Microsoft OLE DB Provider for SQL Server (80040e14): Windows NT user
or group 'YYY\XXX' not found. Check the name again.)
Step Error code: 8004043B
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
****************************************************************************************************
rb wrote:
> Hi Greg,
> Yes, if you open up your DTS package and go to Package => Properties you
> will see a tab for 'Logging', in the 'Error Handling' section you can specify
> a file to log to. Just remember that the file is always appended to and not
> overwritten.
> Ray
> "shub" wrote:
> > Thanks Greg for your response. I will definitely look at that option.
> > Is there any way this option could be incorporated when executed
> > through enterprise manager.
> > Greg Larsen wrote:
> > > I would use the DTSRUN command to execute the DTS package. On the DTSRUN
> > > command you can use the /L option to create a log froma DTS package.
> > >
> > > "shub" wrote:
> > >
> > > > I am using SQL Server 2000 and I have a created a DTS package that
> > > > copies Data from a Excel Spreasheet to a SQL Server table then executes
> > > > a user Stored Proc
> > > > What I am wanting to do is if for some reason the Stored Proc errors
> > > > out because of bad data , I would like to see the errors reported in a
> > > > file so that it can be reviewed and I was not sure if there is a way to
> > > > output the errors in DTS. When I run the Stored Proc theough Query
> > > > Analyzer I see the errors in Query Analyzer, I basically want to see
> > > > the same information after running the DTS. Can this be done, if so
> > > > how.
> > > >
> > > > Any help in this regard is greatly appreciated.
> > > > Thanks
> > > >
> > > >
> >
> >
copies Data from a Excel Spreasheet to a SQL Server table then executes
a user Stored Proc
What I am wanting to do is if for some reason the Stored Proc errors
out because of bad data , I would like to see the errors reported in a
file so that it can be reviewed and I was not sure if there is a way to
output the errors in DTS. When I run the Stored Proc theough Query
Analyzer I see the errors in Query Analyzer, I basically want to see
the same information after running the DTS. Can this be done, if so
how.
Any help in this regard is greatly appreciated.
ThanksI would use the DTSRUN command to execute the DTS package. On the DTSRUN
command you can use the /L option to create a log froma DTS package.
"shub" wrote:
> I am using SQL Server 2000 and I have a created a DTS package that
> copies Data from a Excel Spreasheet to a SQL Server table then executes
> a user Stored Proc
> What I am wanting to do is if for some reason the Stored Proc errors
> out because of bad data , I would like to see the errors reported in a
> file so that it can be reviewed and I was not sure if there is a way to
> output the errors in DTS. When I run the Stored Proc theough Query
> Analyzer I see the errors in Query Analyzer, I basically want to see
> the same information after running the DTS. Can this be done, if so
> how.
> Any help in this regard is greatly appreciated.
> Thanks
>|||Thanks Greg for your response. I will definitely look at that option.
Is there any way this option could be incorporated when executed
through enterprise manager.
Greg Larsen wrote:
> I would use the DTSRUN command to execute the DTS package. On the DTSRUN
> command you can use the /L option to create a log froma DTS package.
> "shub" wrote:
> > I am using SQL Server 2000 and I have a created a DTS package that
> > copies Data from a Excel Spreasheet to a SQL Server table then executes
> > a user Stored Proc
> > What I am wanting to do is if for some reason the Stored Proc errors
> > out because of bad data , I would like to see the errors reported in a
> > file so that it can be reviewed and I was not sure if there is a way to
> > output the errors in DTS. When I run the Stored Proc theough Query
> > Analyzer I see the errors in Query Analyzer, I basically want to see
> > the same information after running the DTS. Can this be done, if so
> > how.
> >
> > Any help in this regard is greatly appreciated.
> > Thanks
> >
> >|||I don't know of any way, sorry.
"shub" wrote:
> Thanks Greg for your response. I will definitely look at that option.
> Is there any way this option could be incorporated when executed
> through enterprise manager.
> Greg Larsen wrote:
> > I would use the DTSRUN command to execute the DTS package. On the DTSRUN
> > command you can use the /L option to create a log froma DTS package.
> >
> > "shub" wrote:
> >
> > > I am using SQL Server 2000 and I have a created a DTS package that
> > > copies Data from a Excel Spreasheet to a SQL Server table then executes
> > > a user Stored Proc
> > > What I am wanting to do is if for some reason the Stored Proc errors
> > > out because of bad data , I would like to see the errors reported in a
> > > file so that it can be reviewed and I was not sure if there is a way to
> > > output the errors in DTS. When I run the Stored Proc theough Query
> > > Analyzer I see the errors in Query Analyzer, I basically want to see
> > > the same information after running the DTS. Can this be done, if so
> > > how.
> > >
> > > Any help in this regard is greatly appreciated.
> > > Thanks
> > >
> > >
>|||Hi Greg,
Yes, if you open up your DTS package and go to Package => Properties you
will see a tab for 'Logging', in the 'Error Handling' section you can specify
a file to log to. Just remember that the file is always appended to and not
overwritten.
Ray
"shub" wrote:
> Thanks Greg for your response. I will definitely look at that option.
> Is there any way this option could be incorporated when executed
> through enterprise manager.
> Greg Larsen wrote:
> > I would use the DTSRUN command to execute the DTS package. On the DTSRUN
> > command you can use the /L option to create a log froma DTS package.
> >
> > "shub" wrote:
> >
> > > I am using SQL Server 2000 and I have a created a DTS package that
> > > copies Data from a Excel Spreasheet to a SQL Server table then executes
> > > a user Stored Proc
> > > What I am wanting to do is if for some reason the Stored Proc errors
> > > out because of bad data , I would like to see the errors reported in a
> > > file so that it can be reviewed and I was not sure if there is a way to
> > > output the errors in DTS. When I run the Stored Proc theough Query
> > > Analyzer I see the errors in Query Analyzer, I basically want to see
> > > the same information after running the DTS. Can this be done, if so
> > > how.
> > >
> > > Any help in this regard is greatly appreciated.
> > > Thanks
> > >
> > >
>|||Thanks Ray. I tried using that however when there are multiple errorrs
it is displaying only the first error. For example in my case the DTS
package executes a stored proc to add logins from the table but in some
cases because of typos the proc cannot grant access because it cannot
find the user account in the domain, but if there are multiple errors
it is displaying the very firts one but when I run the same stored proc
through Query analyzer I see all the errors and I need to see all the
errors so that it can be informed that there are wrong entries in the
table.
Any ideas? Here is the only error I am getting
Step 'DTSStep_DTSExecuteSQLTask_2' failed
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:The task reported failure on execution.
(Microsoft OLE DB Provider for SQL Server (80040e14): Windows NT user
or group 'YYY\XXX' not found. Check the name again.)
Step Error code: 8004043B
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
****************************************************************************************************
rb wrote:
> Hi Greg,
> Yes, if you open up your DTS package and go to Package => Properties you
> will see a tab for 'Logging', in the 'Error Handling' section you can specify
> a file to log to. Just remember that the file is always appended to and not
> overwritten.
> Ray
> "shub" wrote:
> > Thanks Greg for your response. I will definitely look at that option.
> > Is there any way this option could be incorporated when executed
> > through enterprise manager.
> > Greg Larsen wrote:
> > > I would use the DTSRUN command to execute the DTS package. On the DTSRUN
> > > command you can use the /L option to create a log froma DTS package.
> > >
> > > "shub" wrote:
> > >
> > > > I am using SQL Server 2000 and I have a created a DTS package that
> > > > copies Data from a Excel Spreasheet to a SQL Server table then executes
> > > > a user Stored Proc
> > > > What I am wanting to do is if for some reason the Stored Proc errors
> > > > out because of bad data , I would like to see the errors reported in a
> > > > file so that it can be reviewed and I was not sure if there is a way to
> > > > output the errors in DTS. When I run the Stored Proc theough Query
> > > > Analyzer I see the errors in Query Analyzer, I basically want to see
> > > > the same information after running the DTS. Can this be done, if so
> > > > how.
> > > >
> > > > Any help in this regard is greatly appreciated.
> > > > Thanks
> > > >
> > > >
> >
> >
Sunday, March 25, 2012
DTS Problem
Hi there,
I have created a DTS that copies data from a table in SQL Server to a table
with the same structure, but in Oracle.
Lets suppose I have 100 rows in the origin table. If the DTS fails in he
middle of the operation, supposing the row 55 has an error, the previous
rows are copied to the Oracle table.
How can I now wich rows have been copied?
The DTS runs periodicaly, and I need to know wich rows are copied, so I can
block them on the SQL Server, so they can not be copied again to Oracle.
Can anybody help me? Thanks in advance.
Regards,
Marcothis is not the proper way. but it works for me.
Make it 2 step.
In the first step, put the -ve of the real value; If the dts succees,
select all the rows with -ve value in the field and put it back to positive.
If it abends, you know all the rows with -ve value came from unsuccessful
import.
you can try other methods like prefixing text field like say lastname with
"SQL-"+lname and select all the rows where lastname like "SQL-" and remove
that.sqlsql
I have created a DTS that copies data from a table in SQL Server to a table
with the same structure, but in Oracle.
Lets suppose I have 100 rows in the origin table. If the DTS fails in he
middle of the operation, supposing the row 55 has an error, the previous
rows are copied to the Oracle table.
How can I now wich rows have been copied?
The DTS runs periodicaly, and I need to know wich rows are copied, so I can
block them on the SQL Server, so they can not be copied again to Oracle.
Can anybody help me? Thanks in advance.
Regards,
Marcothis is not the proper way. but it works for me.
Make it 2 step.
In the first step, put the -ve of the real value; If the dts succees,
select all the rows with -ve value in the field and put it back to positive.
If it abends, you know all the rows with -ve value came from unsuccessful
import.
you can try other methods like prefixing text field like say lastname with
"SQL-"+lname and select all the rows where lastname like "SQL-" and remove
that.sqlsql
DTS Permissions
We have a DTS package that was created and saved by
someone who is no longer with the company. How can we
convert this DTS package to a valid ID? ThanksHi Natalie
I am reviewing your post and our community members provide some good
suggestions to your problem. Have you try these methods? Do they resolve
problem? If you still have any questions about it, please feel free to post
message here and we are glad to help!
Looking forward to hearing from you. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.sqlsql
someone who is no longer with the company. How can we
convert this DTS package to a valid ID? ThanksHi Natalie
I am reviewing your post and our community members provide some good
suggestions to your problem. Have you try these methods? Do they resolve
problem? If you still have any questions about it, please feel free to post
message here and we are glad to help!
Looking forward to hearing from you. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.sqlsql
DTS permission
I created a DTS package to extract data to a text file.
I want to create a login for a user who should be able ONLY to run this DTS. I don't want to give him/her any access to any database .
Is that possible?
Thanks in advance
GiorgioWithout permission on database user cannot complete the execution of DTS package.|||I find out a workaround:
1) I created a user that has permission only on the sample DB Northwind.
2) The DTS package is protected by "DTS Owner password" and DTS User password
3) The "Connection" in the DTS package has inside a username and password that has acess to the appropriate DB.
With this workaround I obtained that:
1) The user has access only to the Northwind DB
2) The user can run only the DTS package (because is protected with DTS User password).
Thanks anyway to everyone who replied to me!|||Then my reply was intended to light when you said no permission to any database.
I want to create a login for a user who should be able ONLY to run this DTS. I don't want to give him/her any access to any database .
Is that possible?
Thanks in advance
GiorgioWithout permission on database user cannot complete the execution of DTS package.|||I find out a workaround:
1) I created a user that has permission only on the sample DB Northwind.
2) The DTS package is protected by "DTS Owner password" and DTS User password
3) The "Connection" in the DTS package has inside a username and password that has acess to the appropriate DB.
With this workaround I obtained that:
1) The user has access only to the Northwind DB
2) The user can run only the DTS package (because is protected with DTS User password).
Thanks anyway to everyone who replied to me!|||Then my reply was intended to light when you said no permission to any database.
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 are not working as job
Hello,
I scheduled a dts package and it created automatically a job but the job fails with following error :
(reason: The system cannot find the file specified).
dts package is connecting the databases which exist on the server and I created this dts package on the same server as user sa.
I don't use a file as a data source or for something else in the package.
Server is SQL 2000 with SP3 and operating system is win2k.
I hope there is someone who has an idea...
elifElif:
What EXACTLY is you package doing? In other words, are you using ActiveX + VBS to create and Run the job?
RobbieD|||My package is copying a database into another one and changing some characters in some fields. I added a vb code to transformations screen for this.
The package works fine when executed. Problem is scheduling. I want to execute it at night because the people are working one of the databases which dts uses.
If I can not schedule this I have to schedule my own night or weekend. This is ridiculous.|||...SO you have already right-clicked the package in Enterprise Manager, selected Schedule Package - but that doesn't work, right?|||Originally posted by robbied111
...SO you have already right-clicked the package in Enterprise Manager, selected Schedule Package - but that doesn't work, right?
It worked and created the job. The problem is the "job". That does not work.|||I recall our DBA having the same issue:
I believe that the job may need to be scheduled directly on the server with the logged in user having admin priviledges on the server. It had less to do with SQL Server but was an effect of Windows 2000.
I may be offbase but that is what he found.
Originally posted by eguven
It worked and created the job. The problem is the "job". That does not work.|||The weather was stormy on friday, so we did not work.
Thanks for responses.
I'm working directly on the server and I'm user 'sa'.
I also prepared and scheduled the dts package on the server.
And this job does not work.
I have read something on the msdn related the user priviliges problem with this issue. That's why I did everything on the server as they said.
There is something else in my problem. I don't really understand.|||Dear friends,
I solved by myself this crazy problem. I wanted to share solution with you. When I scheduled the dts package, a job was created automatically. But it uses DTSRUN utility to run the package. This utility can only be run in the "c:\Program Files\Microsoft SQL Server\80\Tools\Binn " directory. I don't know why... But there is no path information in the job definition. That's why I recevied "File not found" message. I added this path to job and it worked. No need to be smart when working with a microft. We must just think simple.
bye
elif|||You might have removed it from the path variable while making some config settings ... otherwise it is automatically added when you install sql server .. You can view and set the path variables in System properties .. Advanced Tab ... Environment Variables ... No need to blame MS for your own fault. :)
I scheduled a dts package and it created automatically a job but the job fails with following error :
(reason: The system cannot find the file specified).
dts package is connecting the databases which exist on the server and I created this dts package on the same server as user sa.
I don't use a file as a data source or for something else in the package.
Server is SQL 2000 with SP3 and operating system is win2k.
I hope there is someone who has an idea...
elifElif:
What EXACTLY is you package doing? In other words, are you using ActiveX + VBS to create and Run the job?
RobbieD|||My package is copying a database into another one and changing some characters in some fields. I added a vb code to transformations screen for this.
The package works fine when executed. Problem is scheduling. I want to execute it at night because the people are working one of the databases which dts uses.
If I can not schedule this I have to schedule my own night or weekend. This is ridiculous.|||...SO you have already right-clicked the package in Enterprise Manager, selected Schedule Package - but that doesn't work, right?|||Originally posted by robbied111
...SO you have already right-clicked the package in Enterprise Manager, selected Schedule Package - but that doesn't work, right?
It worked and created the job. The problem is the "job". That does not work.|||I recall our DBA having the same issue:
I believe that the job may need to be scheduled directly on the server with the logged in user having admin priviledges on the server. It had less to do with SQL Server but was an effect of Windows 2000.
I may be offbase but that is what he found.
Originally posted by eguven
It worked and created the job. The problem is the "job". That does not work.|||The weather was stormy on friday, so we did not work.
Thanks for responses.
I'm working directly on the server and I'm user 'sa'.
I also prepared and scheduled the dts package on the server.
And this job does not work.
I have read something on the msdn related the user priviliges problem with this issue. That's why I did everything on the server as they said.
There is something else in my problem. I don't really understand.|||Dear friends,
I solved by myself this crazy problem. I wanted to share solution with you. When I scheduled the dts package, a job was created automatically. But it uses DTSRUN utility to run the package. This utility can only be run in the "c:\Program Files\Microsoft SQL Server\80\Tools\Binn " directory. I don't know why... But there is no path information in the job definition. That's why I recevied "File not found" message. I added this path to job and it worked. No need to be smart when working with a microft. We must just think simple.
bye
elif|||You might have removed it from the path variable while making some config settings ... otherwise it is automatically added when you install sql server .. You can view and set the path variables in System properties .. Advanced Tab ... Environment Variables ... No need to blame MS for your own fault. :)
DTS packages
I have a query when a DTS package is created, does it create a
file anywhere on the server
ThanksDepends. DTS packages can be saved to a file (strucutred storage or a VBS
file), or to SQL Server.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:07F172CA-1FFC-44BD-8465-B12799690E2F@.microsoft.com...
I have a query when a DTS package is created, does it create a
file anywhere on the server
Thanks
file anywhere on the server
ThanksDepends. DTS packages can be saved to a file (strucutred storage or a VBS
file), or to SQL Server.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:07F172CA-1FFC-44BD-8465-B12799690E2F@.microsoft.com...
I have a query when a DTS package is created, does it create a
file anywhere on the server
Thanks
DTS Packages
Hi
When DTS packages are created, where do the physical location of the files
kept on the severThe default option for saving DTS packages, is to save them to SQL Server,
in which case they get stored in the table msdb.dbo.sysdtspackages.
Alternatively you can save them to files, or .vbs files or to repository.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:FE0DD431-B9C5-43E3-9020-34CBFA017E26@.microsoft.com...
Hi
When DTS packages are created, where do the physical location of the files
kept on the sever
When DTS packages are created, where do the physical location of the files
kept on the severThe default option for saving DTS packages, is to save them to SQL Server,
in which case they get stored in the table msdb.dbo.sysdtspackages.
Alternatively you can save them to files, or .vbs files or to repository.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:FE0DD431-B9C5-43E3-9020-34CBFA017E26@.microsoft.com...
Hi
When DTS packages are created, where do the physical location of the files
kept on the sever
Wednesday, March 21, 2012
DTS packages
I have a query when a DTS package is created, does it create a
file anywhere on the server
Thanks
Depends. DTS packages can be saved to a file (strucutred storage or a VBS
file), or to SQL Server.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:07F172CA-1FFC-44BD-8465-B12799690E2F@.microsoft.com...
I have a query when a DTS package is created, does it create a
file anywhere on the server
Thanks
sqlsql
file anywhere on the server
Thanks
Depends. DTS packages can be saved to a file (strucutred storage or a VBS
file), or to SQL Server.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:07F172CA-1FFC-44BD-8465-B12799690E2F@.microsoft.com...
I have a query when a DTS package is created, does it create a
file anywhere on the server
Thanks
sqlsql
DTS Packages
Hi
When DTS packages are created, where do the physical location of the files
kept on the sever
The default option for saving DTS packages, is to save them to SQL Server,
in which case they get stored in the table msdb.dbo.sysdtspackages.
Alternatively you can save them to files, or .vbs files or to repository.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:FE0DD431-B9C5-43E3-9020-34CBFA017E26@.microsoft.com...
Hi
When DTS packages are created, where do the physical location of the files
kept on the sever
When DTS packages are created, where do the physical location of the files
kept on the sever
The default option for saving DTS packages, is to save them to SQL Server,
in which case they get stored in the table msdb.dbo.sysdtspackages.
Alternatively you can save them to files, or .vbs files or to repository.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:FE0DD431-B9C5-43E3-9020-34CBFA017E26@.microsoft.com...
Hi
When DTS packages are created, where do the physical location of the files
kept on the sever
Subscribe to:
Posts (Atom)