SEO

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: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. IfI
> > 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

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...
>> 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.

On Jan 18, 2:54 pm, "segis bata" <segisb...@hotmail.com> wrote:
> 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...
>
> > 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.

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
>