Help! - DTS/Job Task (SQL Server 2000)

Hello all,
I have this situation, I have a DTS package consisting of a series of data
transformations and data transfers from our server to remote servers. If I
execute the DTS from the Data Transformation Services --> Local Packages, it
runs smoothly without any problems. But, when I schedule that same DTS
package and have SQL (Server 2000) run it as a task on a daily manner it
runs "successfully" (according to SQL) but when I see the job history I see
that no records were transferred even though it says "successfully" (which,
obviously, isn't)
So, my question is, what could be causing that a DTS package running from a
Job Task transfers no records and when I run it directly from Data
Transformation Services --> Local Packages, it transfers the right data
(between 180 and 200 records depending on the day, but NEVER 0 records)
is it permissions? it it something else? (btw, my DTS package doesn't need
parameters or anything like that)
Thanks in advance for all your help,
SB-R> So, my question is, what could be causing that a DTS package running from
> a Job Task transfers no records and when I run it directly from Data
> Transformation Services --> Local Packages, it transfers the right data
> (between 180 and 200 records depending on the day, but NEVER 0 records)
> is it permissions? it it something else? (btw, my DTS package doesn't need
> parameters or anything like that)
If you run the package remotely using Enterprise Manager, it runs on the
client machine. However, the package runs on the sever when launched via a
job. Perhaps this is reason for the difference.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"segis bata" <segisbata@.hotmail.com> wrote in message
news:u6xcRrWWIHA.4896@.TK2MSFTNGP06.phx.gbl...
> Hello all,
> I have this situation, I have a DTS package consisting of a series of data
> transformations and data transfers from our server to remote servers. If I
> execute the DTS from the Data Transformation Services --> Local Packages,
> it runs smoothly without any problems. But, when I schedule that same DTS
> package and have SQL (Server 2000) run it as a task on a daily manner it
> runs "successfully" (according to SQL) but when I see the job history I
> see that no records were transferred even though it says "successfully"
> (which, obviously, isn't)
> So, my question is, what could be causing that a DTS package running from
> a Job Task transfers no records and when I run it directly from Data
> Transformation Services --> Local Packages, it transfers the right data
> (between 180 and 200 records depending on the day, but NEVER 0 records)
> is it permissions? it it something else? (btw, my DTS package doesn't need
> parameters or anything like that)
> Thanks in advance for all your help,
> SB-R
>|||Dan,
I know that, that's why when I run it (I should've specified this in my
previous message) I use remote desktop, so I'm running "locally" from within
the same server the job task must run.
But thanks for your pointing that out,
SB-R
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:8DE4CE3E-6435-43CA-A152-A21982BF06F7@.microsoft.com...
>> So, my question is, what could be causing that a DTS package running from
>> a Job Task transfers no records and when I run it directly from Data
>> Transformation Services --> Local Packages, it transfers the right data
>> (between 180 and 200 records depending on the day, but NEVER 0 records)
>> is it permissions? it it something else? (btw, my DTS package doesn't
>> need parameters or anything like that)
> If you run the package remotely using Enterprise Manager, it runs on the
> client machine. However, the package runs on the sever when launched via
> a job. Perhaps this is reason for the difference.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "segis bata" <segisbata@.hotmail.com> wrote in message
> news:u6xcRrWWIHA.4896@.TK2MSFTNGP06.phx.gbl...
>> Hello all,
>> I have this situation, I have a DTS package consisting of a series of
>> data transformations and data transfers from our server to remote
>> servers. If I execute the DTS from the Data Transformation Services -->
>> Local Packages, it runs smoothly without any problems. But, when I
>> schedule that same DTS package and have SQL (Server 2000) run it as a
>> task on a daily manner it runs "successfully" (according to SQL) but when
>> I see the job history I see that no records were transferred even though
>> it says "successfully" (which, obviously, isn't)
>> So, my question is, what could be causing that a DTS package running from
>> a Job Task transfers no records and when I run it directly from Data
>> Transformation Services --> Local Packages, it transfers the right data
>> (between 180 and 200 records depending on the day, but NEVER 0 records)
>> is it permissions? it it something else? (btw, my DTS package doesn't
>> need parameters or anything like that)
>> Thanks in advance for all your help,
>> SB-R
>|||Ajay,
here's what the log said: "Executed as user: usr999xxx. ...te Table
[ourDB].[dbo].[tblABC001] Step DTSRun OnFinish: Create Table
[ourDB].[dbo].[tblABC001] Step DTSRun OnStart: Copy Data from tblABC001
to [ourDB].[dbo].[tblABC001] Step DTSRun OnProgress: Copy Data from
tblABC001 to [ourDB].[dbo].[tblABC001] Step; 0 Rows have been transformed or
copied.; PercentComplete = 0; ProgressCount = 0 DTSRun OnFinish: Copy
Data from tblABC001 to [ourDB].[dbo].[tblABC001] Step DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnProgress:
DTSStep_DTSDataPumpTask_1; 0 Rows have been transformed or copied.;
PercentComplete = 0; ProgressCount = 0 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun OnStart: Create Table
[ourDB].[dbo].[tblABC002] Step DTSRun OnFinish: Create Table
[ourDB].[dbo].[tblABC002] Step D... Process Exit Code 0. The step
succeeded."
so, 0 records. No good...
"Ajay Rengunthwar" <ajudba@.gmail.com> wrote in message
news:85670f10-d8fd-4f3c-b2b1-37c1f8baa466@.s12g2000prg.googlegroups.com...
On Jan 18, 7:13 am, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> > So, my question is, what could be causing that a DTS package running
> > from
> > a Job Task transfers no records and when I run it directly from Data
> > Transformation Services --> Local Packages, it transfers the right data
> > (between 180 and 200 records depending on the day, but NEVER 0 records)
> > is it permissions? it it something else? (btw, my DTS package doesn't
> > need
> > parameters or anything like that)
> If you run the package remotely using Enterprise Manager, it runs on the
> client machine. However, the package runs on the sever when launched via a
> job. Perhaps this is reason for the difference.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "segis bata" <segisb...@.hotmail.com> wrote in message
> news:u6xcRrWWIHA.4896@.TK2MSFTNGP06.phx.gbl...
>
> > Hello all,
> > I have this situation, I have a DTS package consisting of a series of
> > data
> > transformations and data transfers from our server to remote servers. If
> > I
> > execute the DTS from the Data Transformation Services --> Local
> > Packages,
> > it runs smoothly without any problems. But, when I schedule that same
> > DTS
> > package and have SQL (Server 2000) run it as a task on a daily manner it
> > runs "successfully" (according to SQL) but when I see the job history I
> > see that no records were transferred even though it says "successfully"
> > (which, obviously, isn't)
> > So, my question is, what could be causing that a DTS package running
> > from
> > a Job Task transfers no records and when I run it directly from Data
> > Transformation Services --> Local Packages, it transfers the right data
> > (between 180 and 200 records depending on the day, but NEVER 0 records)
> > is it permissions? it it something else? (btw, my DTS package doesn't
> > need
> > parameters or anything like that)
> > Thanks in advance for all your help,
> > SB-R- Hide quoted text -
> - Show quoted text -
Please check the log of the job and the step details.
Thanks
Ajay Rengunthwar
MCDBA,MCTS|||Mark,
can you give me a couple of examples on how to do that lag? thanks!
SB-R
"Mark T" <vwttracer@.hotmail.com> wrote in message
news:69674cef-811a-4cbd-b5eb-533ad36db918@.q39g2000hsf.googlegroups.com...
> On Jan 17, 5:38 pm, "segis bata" <segisb...@.hotmail.com> wrote:
>> Hello all,
>> I have this situation, I have a DTS package consisting of a series of
>> data
>> transformations and data transfers from our server to remote servers. If
>> I
>> execute the DTS from the Data Transformation Services --> Local Packages,
>> it
>> runs smoothly without any problems. But, when I schedule that same DTS
>> package and have SQL (Server 2000) run it as a task on a daily manner it
>> runs "successfully" (according to SQL) but when I see the job history I
>> see
>> that no records were transferred even though it says "successfully"
>> (which,
>> obviously, isn't)
>> So, my question is, what could be causing that a DTS package running from
>> a
>> Job Task transfers no records and when I run it directly from Data
>> Transformation Services --> Local Packages, it transfers the right data
>> (between 180 and 200 records depending on the day, but NEVER 0 records)
>> is it permissions? it it something else? (btw, my DTS package doesn't
>> need
>> parameters or anything like that)
>> Thanks in advance for all your help,
>> SB-R
> I came across a similar problem when I was moving data from sever A to
> server B. I was able to fix this by adding a lag between when the data
> was available in server A and when it got pulled by the job into
> server B. This may or may not solve your problem but its worth a try.|||I believe that when you manually run a dts package the permissions and
rights of your remote desktop login are being used but when the dts job is
executed by a job it uses whatever the permissions of the sqlagent are
(which may be the system account of the server the contains the dts
package).
--
Sincerely,
John K
Knowledgy Consulting
www.knowledgy.org
Atlanta's Business Intelligence and Data Warehouse Experts
"segis bata" <segisbata@.hotmail.com> wrote in message
news:u6xcRrWWIHA.4896@.TK2MSFTNGP06.phx.gbl...
> Hello all,
> I have this situation, I have a DTS package consisting of a series of data
> transformations and data transfers from our server to remote servers. If I
> execute the DTS from the Data Transformation Services --> Local Packages,
> it runs smoothly without any problems. But, when I schedule that same DTS
> package and have SQL (Server 2000) run it as a task on a daily manner it
> runs "successfully" (according to SQL) but when I see the job history I
> see that no records were transferred even though it says "successfully"
> (which, obviously, isn't)
> So, my question is, what could be causing that a DTS package running from
> a Job Task transfers no records and when I run it directly from Data
> Transformation Services --> Local Packages, it transfers the right data
> (between 180 and 200 records depending on the day, but NEVER 0 records)
> is it permissions? it it something else? (btw, my DTS package doesn't need
> parameters or anything like that)
> Thanks in advance for all your help,
> SB-R
>