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 manually. Show all posts
Showing posts with label manually. Show all posts
Thursday, March 29, 2012
DTS Scheduling Issue
I have a DTS package that runs without any problems when I run it manually, however, when I schedule the package at a certain time no output is produced.
I have checked the Agent and it says the job is executing, however, it seems to have hung.
The log tells me DTSStep CreateProcessTask3 started at the correct time but there is nothing after that.
How do I check this? or find out what is causing it to hang?
Thanks!Try running profiler at the time the job start starts executing to see how far the job get's. What user account is the job run under?|||Hey tr1na,
Many thanks for your response it is appreciated. SQL server is new to me so apologies for the lack of knowledge
I have never run profiler before, I have had a look and it doesnt look that simple to run and also, I couldnt see where I could call my DTS package.
Anyway, in the package log file it states DTSStep CreateProcessTask3 started at 07.32 and well thats it! I have read somewhere that this could be a security issue and I should be logged in as SYSADMIN? However, I am currently Remote Desktop to the SERVER, login through NT, the package owner is my USER ID, how can I log into EM as SYSADMIN and try scheduling a DTS package under SYSADMIN, do you think this could be the problem?
I am not sure how to check the LOGIN ID.|||To view which user the job is running as right click the job and select "view job history" then tick "show step details". You should then see
"Executed as user: SYD\blah" etc|||Thanks!
Well, unfortunately, the job does not complete because it hangs so there is no job history, however, when I cancelled to job it tells me that the job was cancelled prior to completion by my "USER ID". So I suspect this is the USER ID being used to start the job?
Perhaps I need someway to remote desktop but login as SYSADMIN?|||when you run the job manually are you logged in as your userid. if so check the job runs under your user id.|||there are differences of environment when u execute a dts by right-clicking and thru jobs. for example if u refer a server name as (local) in connection, during right-clicked execution the database will be selected from the server where EM is installed but during execution thru job it will connect to server from where the job is running. similarly any disk file path/reference to COM component also changes with the execution mode. and this is often the source of problem reported by u...|||OK. The job did not run manually when logged in by me, it does run manually when logged in to the database as "sa"
upalsen:
Thank you for your response, it is appreciated, how can I get round this problem, is it a matter of logging in as SYSADMIN, hopefully it is as something as simple as this?|||there r a few settings under properties context menu (right-click) of SQL Server Agent. by default users other than sysadmin r not allowed to run cmdexec etc command. check the Job-System tab and Connection tab for such user specific issues. also a job can be saved under user other than sa. i am not sure exactly where your job is hanging but u can try the above combinations and check the result.|||Thank you.
OK. Well the check box is checked "Only users with SysAdmin priviledges can run CmdExec etc..."
So I uncheck the box, then it asks me to "Enter the proxy account SQL agent will use to run jobs owned by non-administrators"
Does this mean I need to create a "dummy" account and does it need to be a member of the sysadmin group or have specific priviledges?
Thank you.|||u give the name & password of the local m/c administrator and enter the machine name as domain. if u r having a domain controller in your network u can specify the details of a domain account as well.
I have checked the Agent and it says the job is executing, however, it seems to have hung.
The log tells me DTSStep CreateProcessTask3 started at the correct time but there is nothing after that.
How do I check this? or find out what is causing it to hang?
Thanks!Try running profiler at the time the job start starts executing to see how far the job get's. What user account is the job run under?|||Hey tr1na,
Many thanks for your response it is appreciated. SQL server is new to me so apologies for the lack of knowledge
I have never run profiler before, I have had a look and it doesnt look that simple to run and also, I couldnt see where I could call my DTS package.
Anyway, in the package log file it states DTSStep CreateProcessTask3 started at 07.32 and well thats it! I have read somewhere that this could be a security issue and I should be logged in as SYSADMIN? However, I am currently Remote Desktop to the SERVER, login through NT, the package owner is my USER ID, how can I log into EM as SYSADMIN and try scheduling a DTS package under SYSADMIN, do you think this could be the problem?
I am not sure how to check the LOGIN ID.|||To view which user the job is running as right click the job and select "view job history" then tick "show step details". You should then see
"Executed as user: SYD\blah" etc|||Thanks!
Well, unfortunately, the job does not complete because it hangs so there is no job history, however, when I cancelled to job it tells me that the job was cancelled prior to completion by my "USER ID". So I suspect this is the USER ID being used to start the job?
Perhaps I need someway to remote desktop but login as SYSADMIN?|||when you run the job manually are you logged in as your userid. if so check the job runs under your user id.|||there are differences of environment when u execute a dts by right-clicking and thru jobs. for example if u refer a server name as (local) in connection, during right-clicked execution the database will be selected from the server where EM is installed but during execution thru job it will connect to server from where the job is running. similarly any disk file path/reference to COM component also changes with the execution mode. and this is often the source of problem reported by u...|||OK. The job did not run manually when logged in by me, it does run manually when logged in to the database as "sa"
upalsen:
Thank you for your response, it is appreciated, how can I get round this problem, is it a matter of logging in as SYSADMIN, hopefully it is as something as simple as this?|||there r a few settings under properties context menu (right-click) of SQL Server Agent. by default users other than sysadmin r not allowed to run cmdexec etc command. check the Job-System tab and Connection tab for such user specific issues. also a job can be saved under user other than sa. i am not sure exactly where your job is hanging but u can try the above combinations and check the result.|||Thank you.
OK. Well the check box is checked "Only users with SysAdmin priviledges can run CmdExec etc..."
So I uncheck the box, then it asks me to "Enter the proxy account SQL agent will use to run jobs owned by non-administrators"
Does this mean I need to create a "dummy" account and does it need to be a member of the sysadmin group or have specific priviledges?
Thank you.|||u give the name & password of the local m/c administrator and enter the machine name as domain. if u r having a domain controller in your network u can specify the details of a domain account as well.
DTS scheduling and float problem
Hi
I have some DTS-package that import textfiles. When I run
then manually everything works fine, but when i schedule
them the fields containing floatvalues is becomming
integers instead. Any one who know whats the problem is?
/AnettHi,
Inside the data Transformation task, change the destination field to Float
data type inside the package.
Thanks
Hari
SQL Server MVP
"Anett" <anonymous@.discussions.microsoft.com> wrote in message
news:051c01c54019$a2377270$a401280a@.phx.gbl...
> Hi
> I have some DTS-package that import textfiles. When I run
> then manually everything works fine, but when i schedule
> them the fields containing floatvalues is becomming
> integers instead. Any one who know whats the problem is?
> /Anett
>
I have some DTS-package that import textfiles. When I run
then manually everything works fine, but when i schedule
them the fields containing floatvalues is becomming
integers instead. Any one who know whats the problem is?
/AnettHi,
Inside the data Transformation task, change the destination field to Float
data type inside the package.
Thanks
Hari
SQL Server MVP
"Anett" <anonymous@.discussions.microsoft.com> wrote in message
news:051c01c54019$a2377270$a401280a@.phx.gbl...
> Hi
> I have some DTS-package that import textfiles. When I run
> then manually everything works fine, but when i schedule
> them the fields containing floatvalues is becomming
> integers instead. Any one who know whats the problem is?
> /Anett
>
DTS scheduling and float problem
Hi
I have some DTS-package that import textfiles. When I run
then manually everything works fine, but when i schedule
them the fields containing floatvalues is becomming
integers instead. Any one who know whats the problem is?
/Anett
Hi,
Inside the data Transformation task, change the destination field to Float
data type inside the package.
Thanks
Hari
SQL Server MVP
"Anett" <anonymous@.discussions.microsoft.com> wrote in message
news:051c01c54019$a2377270$a401280a@.phx.gbl...
> Hi
> I have some DTS-package that import textfiles. When I run
> then manually everything works fine, but when i schedule
> them the fields containing floatvalues is becomming
> integers instead. Any one who know whats the problem is?
> /Anett
>
I have some DTS-package that import textfiles. When I run
then manually everything works fine, but when i schedule
them the fields containing floatvalues is becomming
integers instead. Any one who know whats the problem is?
/Anett
Hi,
Inside the data Transformation task, change the destination field to Float
data type inside the package.
Thanks
Hari
SQL Server MVP
"Anett" <anonymous@.discussions.microsoft.com> wrote in message
news:051c01c54019$a2377270$a401280a@.phx.gbl...
> Hi
> I have some DTS-package that import textfiles. When I run
> then manually everything works fine, but when i schedule
> them the fields containing floatvalues is becomming
> integers instead. Any one who know whats the problem is?
> /Anett
>
DTS scheduling and float problem
Hi
I have some DTS-package that import textfiles. When I run
then manually everything works fine, but when i schedule
them the fields containing floatvalues is becomming
integers instead. Any one who know whats the problem is?
/AnettHi,
Inside the data Transformation task, change the destination field to Float
data type inside the package.
Thanks
Hari
SQL Server MVP
"Anett" <anonymous@.discussions.microsoft.com> wrote in message
news:051c01c54019$a2377270$a401280a@.phx.gbl...
> Hi
> I have some DTS-package that import textfiles. When I run
> then manually everything works fine, but when i schedule
> them the fields containing floatvalues is becomming
> integers instead. Any one who know whats the problem is?
> /Anett
>sqlsql
I have some DTS-package that import textfiles. When I run
then manually everything works fine, but when i schedule
them the fields containing floatvalues is becomming
integers instead. Any one who know whats the problem is?
/AnettHi,
Inside the data Transformation task, change the destination field to Float
data type inside the package.
Thanks
Hari
SQL Server MVP
"Anett" <anonymous@.discussions.microsoft.com> wrote in message
news:051c01c54019$a2377270$a401280a@.phx.gbl...
> Hi
> I have some DTS-package that import textfiles. When I run
> then manually everything works fine, but when i schedule
> them the fields containing floatvalues is becomming
> integers instead. Any one who know whats the problem is?
> /Anett
>sqlsql
DTS Scheduled Job
I am getting the following error whenever I execute
the job via sql server job agent. When I execute
the job package manually via dts the result is good.
Should I mount the physical drive to the main server?
Executed as user: card\sql. DTSRun: Loading... DTSRun:
Executing...
DTSRun OnStart: Copy Data from Results to S:\test.xls
Step DTSRun OnError: Copy Data from Results to
S:\test.xls Step,
Error = -2147467259 (80004005)
Error string: Error opening datafile: The system cannot
find the path specified.
Error source: Microsoft Data Transformation Services Flat
File Rowset Provider
Help file: DTSFFile.hlp Help context: 0
Error Detail Records: Error: 3 (3); Provider Error:
3 (3)
Error string: Error opening datafile: The system cannot
find the path specified.
Error source: Microsoft Data Transformation Services Flat
File Rowset Provider
Help file: DTSFFile.hlp Help context: 0
DTSRun OnFinish: Copy Data from Results to S:\test.xls
Step
DTSRun: Package execution complete. Process Exit Code
1.
The step failed.DTS is a client tool and thus when you run it from EM it's running
on your pc. If the server doesn't have an S drive then you'll get this
error. It's best to use UNC paths to avoid ambiguity. Also check out
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)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"L Dominguez" <ldominguez@.cardionet.com> wrote in message
news:02af01c34730$9a2823b0$a601280a@.phx.gbl...
I am getting the following error whenever I execute
the job via sql server job agent. When I execute
the job package manually via dts the result is good.
Should I mount the physical drive to the main server?
Executed as user: card\sql. DTSRun: Loading... DTSRun:
Executing...
DTSRun OnStart: Copy Data from Results to S:\test.xls
Step DTSRun OnError: Copy Data from Results to
S:\test.xls Step,
Error = -2147467259 (80004005)
Error string: Error opening datafile: The system cannot
find the path specified.
Error source: Microsoft Data Transformation Services Flat
File Rowset Provider
Help file: DTSFFile.hlp Help context: 0
Error Detail Records: Error: 3 (3); Provider Error:
3 (3)
Error string: Error opening datafile: The system cannot
find the path specified.
Error source: Microsoft Data Transformation Services Flat
File Rowset Provider
Help file: DTSFFile.hlp Help context: 0
DTSRun OnFinish: Copy Data from Results to S:\test.xls
Step
DTSRun: Package execution complete. Process Exit Code
1.
The step failed.
the job via sql server job agent. When I execute
the job package manually via dts the result is good.
Should I mount the physical drive to the main server?
Executed as user: card\sql. DTSRun: Loading... DTSRun:
Executing...
DTSRun OnStart: Copy Data from Results to S:\test.xls
Step DTSRun OnError: Copy Data from Results to
S:\test.xls Step,
Error = -2147467259 (80004005)
Error string: Error opening datafile: The system cannot
find the path specified.
Error source: Microsoft Data Transformation Services Flat
File Rowset Provider
Help file: DTSFFile.hlp Help context: 0
Error Detail Records: Error: 3 (3); Provider Error:
3 (3)
Error string: Error opening datafile: The system cannot
find the path specified.
Error source: Microsoft Data Transformation Services Flat
File Rowset Provider
Help file: DTSFFile.hlp Help context: 0
DTSRun OnFinish: Copy Data from Results to S:\test.xls
Step
DTSRun: Package execution complete. Process Exit Code
1.
The step failed.DTS is a client tool and thus when you run it from EM it's running
on your pc. If the server doesn't have an S drive then you'll get this
error. It's best to use UNC paths to avoid ambiguity. Also check out
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)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"L Dominguez" <ldominguez@.cardionet.com> wrote in message
news:02af01c34730$9a2823b0$a601280a@.phx.gbl...
I am getting the following error whenever I execute
the job via sql server job agent. When I execute
the job package manually via dts the result is good.
Should I mount the physical drive to the main server?
Executed as user: card\sql. DTSRun: Loading... DTSRun:
Executing...
DTSRun OnStart: Copy Data from Results to S:\test.xls
Step DTSRun OnError: Copy Data from Results to
S:\test.xls Step,
Error = -2147467259 (80004005)
Error string: Error opening datafile: The system cannot
find the path specified.
Error source: Microsoft Data Transformation Services Flat
File Rowset Provider
Help file: DTSFFile.hlp Help context: 0
Error Detail Records: Error: 3 (3); Provider Error:
3 (3)
Error string: Error opening datafile: The system cannot
find the path specified.
Error source: Microsoft Data Transformation Services Flat
File Rowset Provider
Help file: DTSFFile.hlp Help context: 0
DTSRun OnFinish: Copy Data from Results to S:\test.xls
Step
DTSRun: Package execution complete. Process Exit Code
1.
The step failed.
DTS runs OK, but not scheduled job
Hi,
When I run DTS manually, it works fine. But when I run the scheduled job, it
failes.
The error said cannot find a file specified. It imports Excel file to
SQL2000 Server database. I set same domain user id for DTS creater and Agent
executer and job owner.
I read several articles same problem like this, but I haven't get solution...
Thank you,
Masako
Where is located the EXCEL File?
It should be located on server and not on the your workstation.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> Hi,
> When I run DTS manually, it works fine. But when I run the scheduled job,
it
> failes.
> The error said cannot find a file specified. It imports Excel file to
> SQL2000 Server database. I set same domain user id for DTS creater and
Agent
> executer and job owner.
> I read several articles same problem like this, but I haven't get
solution...
> --
> Thank you,
|||Hi Uri,
Does it have to? The Excel file is located on another server.
I had no problem like this job flow previous SQL Server. We used to Windows
NT server + SQL7, now new server is Windows2000 english version + SQL2000
Japanese version.
"Uri Dimant" wrote:
> Masako
> Where is located the EXCEL File?
> It should be located on server and not on the your workstation.
>
> "Masako" <Masako@.discussions.microsoft.com> wrote in message
> news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> it
> Agent
> solution...
>
>
|||Maskao
Make sure that SQL Server Agent is running under Domain Account not a Local
account.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:CC48C0DC-A9F9-4E3A-8277-F6C55BF6C588@.microsoft.com...
> Hi Uri,
> Does it have to? The Excel file is located on another server.
> I had no problem like this job flow previous SQL Server. We used to
Windows[vbcol=seagreen]
> NT server + SQL7, now new server is Windows2000 english version + SQL2000
> Japanese version.
> "Uri Dimant" wrote:
job,[vbcol=seagreen]
When I run DTS manually, it works fine. But when I run the scheduled job, it
failes.
The error said cannot find a file specified. It imports Excel file to
SQL2000 Server database. I set same domain user id for DTS creater and Agent
executer and job owner.
I read several articles same problem like this, but I haven't get solution...
Thank you,
Masako
Where is located the EXCEL File?
It should be located on server and not on the your workstation.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> Hi,
> When I run DTS manually, it works fine. But when I run the scheduled job,
it
> failes.
> The error said cannot find a file specified. It imports Excel file to
> SQL2000 Server database. I set same domain user id for DTS creater and
Agent
> executer and job owner.
> I read several articles same problem like this, but I haven't get
solution...
> --
> Thank you,
|||Hi Uri,
Does it have to? The Excel file is located on another server.
I had no problem like this job flow previous SQL Server. We used to Windows
NT server + SQL7, now new server is Windows2000 english version + SQL2000
Japanese version.
"Uri Dimant" wrote:
> Masako
> Where is located the EXCEL File?
> It should be located on server and not on the your workstation.
>
> "Masako" <Masako@.discussions.microsoft.com> wrote in message
> news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> it
> Agent
> solution...
>
>
|||Maskao
Make sure that SQL Server Agent is running under Domain Account not a Local
account.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:CC48C0DC-A9F9-4E3A-8277-F6C55BF6C588@.microsoft.com...
> Hi Uri,
> Does it have to? The Excel file is located on another server.
> I had no problem like this job flow previous SQL Server. We used to
Windows[vbcol=seagreen]
> NT server + SQL7, now new server is Windows2000 english version + SQL2000
> Japanese version.
> "Uri Dimant" wrote:
job,[vbcol=seagreen]
DTS runs OK, but not scheduled job
Hi,
When I run DTS manually, it works fine. But when I run the scheduled job, it
failes.
The error said cannot find a file specified. It imports Excel file to
SQL2000 Server database. I set same domain user id for DTS creater and Agent
executer and job owner.
I read several articles same problem like this, but I haven't get solution..
.
Thank you,Masako
Where is located the EXCEL File?
It should be located on server and not on the your workstation.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> Hi,
> When I run DTS manually, it works fine. But when I run the scheduled job,
it
> failes.
> The error said cannot find a file specified. It imports Excel file to
> SQL2000 Server database. I set same domain user id for DTS creater and
Agent
> executer and job owner.
> I read several articles same problem like this, but I haven't get
solution...
> --
> Thank you,|||Hi Uri,
Does it have to? The Excel file is located on another server.
I had no problem like this job flow previous SQL Server. We used to Windows
NT server + SQL7, now new server is Windows2000 english version + SQL2000
Japanese version.
"Uri Dimant" wrote:
> Masako
> Where is located the EXCEL File?
> It should be located on server and not on the your workstation.
>
> "Masako" <Masako@.discussions.microsoft.com> wrote in message
> news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> it
> Agent
> solution...
>
>|||Maskao
Make sure that SQL Server Agent is running under Domain Account not a Local
account.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:CC48C0DC-A9F9-4E3A-8277-F6C55BF6C588@.microsoft.com...
> Hi Uri,
> Does it have to? The Excel file is located on another server.
> I had no problem like this job flow previous SQL Server. We used to
Windows[vbcol=seagreen]
> NT server + SQL7, now new server is Windows2000 english version + SQL2000
> Japanese version.
> "Uri Dimant" wrote:
>
job,[vbcol=seagreen]
When I run DTS manually, it works fine. But when I run the scheduled job, it
failes.
The error said cannot find a file specified. It imports Excel file to
SQL2000 Server database. I set same domain user id for DTS creater and Agent
executer and job owner.
I read several articles same problem like this, but I haven't get solution..
.
Thank you,Masako
Where is located the EXCEL File?
It should be located on server and not on the your workstation.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> Hi,
> When I run DTS manually, it works fine. But when I run the scheduled job,
it
> failes.
> The error said cannot find a file specified. It imports Excel file to
> SQL2000 Server database. I set same domain user id for DTS creater and
Agent
> executer and job owner.
> I read several articles same problem like this, but I haven't get
solution...
> --
> Thank you,|||Hi Uri,
Does it have to? The Excel file is located on another server.
I had no problem like this job flow previous SQL Server. We used to Windows
NT server + SQL7, now new server is Windows2000 english version + SQL2000
Japanese version.
"Uri Dimant" wrote:
> Masako
> Where is located the EXCEL File?
> It should be located on server and not on the your workstation.
>
> "Masako" <Masako@.discussions.microsoft.com> wrote in message
> news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> it
> Agent
> solution...
>
>|||Maskao
Make sure that SQL Server Agent is running under Domain Account not a Local
account.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:CC48C0DC-A9F9-4E3A-8277-F6C55BF6C588@.microsoft.com...
> Hi Uri,
> Does it have to? The Excel file is located on another server.
> I had no problem like this job flow previous SQL Server. We used to
Windows[vbcol=seagreen]
> NT server + SQL7, now new server is Windows2000 english version + SQL2000
> Japanese version.
> "Uri Dimant" wrote:
>
job,[vbcol=seagreen]
DTS runs OK, but not scheduled job
Hi,
When I run DTS manually, it works fine. But when I run the scheduled job, it
failes.
The error said cannot find a file specified. It imports Excel file to
SQL2000 Server database. I set same domain user id for DTS creater and Agent
executer and job owner.
I read several articles same problem like this, but I haven't get solution...
--
Thank you,Masako
Where is located the EXCEL File?
It should be located on server and not on the your workstation.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> Hi,
> When I run DTS manually, it works fine. But when I run the scheduled job,
it
> failes.
> The error said cannot find a file specified. It imports Excel file to
> SQL2000 Server database. I set same domain user id for DTS creater and
Agent
> executer and job owner.
> I read several articles same problem like this, but I haven't get
solution...
> --
> Thank you,|||Hi Uri,
Does it have to? The Excel file is located on another server.
I had no problem like this job flow previous SQL Server. We used to Windows
NT server + SQL7, now new server is Windows2000 english version + SQL2000
Japanese version.
"Uri Dimant" wrote:
> Masako
> Where is located the EXCEL File?
> It should be located on server and not on the your workstation.
>
> "Masako" <Masako@.discussions.microsoft.com> wrote in message
> news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> > Hi,
> >
> > When I run DTS manually, it works fine. But when I run the scheduled job,
> it
> > failes.
> > The error said cannot find a file specified. It imports Excel file to
> > SQL2000 Server database. I set same domain user id for DTS creater and
> Agent
> > executer and job owner.
> >
> > I read several articles same problem like this, but I haven't get
> solution...
> >
> > --
> > Thank you,
>
>|||Maskao
Make sure that SQL Server Agent is running under Domain Account not a Local
account.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:CC48C0DC-A9F9-4E3A-8277-F6C55BF6C588@.microsoft.com...
> Hi Uri,
> Does it have to? The Excel file is located on another server.
> I had no problem like this job flow previous SQL Server. We used to
Windows
> NT server + SQL7, now new server is Windows2000 english version + SQL2000
> Japanese version.
> "Uri Dimant" wrote:
> > Masako
> > Where is located the EXCEL File?
> > It should be located on server and not on the your workstation.
> >
> >
> > "Masako" <Masako@.discussions.microsoft.com> wrote in message
> > news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> > > Hi,
> > >
> > > When I run DTS manually, it works fine. But when I run the scheduled
job,
> > it
> > > failes.
> > > The error said cannot find a file specified. It imports Excel file to
> > > SQL2000 Server database. I set same domain user id for DTS creater and
> > Agent
> > > executer and job owner.
> > >
> > > I read several articles same problem like this, but I haven't get
> > solution...
> > >
> > > --
> > > Thank you,
> >
> >
> >
When I run DTS manually, it works fine. But when I run the scheduled job, it
failes.
The error said cannot find a file specified. It imports Excel file to
SQL2000 Server database. I set same domain user id for DTS creater and Agent
executer and job owner.
I read several articles same problem like this, but I haven't get solution...
--
Thank you,Masako
Where is located the EXCEL File?
It should be located on server and not on the your workstation.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> Hi,
> When I run DTS manually, it works fine. But when I run the scheduled job,
it
> failes.
> The error said cannot find a file specified. It imports Excel file to
> SQL2000 Server database. I set same domain user id for DTS creater and
Agent
> executer and job owner.
> I read several articles same problem like this, but I haven't get
solution...
> --
> Thank you,|||Hi Uri,
Does it have to? The Excel file is located on another server.
I had no problem like this job flow previous SQL Server. We used to Windows
NT server + SQL7, now new server is Windows2000 english version + SQL2000
Japanese version.
"Uri Dimant" wrote:
> Masako
> Where is located the EXCEL File?
> It should be located on server and not on the your workstation.
>
> "Masako" <Masako@.discussions.microsoft.com> wrote in message
> news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> > Hi,
> >
> > When I run DTS manually, it works fine. But when I run the scheduled job,
> it
> > failes.
> > The error said cannot find a file specified. It imports Excel file to
> > SQL2000 Server database. I set same domain user id for DTS creater and
> Agent
> > executer and job owner.
> >
> > I read several articles same problem like this, but I haven't get
> solution...
> >
> > --
> > Thank you,
>
>|||Maskao
Make sure that SQL Server Agent is running under Domain Account not a Local
account.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:CC48C0DC-A9F9-4E3A-8277-F6C55BF6C588@.microsoft.com...
> Hi Uri,
> Does it have to? The Excel file is located on another server.
> I had no problem like this job flow previous SQL Server. We used to
Windows
> NT server + SQL7, now new server is Windows2000 english version + SQL2000
> Japanese version.
> "Uri Dimant" wrote:
> > Masako
> > Where is located the EXCEL File?
> > It should be located on server and not on the your workstation.
> >
> >
> > "Masako" <Masako@.discussions.microsoft.com> wrote in message
> > news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> > > Hi,
> > >
> > > When I run DTS manually, it works fine. But when I run the scheduled
job,
> > it
> > > failes.
> > > The error said cannot find a file specified. It imports Excel file to
> > > SQL2000 Server database. I set same domain user id for DTS creater and
> > Agent
> > > executer and job owner.
> > >
> > > I read several articles same problem like this, but I haven't get
> > solution...
> > >
> > > --
> > > Thank you,
> >
> >
> >
DTS runs manually, fails as scheduled job
I've mined countless threads all over the Net where people are having my problem, but none of the solutions I've seen seem to apply.
When I run a DTS package from Enterprise Manager, everything works great. When I schedule the same package as a job, it fails immediately and reports this error:
Executed as user: SERVER-NAME\SYSTEM. DTSRun: Loading... Error: -2147221005 (800401F3); Provider Error: 0 (0) Error string: Invalid class string Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 713. Process Exit Code 1. The step failed.
My hunch is the SERVER-NAME\SYSTEM.DTSRun account doesn't have sufficient privileges, but I can't find how/where to fix that.
Help, please.
Thanks,
Doug
Checkout the following links
http://www.sqlmag.com/articles/index.cfm?articleid=46723& - Security in SSIS
http://support.microsoft.com/?kbid=918760 - An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step
Thanks,
Loonysan
"Invalid class string" is not strictly a SQL Server error in DTS. Essentially it means in cannot find the CLSID for a required object when reading the package. This error can also occur when loading a package that contains a CustomTask which has not been registered on the current machine. (http://www.databasejournal.com/features/mssql/article.php/1461391)
PRB: "Invalid class string" error message when opening DTS package
(http://support.microsoft.com/?scid=kb%3Ben-us%3B288910&x=15&y=16)
The top two hits I got from Google, both say pretty much the same thing. Something is missing from your server, which causes the "Invalid class string", or "I cannot find your task".
FYI, general DTS info on jobs-
INF: How to Run a DTS Package as a Scheduled Job
(http://support.microsoft.com/kb/269074/en-us)
DTS runs great... but not from the job list
I have a DTS package that updates a database from a text file and it works
great when I run it manually from the DTS designer. However, I right click
on it and set it up to run automatically and it will not run. I went to
view the history of the job messages and it appears that the following is
the error for this task:
Executed as user: MAHC_SQL2\SYSTEM. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun
OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1,
Error = -2147467259 (80004005) Error string: Error opening datafile:
The network name cannot be found. Error source: Microsoft Data
Transformation Services Flat File Rowset Provider Help file:
DTSFFile.hlp Help context: 0 Error Detail Records: Error:
67 (43); Provider Error: 67 (43) Error string: Error opening
datafile: The network name cannot be found. Error source: Microsoft
Data Transformation Services Flat File Rowset Provider Help file:
DTSFFile.hlp Help context: 0 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
Can anyone give me any clues as to what the problem could be?
Schoo
Have a look at this KB that details the common reasons a scheduled DTS
package will fail
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
"Schoo" <scott.schuman@.nospam.ma-hc.com> wrote in message
news:%23enkcwUrEHA.452@.TK2MSFTNGP09.phx.gbl...
>I have a DTS package that updates a database from a text file and it works
> great when I run it manually from the DTS designer. However, I right
> click
> on it and set it up to run automatically and it will not run. I went to
> view the history of the job messages and it appears that the following is
> the error for this task:
> Executed as user: MAHC_SQL2\SYSTEM. DTSRun: Loading... DTSRun:
> Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun
> OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1,
> Error = -2147467259 (80004005) Error string: Error opening datafile:
> The network name cannot be found. Error source: Microsoft Data
> Transformation Services Flat File Rowset Provider Help file:
> DTSFFile.hlp Help context: 0 Error Detail Records: Error:
> 67 (43); Provider Error: 67 (43) Error string: Error opening
> datafile: The network name cannot be found. Error source:
> Microsoft
> Data Transformation Services Flat File Rowset Provider Help file:
> DTSFFile.hlp Help context: 0 DTSRun OnFinish:
> DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process
> Exit Code 1. The step failed.
> Can anyone give me any clues as to what the problem could be?
> Schoo
>
|||Hi Schoo,
Thanks for you post and I would love to show my gratidue for MVP Jasper
Smith's providing that great KB:Q269074
From your Error Message, I foudn the error was "The network name cannot be
found" so that please check whether there is anything wrong with your
source and target of database. Make sure they are "online", also please
check the permission issue. It seems strange and I need more information to
make further troubleshooting.
Would you please generate a small DTS package and see whether it works
fine? To get a better picture of the problem we would like to reproduct the
issue on our end. To expedite the process, please provide us the detailed
reproduce steps.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Schoo,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
great when I run it manually from the DTS designer. However, I right click
on it and set it up to run automatically and it will not run. I went to
view the history of the job messages and it appears that the following is
the error for this task:
Executed as user: MAHC_SQL2\SYSTEM. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun
OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1,
Error = -2147467259 (80004005) Error string: Error opening datafile:
The network name cannot be found. Error source: Microsoft Data
Transformation Services Flat File Rowset Provider Help file:
DTSFFile.hlp Help context: 0 Error Detail Records: Error:
67 (43); Provider Error: 67 (43) Error string: Error opening
datafile: The network name cannot be found. Error source: Microsoft
Data Transformation Services Flat File Rowset Provider Help file:
DTSFFile.hlp Help context: 0 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
Can anyone give me any clues as to what the problem could be?
Schoo
Have a look at this KB that details the common reasons a scheduled DTS
package will fail
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
"Schoo" <scott.schuman@.nospam.ma-hc.com> wrote in message
news:%23enkcwUrEHA.452@.TK2MSFTNGP09.phx.gbl...
>I have a DTS package that updates a database from a text file and it works
> great when I run it manually from the DTS designer. However, I right
> click
> on it and set it up to run automatically and it will not run. I went to
> view the history of the job messages and it appears that the following is
> the error for this task:
> Executed as user: MAHC_SQL2\SYSTEM. DTSRun: Loading... DTSRun:
> Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun
> OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1,
> Error = -2147467259 (80004005) Error string: Error opening datafile:
> The network name cannot be found. Error source: Microsoft Data
> Transformation Services Flat File Rowset Provider Help file:
> DTSFFile.hlp Help context: 0 Error Detail Records: Error:
> 67 (43); Provider Error: 67 (43) Error string: Error opening
> datafile: The network name cannot be found. Error source:
> Microsoft
> Data Transformation Services Flat File Rowset Provider Help file:
> DTSFFile.hlp Help context: 0 DTSRun OnFinish:
> DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process
> Exit Code 1. The step failed.
> Can anyone give me any clues as to what the problem could be?
> Schoo
>
|||Hi Schoo,
Thanks for you post and I would love to show my gratidue for MVP Jasper
Smith's providing that great KB:Q269074
From your Error Message, I foudn the error was "The network name cannot be
found" so that please check whether there is anything wrong with your
source and target of database. Make sure they are "online", also please
check the permission issue. It seems strange and I need more information to
make further troubleshooting.
Would you please generate a small DTS package and see whether it works
fine? To get a better picture of the problem we would like to reproduct the
issue on our end. To expedite the process, please provide us the detailed
reproduce steps.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Schoo,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
Sunday, March 25, 2012
DTS Pkg Job error
I recently installed a new SQL Server and am having problems scheduling my DTS packages. They will work fine if I manually execute them, but none of them will fire from a job.
Regardless of the DTS package scheduled it fails with the following error:
Executed as user: STANDREWS\SYSTEM. The process could not be created for step 1
of job 0x8D49E4684F14CB49BFC9223F4E47FC16 (reason: The system cannot find the file
specified). The step failed.
I have tried all kinds of stuff to no avail.What does step 1 look like? Did you try specifying the entire path for dtsrun.exe?
Terri|||when they fire from a job, they are firing under the security context of the account that the SQL Agent uses.
Therefore i would conclude that your account has access to something that the SQL Agent account does not.
you could set the SQL Agent to use a specific machine account...|||Yeah, created the job in various fashions, by name, by GUID, etc, etc. I am not certain of the absolute file path though, where is that found?
I will also check the SQL Agent security.|||The absolute file path is the physical path to dtsrun.exe on the SQL Server. On my local SQL Server, e.g., it can be found here: C:\Program Files\Microsoft SQL Server\80\Tools\Binn\dtsrun.exe
I am bringing this up because I have come across this problem in the past.KB 322746 - PRB: Data Transformation Services Jobs May Fail Because the Path for Dtsrun.exe Cannot Be Found addresses this.
Wednesday, March 21, 2012
DTS package with inputs
Hi I am using mssql 2000 and have been performing a data port manually that
consists of running a query on an sql database (using a date range as an
input) and then connecting to an Oracle database and appending a table in the
Oracle database with the data from the SQL query. The SQL query writes the
data to a table and I then use access to connect to this table and the
destination table in the Oracle database and run a simple query inside of
access to append the data into the Oracle table. I would like to create a
dts package that does this but the initial query requires 2 input parameters,
start date and end date, anyhow just wondering if you can still build a dts
package that when run from Enterprise manager would prompt for the date
inputs and then perform the sql query as well as the data append to the
Oracle table.
Thanks.
Paul G
Software engineer.
Hi Paul
You can set global variables on the command line such as (this has wrapped):
DECLARE @.datefrom CHAR(10)
DECLARE @.dateto CHAR(10)
DECLARE @.cmd varchar(2200)
DECLARE @.cmdroot varchar(2000)
DECLARE @.stat int
SET @.cmdroot = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N "' + DB_NAME()
+ ' Monthly Export" '
SELECT@.dateto =
CONVERT(CHAR(10),DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)),121),
@.datefrom =
CONVERT(CHAR(10),DATEADD(m,-1,DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME))),121)
SET @.cmd = @.cmdroot + ' /A "DateFrom":"7"="' + @.datefrom + '" /A
"DateTo":"7"="' + @.dateto + '"'
EXEC @.stat = master..xp_cmdshell @.cmd
SET @.stat = COALESCE(@.stat,@.@.ERROR)
John
"Paul" wrote:
> Hi I am using mssql 2000 and have been performing a data port manually that
> consists of running a query on an sql database (using a date range as an
> input) and then connecting to an Oracle database and appending a table in the
> Oracle database with the data from the SQL query. The SQL query writes the
> data to a table and I then use access to connect to this table and the
> destination table in the Oracle database and run a simple query inside of
> access to append the data into the Oracle table. I would like to create a
> dts package that does this but the initial query requires 2 input parameters,
> start date and end date, anyhow just wondering if you can still build a dts
> package that when run from Enterprise manager would prompt for the date
> inputs and then perform the sql query as well as the data append to the
> Oracle table.
> Thanks.
> --
> Paul G
> Software engineer.
|||Hi thanks for the response, for some reason I did not receive the
notification of replies. So would you place the variable declarations as
well as the provided code in the actual SQL job as the command in a step, or
would this be placed in one of the queries of the dts package?
Paul G
Software engineer.
"John Bell" wrote:
[vbcol=seagreen]
> Hi Paul
> You can set global variables on the command line such as (this has wrapped):
> DECLARE @.datefrom CHAR(10)
> DECLARE @.dateto CHAR(10)
> DECLARE @.cmd varchar(2200)
> DECLARE @.cmdroot varchar(2000)
> DECLARE @.stat int
> SET @.cmdroot = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N "' + DB_NAME()
> + ' Monthly Export" '
> SELECT@.dateto =
> CONVERT(CHAR(10),DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)),121),
> @.datefrom =
> CONVERT(CHAR(10),DATEADD(m,-1,DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME))),121)
> SET @.cmd = @.cmdroot + ' /A "DateFrom":"7"="' + @.datefrom + '" /A
> "DateTo":"7"="' + @.dateto + '"'
> EXEC @.stat = master..xp_cmdshell @.cmd
> SET @.stat = COALESCE(@.stat,@.@.ERROR)
> John
> "Paul" wrote:
|||Hi Paul
The values would need to be global variables and to assign them withing the
job would require an activeX task. It is easier and probably a more flexible
design if you have a job that you pass the dates required to it. Then if at
some point you need a different date range or run it manually then it is easy
to do.
I would put the code into a stored procedure and then run that from the SQL
Agent job step.
If you are using stored procedures withing the DTS package then you can have
parameters that maped to the global variables see
http://www.sqldts.com/234.aspx
John
"Paul" wrote:
[vbcol=seagreen]
> Hi thanks for the response, for some reason I did not receive the
> notification of replies. So would you place the variable declarations as
> well as the provided code in the actual SQL job as the command in a step, or
> would this be placed in one of the queries of the dts package?
> --
> Paul G
> Software engineer.
>
> "John Bell" wrote:
|||ok thanks for the additional information. I will probably have to run this
manually for some time as it is necessary to perform some manual data checks
first. Eventually hope to automate and schedule the task. Will take a look
at the provided link.
Paul G
Software engineer.
"John Bell" wrote:
[vbcol=seagreen]
> Hi Paul
> The values would need to be global variables and to assign them withing the
> job would require an activeX task. It is easier and probably a more flexible
> design if you have a job that you pass the dates required to it. Then if at
> some point you need a different date range or run it manually then it is easy
> to do.
> I would put the code into a stored procedure and then run that from the SQL
> Agent job step.
> If you are using stored procedures withing the DTS package then you can have
> parameters that maped to the global variables see
> http://www.sqldts.com/234.aspx
>
> John
> "Paul" wrote:
consists of running a query on an sql database (using a date range as an
input) and then connecting to an Oracle database and appending a table in the
Oracle database with the data from the SQL query. The SQL query writes the
data to a table and I then use access to connect to this table and the
destination table in the Oracle database and run a simple query inside of
access to append the data into the Oracle table. I would like to create a
dts package that does this but the initial query requires 2 input parameters,
start date and end date, anyhow just wondering if you can still build a dts
package that when run from Enterprise manager would prompt for the date
inputs and then perform the sql query as well as the data append to the
Oracle table.
Thanks.
Paul G
Software engineer.
Hi Paul
You can set global variables on the command line such as (this has wrapped):
DECLARE @.datefrom CHAR(10)
DECLARE @.dateto CHAR(10)
DECLARE @.cmd varchar(2200)
DECLARE @.cmdroot varchar(2000)
DECLARE @.stat int
SET @.cmdroot = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N "' + DB_NAME()
+ ' Monthly Export" '
SELECT@.dateto =
CONVERT(CHAR(10),DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)),121),
@.datefrom =
CONVERT(CHAR(10),DATEADD(m,-1,DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME))),121)
SET @.cmd = @.cmdroot + ' /A "DateFrom":"7"="' + @.datefrom + '" /A
"DateTo":"7"="' + @.dateto + '"'
EXEC @.stat = master..xp_cmdshell @.cmd
SET @.stat = COALESCE(@.stat,@.@.ERROR)
John
"Paul" wrote:
> Hi I am using mssql 2000 and have been performing a data port manually that
> consists of running a query on an sql database (using a date range as an
> input) and then connecting to an Oracle database and appending a table in the
> Oracle database with the data from the SQL query. The SQL query writes the
> data to a table and I then use access to connect to this table and the
> destination table in the Oracle database and run a simple query inside of
> access to append the data into the Oracle table. I would like to create a
> dts package that does this but the initial query requires 2 input parameters,
> start date and end date, anyhow just wondering if you can still build a dts
> package that when run from Enterprise manager would prompt for the date
> inputs and then perform the sql query as well as the data append to the
> Oracle table.
> Thanks.
> --
> Paul G
> Software engineer.
|||Hi thanks for the response, for some reason I did not receive the
notification of replies. So would you place the variable declarations as
well as the provided code in the actual SQL job as the command in a step, or
would this be placed in one of the queries of the dts package?
Paul G
Software engineer.
"John Bell" wrote:
[vbcol=seagreen]
> Hi Paul
> You can set global variables on the command line such as (this has wrapped):
> DECLARE @.datefrom CHAR(10)
> DECLARE @.dateto CHAR(10)
> DECLARE @.cmd varchar(2200)
> DECLARE @.cmdroot varchar(2000)
> DECLARE @.stat int
> SET @.cmdroot = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N "' + DB_NAME()
> + ' Monthly Export" '
> SELECT@.dateto =
> CONVERT(CHAR(10),DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)),121),
> @.datefrom =
> CONVERT(CHAR(10),DATEADD(m,-1,DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME))),121)
> SET @.cmd = @.cmdroot + ' /A "DateFrom":"7"="' + @.datefrom + '" /A
> "DateTo":"7"="' + @.dateto + '"'
> EXEC @.stat = master..xp_cmdshell @.cmd
> SET @.stat = COALESCE(@.stat,@.@.ERROR)
> John
> "Paul" wrote:
|||Hi Paul
The values would need to be global variables and to assign them withing the
job would require an activeX task. It is easier and probably a more flexible
design if you have a job that you pass the dates required to it. Then if at
some point you need a different date range or run it manually then it is easy
to do.
I would put the code into a stored procedure and then run that from the SQL
Agent job step.
If you are using stored procedures withing the DTS package then you can have
parameters that maped to the global variables see
http://www.sqldts.com/234.aspx
John
"Paul" wrote:
[vbcol=seagreen]
> Hi thanks for the response, for some reason I did not receive the
> notification of replies. So would you place the variable declarations as
> well as the provided code in the actual SQL job as the command in a step, or
> would this be placed in one of the queries of the dts package?
> --
> Paul G
> Software engineer.
>
> "John Bell" wrote:
|||ok thanks for the additional information. I will probably have to run this
manually for some time as it is necessary to perform some manual data checks
first. Eventually hope to automate and schedule the task. Will take a look
at the provided link.
Paul G
Software engineer.
"John Bell" wrote:
[vbcol=seagreen]
> Hi Paul
> The values would need to be global variables and to assign them withing the
> job would require an activeX task. It is easier and probably a more flexible
> design if you have a job that you pass the dates required to it. Then if at
> some point you need a different date range or run it manually then it is easy
> to do.
> I would put the code into a stored procedure and then run that from the SQL
> Agent job step.
> If you are using stored procedures withing the DTS package then you can have
> parameters that maped to the global variables see
> http://www.sqldts.com/234.aspx
>
> John
> "Paul" wrote:
DTS package with inputs
Hi I am using mssql 2000 and have been performing a data port manually that
consists of running a query on an sql database (using a date range as an
input) and then connecting to an Oracle database and appending a table in the
Oracle database with the data from the SQL query. The SQL query writes the
data to a table and I then use access to connect to this table and the
destination table in the Oracle database and run a simple query inside of
access to append the data into the Oracle table. I would like to create a
dts package that does this but the initial query requires 2 input parameters,
start date and end date, anyhow just wondering if you can still build a dts
package that when run from Enterprise manager would prompt for the date
inputs and then perform the sql query as well as the data append to the
Oracle table.
Thanks.
--
Paul G
Software engineer.Hi Paul
You can set global variables on the command line such as (this has wrapped):
DECLARE @.datefrom CHAR(10)
DECLARE @.dateto CHAR(10)
DECLARE @.cmd varchar(2200)
DECLARE @.cmdroot varchar(2000)
DECLARE @.stat int
SET @.cmdroot = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N "' + DB_NAME()
+ ' Monthly Export" '
SELECT @.dateto =CONVERT(CHAR(10),DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)),121),
@.datefrom =CONVERT(CHAR(10),DATEADD(m,-1,DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME))),121)
SET @.cmd = @.cmdroot + ' /A "DateFrom":"7"="' + @.datefrom + '" /A
"DateTo":"7"="' + @.dateto + '"'
EXEC @.stat = master..xp_cmdshell @.cmd
SET @.stat = COALESCE(@.stat,@.@.ERROR)
John
"Paul" wrote:
> Hi I am using mssql 2000 and have been performing a data port manually that
> consists of running a query on an sql database (using a date range as an
> input) and then connecting to an Oracle database and appending a table in the
> Oracle database with the data from the SQL query. The SQL query writes the
> data to a table and I then use access to connect to this table and the
> destination table in the Oracle database and run a simple query inside of
> access to append the data into the Oracle table. I would like to create a
> dts package that does this but the initial query requires 2 input parameters,
> start date and end date, anyhow just wondering if you can still build a dts
> package that when run from Enterprise manager would prompt for the date
> inputs and then perform the sql query as well as the data append to the
> Oracle table.
> Thanks.
> --
> Paul G
> Software engineer.|||Hi thanks for the response, for some reason I did not receive the
notification of replies. So would you place the variable declarations as
well as the provided code in the actual SQL job as the command in a step, or
would this be placed in one of the queries of the dts package?
--
Paul G
Software engineer.
"John Bell" wrote:
> Hi Paul
> You can set global variables on the command line such as (this has wrapped):
> DECLARE @.datefrom CHAR(10)
> DECLARE @.dateto CHAR(10)
> DECLARE @.cmd varchar(2200)
> DECLARE @.cmdroot varchar(2000)
> DECLARE @.stat int
> SET @.cmdroot = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N "' + DB_NAME()
> + ' Monthly Export" '
> SELECT @.dateto => CONVERT(CHAR(10),DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)),121),
> @.datefrom => CONVERT(CHAR(10),DATEADD(m,-1,DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME))),121)
> SET @.cmd = @.cmdroot + ' /A "DateFrom":"7"="' + @.datefrom + '" /A
> "DateTo":"7"="' + @.dateto + '"'
> EXEC @.stat = master..xp_cmdshell @.cmd
> SET @.stat = COALESCE(@.stat,@.@.ERROR)
> John
> "Paul" wrote:
> > Hi I am using mssql 2000 and have been performing a data port manually that
> > consists of running a query on an sql database (using a date range as an
> > input) and then connecting to an Oracle database and appending a table in the
> > Oracle database with the data from the SQL query. The SQL query writes the
> > data to a table and I then use access to connect to this table and the
> > destination table in the Oracle database and run a simple query inside of
> > access to append the data into the Oracle table. I would like to create a
> > dts package that does this but the initial query requires 2 input parameters,
> > start date and end date, anyhow just wondering if you can still build a dts
> > package that when run from Enterprise manager would prompt for the date
> > inputs and then perform the sql query as well as the data append to the
> > Oracle table.
> > Thanks.
> > --
> > Paul G
> > Software engineer.|||Hi Paul
The values would need to be global variables and to assign them withing the
job would require an activeX task. It is easier and probably a more flexible
design if you have a job that you pass the dates required to it. Then if at
some point you need a different date range or run it manually then it is easy
to do.
I would put the code into a stored procedure and then run that from the SQL
Agent job step.
If you are using stored procedures withing the DTS package then you can have
parameters that maped to the global variables see
http://www.sqldts.com/234.aspx
John
"Paul" wrote:
> Hi thanks for the response, for some reason I did not receive the
> notification of replies. So would you place the variable declarations as
> well as the provided code in the actual SQL job as the command in a step, or
> would this be placed in one of the queries of the dts package?
> --
> Paul G
> Software engineer.
>
> "John Bell" wrote:
> > Hi Paul
> >
> > You can set global variables on the command line such as (this has wrapped):
> >
> > DECLARE @.datefrom CHAR(10)
> > DECLARE @.dateto CHAR(10)
> > DECLARE @.cmd varchar(2200)
> > DECLARE @.cmdroot varchar(2000)
> > DECLARE @.stat int
> >
> > SET @.cmdroot = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N "' + DB_NAME()
> > + ' Monthly Export" '
> >
> > SELECT @.dateto => > CONVERT(CHAR(10),DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)),121),
> > @.datefrom => > CONVERT(CHAR(10),DATEADD(m,-1,DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME))),121)
> >
> > SET @.cmd = @.cmdroot + ' /A "DateFrom":"7"="' + @.datefrom + '" /A
> > "DateTo":"7"="' + @.dateto + '"'
> >
> > EXEC @.stat = master..xp_cmdshell @.cmd
> > SET @.stat = COALESCE(@.stat,@.@.ERROR)
> >
> > John
> >
> > "Paul" wrote:
> >
> > > Hi I am using mssql 2000 and have been performing a data port manually that
> > > consists of running a query on an sql database (using a date range as an
> > > input) and then connecting to an Oracle database and appending a table in the
> > > Oracle database with the data from the SQL query. The SQL query writes the
> > > data to a table and I then use access to connect to this table and the
> > > destination table in the Oracle database and run a simple query inside of
> > > access to append the data into the Oracle table. I would like to create a
> > > dts package that does this but the initial query requires 2 input parameters,
> > > start date and end date, anyhow just wondering if you can still build a dts
> > > package that when run from Enterprise manager would prompt for the date
> > > inputs and then perform the sql query as well as the data append to the
> > > Oracle table.
> > > Thanks.
> > > --
> > > Paul G
> > > Software engineer.|||ok thanks for the additional information. I will probably have to run this
manually for some time as it is necessary to perform some manual data checks
first. Eventually hope to automate and schedule the task. Will take a look
at the provided link.
--
Paul G
Software engineer.
"John Bell" wrote:
> Hi Paul
> The values would need to be global variables and to assign them withing the
> job would require an activeX task. It is easier and probably a more flexible
> design if you have a job that you pass the dates required to it. Then if at
> some point you need a different date range or run it manually then it is easy
> to do.
> I would put the code into a stored procedure and then run that from the SQL
> Agent job step.
> If you are using stored procedures withing the DTS package then you can have
> parameters that maped to the global variables see
> http://www.sqldts.com/234.aspx
>
> John
> "Paul" wrote:
> > Hi thanks for the response, for some reason I did not receive the
> > notification of replies. So would you place the variable declarations as
> > well as the provided code in the actual SQL job as the command in a step, or
> > would this be placed in one of the queries of the dts package?
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "John Bell" wrote:
> >
> > > Hi Paul
> > >
> > > You can set global variables on the command line such as (this has wrapped):
> > >
> > > DECLARE @.datefrom CHAR(10)
> > > DECLARE @.dateto CHAR(10)
> > > DECLARE @.cmd varchar(2200)
> > > DECLARE @.cmdroot varchar(2000)
> > > DECLARE @.stat int
> > >
> > > SET @.cmdroot = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N "' + DB_NAME()
> > > + ' Monthly Export" '
> > >
> > > SELECT @.dateto => > > CONVERT(CHAR(10),DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)),121),
> > > @.datefrom => > > CONVERT(CHAR(10),DATEADD(m,-1,DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME))),121)
> > >
> > > SET @.cmd = @.cmdroot + ' /A "DateFrom":"7"="' + @.datefrom + '" /A
> > > "DateTo":"7"="' + @.dateto + '"'
> > >
> > > EXEC @.stat = master..xp_cmdshell @.cmd
> > > SET @.stat = COALESCE(@.stat,@.@.ERROR)
> > >
> > > John
> > >
> > > "Paul" wrote:
> > >
> > > > Hi I am using mssql 2000 and have been performing a data port manually that
> > > > consists of running a query on an sql database (using a date range as an
> > > > input) and then connecting to an Oracle database and appending a table in the
> > > > Oracle database with the data from the SQL query. The SQL query writes the
> > > > data to a table and I then use access to connect to this table and the
> > > > destination table in the Oracle database and run a simple query inside of
> > > > access to append the data into the Oracle table. I would like to create a
> > > > dts package that does this but the initial query requires 2 input parameters,
> > > > start date and end date, anyhow just wondering if you can still build a dts
> > > > package that when run from Enterprise manager would prompt for the date
> > > > inputs and then perform the sql query as well as the data append to the
> > > > Oracle table.
> > > > Thanks.
> > > > --
> > > > Paul G
> > > > Software engineer.
consists of running a query on an sql database (using a date range as an
input) and then connecting to an Oracle database and appending a table in the
Oracle database with the data from the SQL query. The SQL query writes the
data to a table and I then use access to connect to this table and the
destination table in the Oracle database and run a simple query inside of
access to append the data into the Oracle table. I would like to create a
dts package that does this but the initial query requires 2 input parameters,
start date and end date, anyhow just wondering if you can still build a dts
package that when run from Enterprise manager would prompt for the date
inputs and then perform the sql query as well as the data append to the
Oracle table.
Thanks.
--
Paul G
Software engineer.Hi Paul
You can set global variables on the command line such as (this has wrapped):
DECLARE @.datefrom CHAR(10)
DECLARE @.dateto CHAR(10)
DECLARE @.cmd varchar(2200)
DECLARE @.cmdroot varchar(2000)
DECLARE @.stat int
SET @.cmdroot = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N "' + DB_NAME()
+ ' Monthly Export" '
SELECT @.dateto =CONVERT(CHAR(10),DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)),121),
@.datefrom =CONVERT(CHAR(10),DATEADD(m,-1,DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME))),121)
SET @.cmd = @.cmdroot + ' /A "DateFrom":"7"="' + @.datefrom + '" /A
"DateTo":"7"="' + @.dateto + '"'
EXEC @.stat = master..xp_cmdshell @.cmd
SET @.stat = COALESCE(@.stat,@.@.ERROR)
John
"Paul" wrote:
> Hi I am using mssql 2000 and have been performing a data port manually that
> consists of running a query on an sql database (using a date range as an
> input) and then connecting to an Oracle database and appending a table in the
> Oracle database with the data from the SQL query. The SQL query writes the
> data to a table and I then use access to connect to this table and the
> destination table in the Oracle database and run a simple query inside of
> access to append the data into the Oracle table. I would like to create a
> dts package that does this but the initial query requires 2 input parameters,
> start date and end date, anyhow just wondering if you can still build a dts
> package that when run from Enterprise manager would prompt for the date
> inputs and then perform the sql query as well as the data append to the
> Oracle table.
> Thanks.
> --
> Paul G
> Software engineer.|||Hi thanks for the response, for some reason I did not receive the
notification of replies. So would you place the variable declarations as
well as the provided code in the actual SQL job as the command in a step, or
would this be placed in one of the queries of the dts package?
--
Paul G
Software engineer.
"John Bell" wrote:
> Hi Paul
> You can set global variables on the command line such as (this has wrapped):
> DECLARE @.datefrom CHAR(10)
> DECLARE @.dateto CHAR(10)
> DECLARE @.cmd varchar(2200)
> DECLARE @.cmdroot varchar(2000)
> DECLARE @.stat int
> SET @.cmdroot = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N "' + DB_NAME()
> + ' Monthly Export" '
> SELECT @.dateto => CONVERT(CHAR(10),DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)),121),
> @.datefrom => CONVERT(CHAR(10),DATEADD(m,-1,DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME))),121)
> SET @.cmd = @.cmdroot + ' /A "DateFrom":"7"="' + @.datefrom + '" /A
> "DateTo":"7"="' + @.dateto + '"'
> EXEC @.stat = master..xp_cmdshell @.cmd
> SET @.stat = COALESCE(@.stat,@.@.ERROR)
> John
> "Paul" wrote:
> > Hi I am using mssql 2000 and have been performing a data port manually that
> > consists of running a query on an sql database (using a date range as an
> > input) and then connecting to an Oracle database and appending a table in the
> > Oracle database with the data from the SQL query. The SQL query writes the
> > data to a table and I then use access to connect to this table and the
> > destination table in the Oracle database and run a simple query inside of
> > access to append the data into the Oracle table. I would like to create a
> > dts package that does this but the initial query requires 2 input parameters,
> > start date and end date, anyhow just wondering if you can still build a dts
> > package that when run from Enterprise manager would prompt for the date
> > inputs and then perform the sql query as well as the data append to the
> > Oracle table.
> > Thanks.
> > --
> > Paul G
> > Software engineer.|||Hi Paul
The values would need to be global variables and to assign them withing the
job would require an activeX task. It is easier and probably a more flexible
design if you have a job that you pass the dates required to it. Then if at
some point you need a different date range or run it manually then it is easy
to do.
I would put the code into a stored procedure and then run that from the SQL
Agent job step.
If you are using stored procedures withing the DTS package then you can have
parameters that maped to the global variables see
http://www.sqldts.com/234.aspx
John
"Paul" wrote:
> Hi thanks for the response, for some reason I did not receive the
> notification of replies. So would you place the variable declarations as
> well as the provided code in the actual SQL job as the command in a step, or
> would this be placed in one of the queries of the dts package?
> --
> Paul G
> Software engineer.
>
> "John Bell" wrote:
> > Hi Paul
> >
> > You can set global variables on the command line such as (this has wrapped):
> >
> > DECLARE @.datefrom CHAR(10)
> > DECLARE @.dateto CHAR(10)
> > DECLARE @.cmd varchar(2200)
> > DECLARE @.cmdroot varchar(2000)
> > DECLARE @.stat int
> >
> > SET @.cmdroot = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N "' + DB_NAME()
> > + ' Monthly Export" '
> >
> > SELECT @.dateto => > CONVERT(CHAR(10),DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)),121),
> > @.datefrom => > CONVERT(CHAR(10),DATEADD(m,-1,DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME))),121)
> >
> > SET @.cmd = @.cmdroot + ' /A "DateFrom":"7"="' + @.datefrom + '" /A
> > "DateTo":"7"="' + @.dateto + '"'
> >
> > EXEC @.stat = master..xp_cmdshell @.cmd
> > SET @.stat = COALESCE(@.stat,@.@.ERROR)
> >
> > John
> >
> > "Paul" wrote:
> >
> > > Hi I am using mssql 2000 and have been performing a data port manually that
> > > consists of running a query on an sql database (using a date range as an
> > > input) and then connecting to an Oracle database and appending a table in the
> > > Oracle database with the data from the SQL query. The SQL query writes the
> > > data to a table and I then use access to connect to this table and the
> > > destination table in the Oracle database and run a simple query inside of
> > > access to append the data into the Oracle table. I would like to create a
> > > dts package that does this but the initial query requires 2 input parameters,
> > > start date and end date, anyhow just wondering if you can still build a dts
> > > package that when run from Enterprise manager would prompt for the date
> > > inputs and then perform the sql query as well as the data append to the
> > > Oracle table.
> > > Thanks.
> > > --
> > > Paul G
> > > Software engineer.|||ok thanks for the additional information. I will probably have to run this
manually for some time as it is necessary to perform some manual data checks
first. Eventually hope to automate and schedule the task. Will take a look
at the provided link.
--
Paul G
Software engineer.
"John Bell" wrote:
> Hi Paul
> The values would need to be global variables and to assign them withing the
> job would require an activeX task. It is easier and probably a more flexible
> design if you have a job that you pass the dates required to it. Then if at
> some point you need a different date range or run it manually then it is easy
> to do.
> I would put the code into a stored procedure and then run that from the SQL
> Agent job step.
> If you are using stored procedures withing the DTS package then you can have
> parameters that maped to the global variables see
> http://www.sqldts.com/234.aspx
>
> John
> "Paul" wrote:
> > Hi thanks for the response, for some reason I did not receive the
> > notification of replies. So would you place the variable declarations as
> > well as the provided code in the actual SQL job as the command in a step, or
> > would this be placed in one of the queries of the dts package?
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "John Bell" wrote:
> >
> > > Hi Paul
> > >
> > > You can set global variables on the command line such as (this has wrapped):
> > >
> > > DECLARE @.datefrom CHAR(10)
> > > DECLARE @.dateto CHAR(10)
> > > DECLARE @.cmd varchar(2200)
> > > DECLARE @.cmdroot varchar(2000)
> > > DECLARE @.stat int
> > >
> > > SET @.cmdroot = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N "' + DB_NAME()
> > > + ' Monthly Export" '
> > >
> > > SELECT @.dateto => > > CONVERT(CHAR(10),DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)),121),
> > > @.datefrom => > > CONVERT(CHAR(10),DATEADD(m,-1,DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME))),121)
> > >
> > > SET @.cmd = @.cmdroot + ' /A "DateFrom":"7"="' + @.datefrom + '" /A
> > > "DateTo":"7"="' + @.dateto + '"'
> > >
> > > EXEC @.stat = master..xp_cmdshell @.cmd
> > > SET @.stat = COALESCE(@.stat,@.@.ERROR)
> > >
> > > John
> > >
> > > "Paul" wrote:
> > >
> > > > Hi I am using mssql 2000 and have been performing a data port manually that
> > > > consists of running a query on an sql database (using a date range as an
> > > > input) and then connecting to an Oracle database and appending a table in the
> > > > Oracle database with the data from the SQL query. The SQL query writes the
> > > > data to a table and I then use access to connect to this table and the
> > > > destination table in the Oracle database and run a simple query inside of
> > > > access to append the data into the Oracle table. I would like to create a
> > > > dts package that does this but the initial query requires 2 input parameters,
> > > > start date and end date, anyhow just wondering if you can still build a dts
> > > > package that when run from Enterprise manager would prompt for the date
> > > > inputs and then perform the sql query as well as the data append to the
> > > > Oracle table.
> > > > Thanks.
> > > > --
> > > > Paul G
> > > > Software engineer.
DTS Package scheduled to run as Job Fails
Hi,
I have a DTS package which when I execute it manually, it executes perfectly. I need to schedule it and therefore had to use the scheduler which would creata a job for the package. The problem is that job. It does not execute; it fails every time. The error message is: Sql server does not exist or access is denied.
I have tried to trouble-shoot by setting up an alias for the server in the client network utility for sql server. I also create a new dsn with the new alias. It still failed with the same error message. I edited the properties of the dts package to connect to the server's ip instead of it's alias or logical name. Still fails.
Any ideas anyone?
MariaIs sa the owner of the job ? If not make sa the owner
I have a DTS package which when I execute it manually, it executes perfectly. I need to schedule it and therefore had to use the scheduler which would creata a job for the package. The problem is that job. It does not execute; it fails every time. The error message is: Sql server does not exist or access is denied.
I have tried to trouble-shoot by setting up an alias for the server in the client network utility for sql server. I also create a new dsn with the new alias. It still failed with the same error message. I edited the properties of the dts package to connect to the server's ip instead of it's alias or logical name. Still fails.
Any ideas anyone?
MariaIs sa the owner of the job ? If not make sa the owner
Monday, March 19, 2012
DTS Package Not Running When Schedlued
I have been having a problem where I can not get a scheduled DTS pachkage t
o
run. It fails at all launch intervals. The Job can be launched manually and
it will import the data just fine. I am trying to import foxpro files into
the SQL server. Is this a permissions issue? What persmissions are passed
from SQL to the other server I am trying to import from?
Thanks
--
MitchTry granting permissions to the folder that contains the import FoxPro files
to the Windows account that the SQL Server and SQL Server Agent services are
starting under.
HTH
Jerry
"Mitchm" <Mitchm@.discussions.microsoft.com> wrote in message
news:4D64AB57-815C-4A91-87C2-9B5AFF3D26F5@.microsoft.com...
>I have been having a problem where I can not get a scheduled DTS pachkage
>to
> run. It fails at all launch intervals. The Job can be launched manually
> and
> it will import the data just fine. I am trying to import foxpro files into
> the SQL server. Is this a permissions issue? What persmissions are passed
> from SQL to the other server I am trying to import from?
> Thanks
> --
> Mitch|||Running from the scheduler, your DTS package is going to run under the
context of the user that SQL Agent is running under. Check the
properties of SQL Agent, see what user the service is running as. That
user needs to have access to the share that your Foxpro database is on.
o
run. It fails at all launch intervals. The Job can be launched manually and
it will import the data just fine. I am trying to import foxpro files into
the SQL server. Is this a permissions issue? What persmissions are passed
from SQL to the other server I am trying to import from?
Thanks
--
MitchTry granting permissions to the folder that contains the import FoxPro files
to the Windows account that the SQL Server and SQL Server Agent services are
starting under.
HTH
Jerry
"Mitchm" <Mitchm@.discussions.microsoft.com> wrote in message
news:4D64AB57-815C-4A91-87C2-9B5AFF3D26F5@.microsoft.com...
>I have been having a problem where I can not get a scheduled DTS pachkage
>to
> run. It fails at all launch intervals. The Job can be launched manually
> and
> it will import the data just fine. I am trying to import foxpro files into
> the SQL server. Is this a permissions issue? What persmissions are passed
> from SQL to the other server I am trying to import from?
> Thanks
> --
> Mitch|||Running from the scheduler, your DTS package is going to run under the
context of the user that SQL Agent is running under. Check the
properties of SQL Agent, see what user the service is running as. That
user needs to have access to the share that your Foxpro database is on.
DTS Package Not Running When Schedlued
I have been having a problem where I can not get a scheduled DTS pachkage to
run. It fails at all launch intervals. The Job can be launched manually and
it will import the data just fine. I am trying to import foxpro files into
the SQL server. Is this a permissions issue? What persmissions are passed
from SQL to the other server I am trying to import from?
Thanks
--
MitchTry granting permissions to the folder that contains the import FoxPro files
to the Windows account that the SQL Server and SQL Server Agent services are
starting under.
HTH
Jerry
"Mitchm" <Mitchm@.discussions.microsoft.com> wrote in message
news:4D64AB57-815C-4A91-87C2-9B5AFF3D26F5@.microsoft.com...
>I have been having a problem where I can not get a scheduled DTS pachkage
>to
> run. It fails at all launch intervals. The Job can be launched manually
> and
> it will import the data just fine. I am trying to import foxpro files into
> the SQL server. Is this a permissions issue? What persmissions are passed
> from SQL to the other server I am trying to import from?
> Thanks
> --
> Mitch|||Running from the scheduler, your DTS package is going to run under the
context of the user that SQL Agent is running under. Check the
properties of SQL Agent, see what user the service is running as. That
user needs to have access to the share that your Foxpro database is on.
run. It fails at all launch intervals. The Job can be launched manually and
it will import the data just fine. I am trying to import foxpro files into
the SQL server. Is this a permissions issue? What persmissions are passed
from SQL to the other server I am trying to import from?
Thanks
--
MitchTry granting permissions to the folder that contains the import FoxPro files
to the Windows account that the SQL Server and SQL Server Agent services are
starting under.
HTH
Jerry
"Mitchm" <Mitchm@.discussions.microsoft.com> wrote in message
news:4D64AB57-815C-4A91-87C2-9B5AFF3D26F5@.microsoft.com...
>I have been having a problem where I can not get a scheduled DTS pachkage
>to
> run. It fails at all launch intervals. The Job can be launched manually
> and
> it will import the data just fine. I am trying to import foxpro files into
> the SQL server. Is this a permissions issue? What persmissions are passed
> from SQL to the other server I am trying to import from?
> Thanks
> --
> Mitch|||Running from the scheduler, your DTS package is going to run under the
context of the user that SQL Agent is running under. Check the
properties of SQL Agent, see what user the service is running as. That
user needs to have access to the share that your Foxpro database is on.
DTS package manually/locally it runs fine. But the scheduled job run and fails
I was facing some issue on MP. Yesterday I changed the SQL services to use the local admin account. That didn't help my MP issue. But it may have created another issue.
When I run the package manually/locally it runs fine. But the scheduled job run and fails with the Title string.
----------
Executed as user: IL06EDM00\SYSTEM. ...tart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_3 DTSRun OnError: DTSStep_DTSExecuteSQLTask_2, Error = -2147467259 (80004005) Error string: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 18452 (4814) Error string: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2 DTSRun OnError: DTSStep_DTSExecuteSQLTask_3, Error = -2147467259 (80004005) Error string: Login failed for user '(null)'. Reason: Not associated with ... Process Exit Code 2. The step failed.
-------------------
Any ideas.is this Gary down the hall?
more than likely your DTS package is accessing something on another machine. When you run the DTS package, it runs with your security credentials. When the agent is running it, it uses the local system account which does not have network access. Change your sql services to run on a domain account that has access to places DTS needs to do stuff.
When I run the package manually/locally it runs fine. But the scheduled job run and fails with the Title string.
----------
Executed as user: IL06EDM00\SYSTEM. ...tart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_3 DTSRun OnError: DTSStep_DTSExecuteSQLTask_2, Error = -2147467259 (80004005) Error string: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 18452 (4814) Error string: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2 DTSRun OnError: DTSStep_DTSExecuteSQLTask_3, Error = -2147467259 (80004005) Error string: Login failed for user '(null)'. Reason: Not associated with ... Process Exit Code 2. The step failed.
-------------------
Any ideas.is this Gary down the hall?
more than likely your DTS package is accessing something on another machine. When you run the DTS package, it runs with your security credentials. When the agent is running it, it uses the local system account which does not have network access. Change your sql services to run on a domain account that has access to places DTS needs to do stuff.
Sunday, March 11, 2012
DTS package fails when scheduled but not when run manually
I created a DTS package to collect some data from a remote server.
When I run the package manually, it runs fine, but when I schedule it
to run at night, it always fails.
I added both my own account and my server account to the users on the
remote server, I gave them enough permissions to access the database/
tables I need.
Also, I made sure that the remote server allows both SQL server
accounts and windows accounts, but still no luck.
Anybody have any idea where to look next?
Thanks!
dribibuu@.gmail.com wrote:
> I created a DTS package to collect some data from a remote server.
> When I run the package manually, it runs fine, but when I schedule it
> to run at night, it always fails.
> I added both my own account and my server account to the users on the
> remote server, I gave them enough permissions to access the database/
> tables I need.
You do not need that. The connection to the source and destination SQL
server are stored in the DTS package itself.
What you need is permission to read the DTS package (which is apparently
stored on an SQL server) for the account, under which you execute the DTS.
> Also, I made sure that the remote server allows both SQL server
> accounts and windows accounts, but still no luck.
> Anybody have any idea where to look next?
> Thanks!
>
|||Hello, If your using intergrated security to log on the other server which it
sounds like you are then you should test the access using that account
directly using enterprise manager or QA. Also check the advanced settings on
the job and ask it to write the output to a file for logging this way you can
get more information on what is going on. also when you schedule a DTS job to
run like this is actually uses xp_cmdshell to execute the dtsrun.exe directly
so make sure you have the correct rights on the proc an NTFS writes on the
file. My suspicion is that if you add the logging to the job and execute the
job you will see what is wrong. did you try manually running the job?
Let me know how this goes.
John Vandervliet
"dribibuu@.gmail.com" wrote:
> I created a DTS package to collect some data from a remote server.
> When I run the package manually, it runs fine, but when I schedule it
> to run at night, it always fails.
> I added both my own account and my server account to the users on the
> remote server, I gave them enough permissions to access the database/
> tables I need.
> Also, I made sure that the remote server allows both SQL server
> accounts and windows accounts, but still no luck.
> Anybody have any idea where to look next?
> Thanks!
>
When I run the package manually, it runs fine, but when I schedule it
to run at night, it always fails.
I added both my own account and my server account to the users on the
remote server, I gave them enough permissions to access the database/
tables I need.
Also, I made sure that the remote server allows both SQL server
accounts and windows accounts, but still no luck.
Anybody have any idea where to look next?
Thanks!
dribibuu@.gmail.com wrote:
> I created a DTS package to collect some data from a remote server.
> When I run the package manually, it runs fine, but when I schedule it
> to run at night, it always fails.
> I added both my own account and my server account to the users on the
> remote server, I gave them enough permissions to access the database/
> tables I need.
You do not need that. The connection to the source and destination SQL
server are stored in the DTS package itself.
What you need is permission to read the DTS package (which is apparently
stored on an SQL server) for the account, under which you execute the DTS.
> Also, I made sure that the remote server allows both SQL server
> accounts and windows accounts, but still no luck.
> Anybody have any idea where to look next?
> Thanks!
>
|||Hello, If your using intergrated security to log on the other server which it
sounds like you are then you should test the access using that account
directly using enterprise manager or QA. Also check the advanced settings on
the job and ask it to write the output to a file for logging this way you can
get more information on what is going on. also when you schedule a DTS job to
run like this is actually uses xp_cmdshell to execute the dtsrun.exe directly
so make sure you have the correct rights on the proc an NTFS writes on the
file. My suspicion is that if you add the logging to the job and execute the
job you will see what is wrong. did you try manually running the job?
Let me know how this goes.
John Vandervliet
"dribibuu@.gmail.com" wrote:
> I created a DTS package to collect some data from a remote server.
> When I run the package manually, it runs fine, but when I schedule it
> to run at night, it always fails.
> I added both my own account and my server account to the users on the
> remote server, I gave them enough permissions to access the database/
> tables I need.
> Also, I made sure that the remote server allows both SQL server
> accounts and windows accounts, but still no luck.
> Anybody have any idea where to look next?
> Thanks!
>
DTS package fails when scheduled but not when run manually
I created a DTS package to collect some data from a remote server.
When I run the package manually, it runs fine, but when I schedule it
to run at night, it always fails.
I added both my own account and my server account to the users on the
remote server, I gave them enough permissions to access the database/
tables I need.
Also, I made sure that the remote server allows both SQL server
accounts and windows accounts, but still no luck.
Anybody have any idea where to look next?
Thanks!dribibuu@.gmail.com wrote:
> I created a DTS package to collect some data from a remote server.
> When I run the package manually, it runs fine, but when I schedule it
> to run at night, it always fails.
> I added both my own account and my server account to the users on the
> remote server, I gave them enough permissions to access the database/
> tables I need.
You do not need that. The connection to the source and destination SQL
server are stored in the DTS package itself.
What you need is permission to read the DTS package (which is apparently
stored on an SQL server) for the account, under which you execute the DTS.
> Also, I made sure that the remote server allows both SQL server
> accounts and windows accounts, but still no luck.
> Anybody have any idea where to look next?
> Thanks!
>|||Hello, If your using intergrated security to log on the other server which it
sounds like you are then you should test the access using that account
directly using enterprise manager or QA. Also check the advanced settings on
the job and ask it to write the output to a file for logging this way you can
get more information on what is going on. also when you schedule a DTS job to
run like this is actually uses xp_cmdshell to execute the dtsrun.exe directly
so make sure you have the correct rights on the proc an NTFS writes on the
file. My suspicion is that if you add the logging to the job and execute the
job you will see what is wrong. did you try manually running the job?
Let me know how this goes.
John Vandervliet
"dribibuu@.gmail.com" wrote:
> I created a DTS package to collect some data from a remote server.
> When I run the package manually, it runs fine, but when I schedule it
> to run at night, it always fails.
> I added both my own account and my server account to the users on the
> remote server, I gave them enough permissions to access the database/
> tables I need.
> Also, I made sure that the remote server allows both SQL server
> accounts and windows accounts, but still no luck.
> Anybody have any idea where to look next?
> Thanks!
>|||"Yura Shalak" <yuras@.nospam.nospam> wrote in message
news:uDb0AchnHHA.4552@.TK2MSFTNGP04.phx.gbl...
> dribibuu@.gmail.com wrote:
>> I created a DTS package to collect some data from a remote server.
>> When I run the package manually, it runs fine, but when I schedule it
>> to run at night, it always fails.
>> I added both my own account and my server account to the users on the
>> remote server, I gave them enough permissions to access the database/
>> tables I need.
> You do not need that. The connection to the source and destination SQL
> server are stored in the DTS package itself.
> What you need is permission to read the DTS package (which is apparently
> stored on an SQL server) for the account, under which you execute the DTS.
Hmmm...
I am not sure with the response. The account under which you execute the
job, which in turn executes the DTS, needs to have access to the data you
are trying to access. I'd follow the other response to manually run the job
and see the error message.
Quentin|||Quentin Ran wrote:
> "Yura Shalak" <yuras@.nospam.nospam> wrote in message
> news:uDb0AchnHHA.4552@.TK2MSFTNGP04.phx.gbl...
>> dribibuu@.gmail.com wrote:
>> I created a DTS package to collect some data from a remote server.
>> When I run the package manually, it runs fine, but when I schedule it
>> to run at night, it always fails.
BTW, how do you schedule this execution?
Is it an OS job or SQL Server Agent job?
>> I added both my own account and my server account to the users on the
>> remote server, I gave them enough permissions to access the database/
>> tables I need.
>> You do not need that. The connection to the source and destination SQL
>> server are stored in the DTS package itself.
>> What you need is permission to read the DTS package (which is apparently
>> stored on an SQL server) for the account, under which you execute the DTS.
> Hmmm...
> I am not sure with the response. The account under which you execute the
> job, which in turn executes the DTS, needs to have access to the data you
> are trying to access.
Nope. Well, not exactly. To be precise, it depends on the connection
properties: if the Windows Authentication is used, then yes, the
permissions are needed. If SQL server authentication is used, then no,
it needs the access to the DTS package only. And the connection
properties for the data processing (including the credentials) are
defined in the DTS package itself.
Of course, if the package works with a local non-SQL data, the account,
which executes the package, needs access to these data. But that is
another story...
>I'd follow the other response to manually run the job
> and see the error message.
Look at the original post: it runs fine when executed manually.|||> BTW, how do you schedule this execution?
> Is it an OS job or SQL Server Agent job?
If you right click the package it gives you the option of scheduling it.
Then it becomes a SQL Agent job.
> Nope. Well, not exactly. To be precise, it depends on the connection
> properties: if the Windows Authentication is used, then yes, the
> permissions are needed. If SQL server authentication is used, then no,
> it needs the access to the DTS package only. And the connection properties
> for the data processing (including the credentials) are defined in the DTS
> package itself.
> Of course, if the package works with a local non-SQL data, the account,
> which executes the package, needs access to these data. But that is
> another story...
Agreed. But it looks that you have quite some SQL Server authenticated
logins, which is not quite good security practice.
> Look at the original post: it runs fine when executed manually.
The package ran fine manually, but not the job executing the package. The
original post did not specify whether the job was manually run.
HTH
Quentin|||Quentin Ran wrote:
>> BTW, how do you schedule this execution?
>> Is it an OS job or SQL Server Agent job?
> If you right click the package it gives you the option of scheduling it.
> Then it becomes a SQL Agent job.
If that is an Agent job and both connections are using Windows
Authentication, then the account under which the Agent is running (not
the SQL Server service) must have access to both remote and local server
data.
Try running the Agent under your account.
>> Nope. Well, not exactly. To be precise, it depends on the connection
>> properties: if the Windows Authentication is used, then yes, the
>> permissions are needed. If SQL server authentication is used, then no,
>> it needs the access to the DTS package only. And the connection properties
>> for the data processing (including the credentials) are defined in the DTS
>> package itself.
>> Of course, if the package works with a local non-SQL data, the account,
>> which executes the package, needs access to these data. But that is
>> another story...
> Agreed. But it looks that you have quite some SQL Server authenticated
> logins, which is not quite good security practice.
Well, Windows security is not always an option.
>> Look at the original post: it runs fine when executed manually.
> The package ran fine manually, but not the job executing the package. The
> original post did not specify whether the job was manually run.
>
Ah... Yes, that is what I missed.
When I run the package manually, it runs fine, but when I schedule it
to run at night, it always fails.
I added both my own account and my server account to the users on the
remote server, I gave them enough permissions to access the database/
tables I need.
Also, I made sure that the remote server allows both SQL server
accounts and windows accounts, but still no luck.
Anybody have any idea where to look next?
Thanks!dribibuu@.gmail.com wrote:
> I created a DTS package to collect some data from a remote server.
> When I run the package manually, it runs fine, but when I schedule it
> to run at night, it always fails.
> I added both my own account and my server account to the users on the
> remote server, I gave them enough permissions to access the database/
> tables I need.
You do not need that. The connection to the source and destination SQL
server are stored in the DTS package itself.
What you need is permission to read the DTS package (which is apparently
stored on an SQL server) for the account, under which you execute the DTS.
> Also, I made sure that the remote server allows both SQL server
> accounts and windows accounts, but still no luck.
> Anybody have any idea where to look next?
> Thanks!
>|||Hello, If your using intergrated security to log on the other server which it
sounds like you are then you should test the access using that account
directly using enterprise manager or QA. Also check the advanced settings on
the job and ask it to write the output to a file for logging this way you can
get more information on what is going on. also when you schedule a DTS job to
run like this is actually uses xp_cmdshell to execute the dtsrun.exe directly
so make sure you have the correct rights on the proc an NTFS writes on the
file. My suspicion is that if you add the logging to the job and execute the
job you will see what is wrong. did you try manually running the job?
Let me know how this goes.
John Vandervliet
"dribibuu@.gmail.com" wrote:
> I created a DTS package to collect some data from a remote server.
> When I run the package manually, it runs fine, but when I schedule it
> to run at night, it always fails.
> I added both my own account and my server account to the users on the
> remote server, I gave them enough permissions to access the database/
> tables I need.
> Also, I made sure that the remote server allows both SQL server
> accounts and windows accounts, but still no luck.
> Anybody have any idea where to look next?
> Thanks!
>|||"Yura Shalak" <yuras@.nospam.nospam> wrote in message
news:uDb0AchnHHA.4552@.TK2MSFTNGP04.phx.gbl...
> dribibuu@.gmail.com wrote:
>> I created a DTS package to collect some data from a remote server.
>> When I run the package manually, it runs fine, but when I schedule it
>> to run at night, it always fails.
>> I added both my own account and my server account to the users on the
>> remote server, I gave them enough permissions to access the database/
>> tables I need.
> You do not need that. The connection to the source and destination SQL
> server are stored in the DTS package itself.
> What you need is permission to read the DTS package (which is apparently
> stored on an SQL server) for the account, under which you execute the DTS.
Hmmm...
I am not sure with the response. The account under which you execute the
job, which in turn executes the DTS, needs to have access to the data you
are trying to access. I'd follow the other response to manually run the job
and see the error message.
Quentin|||Quentin Ran wrote:
> "Yura Shalak" <yuras@.nospam.nospam> wrote in message
> news:uDb0AchnHHA.4552@.TK2MSFTNGP04.phx.gbl...
>> dribibuu@.gmail.com wrote:
>> I created a DTS package to collect some data from a remote server.
>> When I run the package manually, it runs fine, but when I schedule it
>> to run at night, it always fails.
BTW, how do you schedule this execution?
Is it an OS job or SQL Server Agent job?
>> I added both my own account and my server account to the users on the
>> remote server, I gave them enough permissions to access the database/
>> tables I need.
>> You do not need that. The connection to the source and destination SQL
>> server are stored in the DTS package itself.
>> What you need is permission to read the DTS package (which is apparently
>> stored on an SQL server) for the account, under which you execute the DTS.
> Hmmm...
> I am not sure with the response. The account under which you execute the
> job, which in turn executes the DTS, needs to have access to the data you
> are trying to access.
Nope. Well, not exactly. To be precise, it depends on the connection
properties: if the Windows Authentication is used, then yes, the
permissions are needed. If SQL server authentication is used, then no,
it needs the access to the DTS package only. And the connection
properties for the data processing (including the credentials) are
defined in the DTS package itself.
Of course, if the package works with a local non-SQL data, the account,
which executes the package, needs access to these data. But that is
another story...
>I'd follow the other response to manually run the job
> and see the error message.
Look at the original post: it runs fine when executed manually.|||> BTW, how do you schedule this execution?
> Is it an OS job or SQL Server Agent job?
If you right click the package it gives you the option of scheduling it.
Then it becomes a SQL Agent job.
> Nope. Well, not exactly. To be precise, it depends on the connection
> properties: if the Windows Authentication is used, then yes, the
> permissions are needed. If SQL server authentication is used, then no,
> it needs the access to the DTS package only. And the connection properties
> for the data processing (including the credentials) are defined in the DTS
> package itself.
> Of course, if the package works with a local non-SQL data, the account,
> which executes the package, needs access to these data. But that is
> another story...
Agreed. But it looks that you have quite some SQL Server authenticated
logins, which is not quite good security practice.
> Look at the original post: it runs fine when executed manually.
The package ran fine manually, but not the job executing the package. The
original post did not specify whether the job was manually run.
HTH
Quentin|||Quentin Ran wrote:
>> BTW, how do you schedule this execution?
>> Is it an OS job or SQL Server Agent job?
> If you right click the package it gives you the option of scheduling it.
> Then it becomes a SQL Agent job.
If that is an Agent job and both connections are using Windows
Authentication, then the account under which the Agent is running (not
the SQL Server service) must have access to both remote and local server
data.
Try running the Agent under your account.
>> Nope. Well, not exactly. To be precise, it depends on the connection
>> properties: if the Windows Authentication is used, then yes, the
>> permissions are needed. If SQL server authentication is used, then no,
>> it needs the access to the DTS package only. And the connection properties
>> for the data processing (including the credentials) are defined in the DTS
>> package itself.
>> Of course, if the package works with a local non-SQL data, the account,
>> which executes the package, needs access to these data. But that is
>> another story...
> Agreed. But it looks that you have quite some SQL Server authenticated
> logins, which is not quite good security practice.
Well, Windows security is not always an option.
>> Look at the original post: it runs fine when executed manually.
> The package ran fine manually, but not the job executing the package. The
> original post did not specify whether the job was manually run.
>
Ah... Yes, that is what I missed.
Subscribe to:
Posts (Atom)