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
>
|||On Jan 18, 7:13Xam, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
>
> If you run the package remotely using Enterprise Manager, it runs on the
> client machine. XHowever, the package runs on the sever when launched via a
> job. XPerhaps 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...
>
>
>
>
> - Show quoted text -
Please check the log of the job and the step details.
Thanks
Ajay Rengunthwar
MCDBA,MCTS
|||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.
|||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...
> 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...
>
|||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:
>
> 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...
>
>
>
>
> - 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:
> 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.
|||On Jan 18, 2:54 pm, "segis bata" <segisb...@.hotmail.com> wrote:[vbcol=seagreen]
> Mark,
> can you give me a couple of examples on how to do that lag? thanks!
> SB-R
> "Mark T" <vwttra...@.hotmail.com> wrote in message
> news:69674cef-811a-4cbd-b5eb-533ad36db918@.q39g2000hsf.googlegroups.com...
>
>
>
Something like this in a different step will work: WAITFOR DELAY
'0:03'
|||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
>