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.
Showing posts with label agent. Show all posts
Showing posts with label agent. Show all posts
Thursday, March 29, 2012
Sunday, March 11, 2012
DTS Package Error Executing in SQL Agent
Hi everyone,
I just got assigned to help out with a couple of problems with the running of DTS packages using the SQL Server Agent, this is the problem and a list of what I've done so far:
There are 4 packages that:
a) Deletes all records from a table and then populates it using a flat file that contains that day's transactions.
b) Creates a report in Excel
c) Creates a summary report in Excel
d) Does a), b) and c) for another table.
All these packages are scheduled to run weekdays starting at 8:00 am, and they were owned by sa. Last month we had a massive change of passwords thanks to an audit and unfortunately the dba left without letting us know that password. After the massive change, these packages started failing with errors like:
The job failed. The Job was invoked by Schedule 18 (DLYINVDT_XLS). The last step to run was step 1 (DLYINVDT_XLS).
The job failed. The Job was invoked by Schedule 22 (AGAR830_dts). The last step to run was step 1 (AGAR830_dts)
Yesterday I went into all the packages and changed the connection properties from user sa to another user that has permission over the tables and ran the 4 of them at least 10 times each, both using my personal laptop and directly in the server (just wanted to check remote and local, even though I know it really has nothing to do with the execution that its always local to the server).
Now, the funny thing is that I came in this morning and guess what.... yeap, the packages failed, I can run them manually with NO problem at all, but if I run them using the SQL Server Agent then they just will not run.
At the time I was writing this I found out the password for sa, so now I'm using it but unfortunately no change, the errors now are :
The job failed. The Job was invoked by User sa. The last step to run was step 1 (AGAR830_dts)
I'm an informix dba and I started training myself in sql a couple of months ago so I'm confortable with what I'm doing, but at this point in time I'm about to throw something to the screen :) Since I can execute the packages manually then the users are happy, but I would really like to find out why the Agent is not working right.
Any help will be GREATLY appreciated
Luis TorresHi,
To anyone interested... I solved the problem by recreating the DTS packages and rescheduling them, they work perfect now, would still like to know why the previous problem happened, but oh well, as long as its working now :)
Thanks
Luis Torres|||Generally, when Enterprise Manager creates a dtsrun job, the command is created with the DTS package ID. The ID goes with the previous version of the package. I usually change my jobs to run the package by name, so it always picks up the latest version.|||Thanks MCrowley it now makes sence on why it was failing :)
I just got assigned to help out with a couple of problems with the running of DTS packages using the SQL Server Agent, this is the problem and a list of what I've done so far:
There are 4 packages that:
a) Deletes all records from a table and then populates it using a flat file that contains that day's transactions.
b) Creates a report in Excel
c) Creates a summary report in Excel
d) Does a), b) and c) for another table.
All these packages are scheduled to run weekdays starting at 8:00 am, and they were owned by sa. Last month we had a massive change of passwords thanks to an audit and unfortunately the dba left without letting us know that password. After the massive change, these packages started failing with errors like:
The job failed. The Job was invoked by Schedule 18 (DLYINVDT_XLS). The last step to run was step 1 (DLYINVDT_XLS).
The job failed. The Job was invoked by Schedule 22 (AGAR830_dts). The last step to run was step 1 (AGAR830_dts)
Yesterday I went into all the packages and changed the connection properties from user sa to another user that has permission over the tables and ran the 4 of them at least 10 times each, both using my personal laptop and directly in the server (just wanted to check remote and local, even though I know it really has nothing to do with the execution that its always local to the server).
Now, the funny thing is that I came in this morning and guess what.... yeap, the packages failed, I can run them manually with NO problem at all, but if I run them using the SQL Server Agent then they just will not run.
At the time I was writing this I found out the password for sa, so now I'm using it but unfortunately no change, the errors now are :
The job failed. The Job was invoked by User sa. The last step to run was step 1 (AGAR830_dts)
I'm an informix dba and I started training myself in sql a couple of months ago so I'm confortable with what I'm doing, but at this point in time I'm about to throw something to the screen :) Since I can execute the packages manually then the users are happy, but I would really like to find out why the Agent is not working right.
Any help will be GREATLY appreciated
Luis TorresHi,
To anyone interested... I solved the problem by recreating the DTS packages and rescheduling them, they work perfect now, would still like to know why the previous problem happened, but oh well, as long as its working now :)
Thanks
Luis Torres|||Generally, when Enterprise Manager creates a dtsrun job, the command is created with the DTS package ID. The ID goes with the previous version of the package. I usually change my jobs to run the package by name, so it always picks up the latest version.|||Thanks MCrowley it now makes sence on why it was failing :)
Sunday, February 26, 2012
dts migration
Hi,
How to migrate scheduled jobs from 2000 to 2005. Is this possible by doing bcp out and bcp in system tables which stores sql agent jobs from msdb database ?
Regards
Nit
This question is not related to SSIS. I'd try another forum if I were you. https://forums.microsoft.com/MSDN/default.aspx?ForumGroupID=19&SiteID=1
-Jamie
Friday, February 24, 2012
DTS launch from SQL Server Agent
Hi,
I want to build a task that does 3 things automatically at a fixed time interval:
1) Refresh the data (copy newly inserted data from a datamart to a datawarehouse)
2) Run a program (exe) to produce some external reports
3) Refresh an OLAP Cube with the new data so that users can access it
Basically, using SQL Server Agent, setting up steps 1 and 2 was pretty straightforward (for step 1, the main point of entry is a single sp).
I ran into some trouble figuring the easiest way to complete step 3. I created a DTS package with one object that updates the Cude on my server. This seems to be working fine. Then, I created step 3 in Agent and chose 'CmdExec' as the type of command. I then referenced the external exe 'dtsrun' to execute my DTS package.
Everything seems fine but I'm not sure I used the "best" way of doing things. Since the SQL Server Agent and DTS manager are both SQL Server tools (so is the Analysis services), I'm wondering if there wasn't an easier (thus more robust) way of completing my 3rd step?
Anyone? How is this done where you've worked?
Thanks,
Skip.Well, that is the technique that MSDN (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_dts_pkgmng_9vck.asp) suggests.
You could always make the DTS Package into a SQL Agent job (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_dts_pkgmng_71v2.asp), then run the job using sp_start_job (http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_sa-sz_11uq.asp) (but that is deviant).
-PatP|||As always I suggest this kBA http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074 to help DTS to schedule as a job.
I want to build a task that does 3 things automatically at a fixed time interval:
1) Refresh the data (copy newly inserted data from a datamart to a datawarehouse)
2) Run a program (exe) to produce some external reports
3) Refresh an OLAP Cube with the new data so that users can access it
Basically, using SQL Server Agent, setting up steps 1 and 2 was pretty straightforward (for step 1, the main point of entry is a single sp).
I ran into some trouble figuring the easiest way to complete step 3. I created a DTS package with one object that updates the Cude on my server. This seems to be working fine. Then, I created step 3 in Agent and chose 'CmdExec' as the type of command. I then referenced the external exe 'dtsrun' to execute my DTS package.
Everything seems fine but I'm not sure I used the "best" way of doing things. Since the SQL Server Agent and DTS manager are both SQL Server tools (so is the Analysis services), I'm wondering if there wasn't an easier (thus more robust) way of completing my 3rd step?
Anyone? How is this done where you've worked?
Thanks,
Skip.Well, that is the technique that MSDN (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_dts_pkgmng_9vck.asp) suggests.
You could always make the DTS Package into a SQL Agent job (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_dts_pkgmng_71v2.asp), then run the job using sp_start_job (http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_sa-sz_11uq.asp) (but that is deviant).
-PatP|||As always I suggest this kBA http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074 to help DTS to schedule as a job.
DTS job fails when scheduled from SQL Agent
Folks,
I have a DTS job that imports data from text files (specified as odbc connections) from a remote server into a sql table on the same SQL server that the job has been created on.
The job runs fine if execute directly from the server. If I schedule the same job on the server (through jobs) executing under the same user, the job fails with..
Executed as user: mydomain\mylogin . ...art: DTSStep_DTSActiveScriptTask_1 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: [Microsoft][ODBC Text Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 1023 (3FF) Error string: Error source: Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 Error: -2147220440 (80040428); Provider Error: 0 (0) Error string: Package failed because Step 'DTSStep_DTSDataPumpTask_1' failed. Error source: Microsoft Data Transformation Services ... Process Exit Code 1. The step failed.
How come it loses the path to the file when I dont run it directly?
Cheers
MickDTS runs in the context of the client machine when you run it directly. That means that if you run it from Enterprise Manager on your local PC then it uses the settings, drive mappings and ODBC drivers of your workstation. When a DTS package is run by SQL Agent, it uses the settings from the Server. You have to ensure that the server has all the settings that your local machine does.
Be sure not to use mapped drives to specify file locations -- use UNC instead. This is because a mapped drive only exists in the context of a logged in user. SQL Agent is a service and thus is not logged in.
I hope this makes some sense; I still find this a difficult topic to explain clearly even after dealing with it for five years.
Regards,
hmscott|||Thanks for that,
Thing is I have done every step from package creation to scheduling ON the server itself through terminal services. I thought SQL Agent would be aware of these server-based system DSN's. Ill have a go at UNC then.
Cheers
ML|||http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074 - KBA to schedule DTS as a scheduled job and troubleshoot any issues.
HTH|||Thanks folks,
I used UNC text file sources instead of odbc text connections. Worked great.
Cheers
Mick
I have a DTS job that imports data from text files (specified as odbc connections) from a remote server into a sql table on the same SQL server that the job has been created on.
The job runs fine if execute directly from the server. If I schedule the same job on the server (through jobs) executing under the same user, the job fails with..
Executed as user: mydomain\mylogin . ...art: DTSStep_DTSActiveScriptTask_1 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: [Microsoft][ODBC Text Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 1023 (3FF) Error string: Error source: Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 Error: -2147220440 (80040428); Provider Error: 0 (0) Error string: Package failed because Step 'DTSStep_DTSDataPumpTask_1' failed. Error source: Microsoft Data Transformation Services ... Process Exit Code 1. The step failed.
How come it loses the path to the file when I dont run it directly?
Cheers
MickDTS runs in the context of the client machine when you run it directly. That means that if you run it from Enterprise Manager on your local PC then it uses the settings, drive mappings and ODBC drivers of your workstation. When a DTS package is run by SQL Agent, it uses the settings from the Server. You have to ensure that the server has all the settings that your local machine does.
Be sure not to use mapped drives to specify file locations -- use UNC instead. This is because a mapped drive only exists in the context of a logged in user. SQL Agent is a service and thus is not logged in.
I hope this makes some sense; I still find this a difficult topic to explain clearly even after dealing with it for five years.
Regards,
hmscott|||Thanks for that,
Thing is I have done every step from package creation to scheduling ON the server itself through terminal services. I thought SQL Agent would be aware of these server-based system DSN's. Ill have a go at UNC then.
Cheers
ML|||http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074 - KBA to schedule DTS as a scheduled job and troubleshoot any issues.
HTH|||Thanks folks,
I used UNC text file sources instead of odbc text connections. Worked great.
Cheers
Mick
Tuesday, February 14, 2012
dts import agent
Hi group
I want to copy a DB from one Sql Server 2000 SP3 to antother Sql 2000 Sp3.
Im using the DTS Import agent in Enterprise manager. After a few minutes
Ive got an error Message telling me that the copy process failed.
The Log file of the Enterprise Manager is empty. Any Idea what happened? The
Database has a size of 900 Mb, is that a problem?
Is there another solution to copy the db?
Backup and than restore is not possible because the database I want to copy
use replication.
Any ideas?
thx for any help
BjrnBjrn,
I don't follow why backup and restore is prevented in your situation by the
presence of replication (publisher or subscriber should be fine) - can you
explain more.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hi Paul
I have tried it.
I have build a new DB on my destination Sql Server. Than I have restored the
Databse.
Now I have a dead Databse. Enterprise Manager displays the Icon in grey not
in yellow and behind the Database Name is "loading" written.
I cant delete it, I cant show any properties nothing.
regards
Bjrn
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> schrieb im Newsbeitrag
news:eTNFzlCyGHA.4912@.TK2MSFTNGP03.phx.gbl...
> Bjrn,
> I don't follow why backup and restore is prevented in your situation by
the
> presence of replication (publisher or subscriber should be fine) - can you
> explain more.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>|||I think this is not related at all to replication - I'd guess that you have
restored the database using no_recovery.
Try running "restore database yourdbname with recovery".
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hey Paul
Thx for your help.
I dont know what is different but now I have a working restored DB.
regards
Bjrn
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> schrieb im Newsbeitrag
news:u0Y7eFGyGHA.4092@.TK2MSFTNGP04.phx.gbl...
> I think this is not related at all to replication - I'd guess that you
have
> restored the database using no_recovery.
> Try running "restore database yourdbname with recovery".
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
>
I want to copy a DB from one Sql Server 2000 SP3 to antother Sql 2000 Sp3.
Im using the DTS Import agent in Enterprise manager. After a few minutes
Ive got an error Message telling me that the copy process failed.
The Log file of the Enterprise Manager is empty. Any Idea what happened? The
Database has a size of 900 Mb, is that a problem?
Is there another solution to copy the db?
Backup and than restore is not possible because the database I want to copy
use replication.
Any ideas?
thx for any help
BjrnBjrn,
I don't follow why backup and restore is prevented in your situation by the
presence of replication (publisher or subscriber should be fine) - can you
explain more.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hi Paul
I have tried it.
I have build a new DB on my destination Sql Server. Than I have restored the
Databse.
Now I have a dead Databse. Enterprise Manager displays the Icon in grey not
in yellow and behind the Database Name is "loading" written.
I cant delete it, I cant show any properties nothing.
regards
Bjrn
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> schrieb im Newsbeitrag
news:eTNFzlCyGHA.4912@.TK2MSFTNGP03.phx.gbl...
> Bjrn,
> I don't follow why backup and restore is prevented in your situation by
the
> presence of replication (publisher or subscriber should be fine) - can you
> explain more.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>|||I think this is not related at all to replication - I'd guess that you have
restored the database using no_recovery.
Try running "restore database yourdbname with recovery".
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hey Paul
Thx for your help.
I dont know what is different but now I have a working restored DB.
regards
Bjrn
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> schrieb im Newsbeitrag
news:u0Y7eFGyGHA.4092@.TK2MSFTNGP04.phx.gbl...
> I think this is not related at all to replication - I'd guess that you
have
> restored the database using no_recovery.
> Try running "restore database yourdbname with recovery".
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
>
dts import agent
Hi group
I want to copy a DB from one Sql Server 2000 SP3 to antother Sql 2000 Sp3.
I´m using the DTS Import agent in Enterprise manager. After a few minutes
I´ve got an error Message telling me that the copy process failed.
The Log file of the Enterprise Manager is empty. Any Idea what happened? The
Database has a size of 900 Mb, is that a problem?
Is there another solution to copy the db?
Backup and than restore is not possible because the database I want to copy
use replication.
Any ideas?
thx for any help
BjörnBjörn,
I don't follow why backup and restore is prevented in your situation by the
presence of replication (publisher or subscriber should be fine) - can you
explain more.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hi Paul
I have tried it.
I have build a new DB on my destination Sql Server. Than I have restored the
Databse.
Now I have a dead Databse. Enterprise Manager displays the Icon in grey not
in yellow and behind the Database Name is "loading" written.
I can´t delete it, I can´t show any properties nothing.
regards
Björn
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> schrieb im Newsbeitrag
news:eTNFzlCyGHA.4912@.TK2MSFTNGP03.phx.gbl...
> Björn,
> I don't follow why backup and restore is prevented in your situation by
the
> presence of replication (publisher or subscriber should be fine) - can you
> explain more.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>|||I think this is not related at all to replication - I'd guess that you have
restored the database using no_recovery.
Try running "restore database yourdbname with recovery".
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hey Paul
Thx for your help.
I don´t know what is different but now I have a working restored DB.
regards
Björn
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> schrieb im Newsbeitrag
news:u0Y7eFGyGHA.4092@.TK2MSFTNGP04.phx.gbl...
> I think this is not related at all to replication - I'd guess that you
have
> restored the database using no_recovery.
> Try running "restore database yourdbname with recovery".
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
>
I want to copy a DB from one Sql Server 2000 SP3 to antother Sql 2000 Sp3.
I´m using the DTS Import agent in Enterprise manager. After a few minutes
I´ve got an error Message telling me that the copy process failed.
The Log file of the Enterprise Manager is empty. Any Idea what happened? The
Database has a size of 900 Mb, is that a problem?
Is there another solution to copy the db?
Backup and than restore is not possible because the database I want to copy
use replication.
Any ideas?
thx for any help
BjörnBjörn,
I don't follow why backup and restore is prevented in your situation by the
presence of replication (publisher or subscriber should be fine) - can you
explain more.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hi Paul
I have tried it.
I have build a new DB on my destination Sql Server. Than I have restored the
Databse.
Now I have a dead Databse. Enterprise Manager displays the Icon in grey not
in yellow and behind the Database Name is "loading" written.
I can´t delete it, I can´t show any properties nothing.
regards
Björn
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> schrieb im Newsbeitrag
news:eTNFzlCyGHA.4912@.TK2MSFTNGP03.phx.gbl...
> Björn,
> I don't follow why backup and restore is prevented in your situation by
the
> presence of replication (publisher or subscriber should be fine) - can you
> explain more.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>|||I think this is not related at all to replication - I'd guess that you have
restored the database using no_recovery.
Try running "restore database yourdbname with recovery".
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hey Paul
Thx for your help.
I don´t know what is different but now I have a working restored DB.
regards
Björn
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> schrieb im Newsbeitrag
news:u0Y7eFGyGHA.4092@.TK2MSFTNGP04.phx.gbl...
> I think this is not related at all to replication - I'd guess that you
have
> restored the database using no_recovery.
> Try running "restore database yourdbname with recovery".
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
>
Subscribe to:
Posts (Atom)