Scheduling DTS Package Using A Stored Procedure Problem

Apr 4, 2006

The DTS package runs fine through Enterprise manager successfully.
However, when scheduled through a job that runs the dts through the
following code:

DECLARE @findfile int
Exec @findfile = master.dbo.xp_cmdShell 'dir
\ServerNamefolderfilename.xls', no_output
IF (@findfile=0)
BEGIN
Exec master.dbo.xp_cmdshell 'dtsrun -E -ServerInstance
-N"DataImport"'
END

The servername specified in the above statement in a different server
than the server that the package resides on.

This is the error that I get when I try to run the same code using
query analyzer:

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 = -2147024893
(80070003)
Error string: The system cannot find the path specified.

Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 1100

Error Detail Records:

Error: -2147024893 (80070003); Provider Error: 0 (0)
Error string: The system cannot find the path specified.

Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 1100


Error: -2147024893 (80070003); Provider Error: 0 (0)
Error string: Cannot open a log file of specified name. The system
cannot find the path specified.

Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4700

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 (DTS) Package
Help file: sqldts80.hlp
Help context: 700

NULL


The job is owned by the SQLService account(Windows account) that has
System Admin rights and also is part of the Domain Admin User group.
The Domain Admin User group has full rights on the file that the DTS is
trying to access.

Any help in trying to figure out why the schedule job cannot find the
file path would be appreciated.



Thanks

KR

View 1 Replies


ADVERTISEMENT

Scheduling Stored Procedure

Mar 8, 2007

how can i schedule a sp to run every 10 minutes

View 3 Replies View Related

Scheduling Stored Procedure

Mar 7, 2007

how can i schedule a stored procedure to run every hour?

View 1 Replies View Related

Scheduling A Job Using A Stored Procedure Through A Stored Procedure

Jul 1, 2004

I currently new to SQL server and have been assigned a project to develop an auction site as part of my course. I would like to create a stored procedure which schedules a job to modify the 'auction state' field in a table to 'active' once the auction start time is reached and also run a scheduled job to close the auction at the end time.

I was thinking about using a stored procedure which calls on the sp_add_job but you have to use the msdb for this and you cannot use the 'USE' keyword inside a stored produre to call this. Am I going the wrong way about this or is it possible?

View 3 Replies View Related

Scheduling A DTS Package

Apr 6, 2001

I have created a DTS Package which collects data from DB2 and stores in a table on SQL Server v 7.0. The execution of the package works fine.

When I schedule the package by right-clicking and selecting SCHEDULE PACKAGE,
I fill in the dialog box. When the package runs, I receive the following error:
DTSRun: Loading... Error: -2147217390 (80041012); Provider Error: 0 (0) Error string: [Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionOpen (CreateFile()). Error source: Help file: Help context: 0. Process Exit Code 1. The step failed.

I get this same error when I run the package as a job.
The package is in the repository. My run command looks like:
DTSRun /S 111.11.11.11 /U sa /P xxxx /N DTS_Projections /R
The package owner is sa.

Can anyone offer any advise

View 1 Replies View Related

Scheduling A DTS Package

Aug 1, 2000

Can I call a DTS package from server1 to run on server2 if the package is on server1?

Does the package have to be on both servers? Both databases are identical.

And if not, how do I script the DTS package on server1 to be used and created on server2?

I would appreciate your help.

Thanks,
Dianne

View 2 Replies View Related

Scheduling A DTS Package

Mar 17, 2000

I created a simple test DTS package to import a text file (2 rows, 5 fields wide) into a test table. When I manually execute the package in DTS, it is successful. When I schedule the package and the text file is located on the local drive (C:) the package is successful. I then modified the package to import the same file from the network drives, manually executing the import the package is successful. But when I schedule this package to import from a network drive it fails. Help? Where could the problem come from? NT security, limitations in SQL Server 7 SW, different levels of security between manual and scheduled processes, network security?

FYI: I am running Server 7, on WIN NT Workstation 4.0, SP5, DB size 12 GB, rolling 90 days of data.

View 1 Replies View Related

Scheduling DTS Package

Sep 28, 1999

I can't get my dts package to run when I schedule it.

I keep getting this message.

"The job failed. The Job was invoked by User DALLASdwatson. The last step to run was step 1 (Store)."

I created the DTS Package "Store" and then saved it to Repository.

I can execute the package manually. But can't get it to work when I schedule
it.

Can anyone Please help me and tell me what I am doing wrong.

Thank you for your help.

Dianne

View 1 Replies View Related

SCHEDULING DTS PACKAGE TO RUN

Mar 10, 2000

HAVE CREATED LOCAL DTS PACKAGES WHICH I AM ABLE TO SCHEDULE OK. MY PROBLEM IS I HAVE A DTS PACKAGE WHIC
IMPORTS DATA FROM REMOTE DATABASE, THIS PACKAGE WILL RUN WHEN EXECUTED BUT I CANNOT SCHEDULE IT.
ANY IDEAS PLEASE
THANKS IN ADVANCE
PHILL

View 1 Replies View Related

Scheduling A DTS Package

Mar 9, 2006

I got a DTS package and when i schedule it to run every day it is failing....??

When i normally run the DTS it is executing but when i click on the JOB and start the DTS it is failing.... is there any permission issues i should look at while shceduling a Job..???? :confused:

View 1 Replies View Related

DTS Package: Scheduling

Sep 2, 2002

Hi,

I have a DTS package which populates tables in a database from text files.

This is scheduled to run at 8:30am every morning. I did this by right-clicking on the package and selecting "Schedule Package". I can see the task has been set up in SQL Server Agent's job list.

This task only runs for the first time, and not any further. This task runs "successfully" the first time, i.e the tables in the database are populated with records from a text file. However, when I click on job history for the job, it says there is no job history. The status on the job says "Executing Job Step '1 - ....".

I believe the first time the job was executed, it was not completed properly, hence the status has not been set to "Not Running".

The owner of the package is administrator on the server, therefore have full access to the server. The task was scheduled by the administrator too.

Does anyone have any suggestions on how I can fix this?

Thanks,
Vera

View 2 Replies View Related

Scheduling A DTS Package

May 10, 2004

I am using Enterprise manager with an MSDE engine. I have created a DTS package that updates a table in one of the databases. If I right click and choose "Execute Package" it runs...No sweat. When I try to use the scheduler to run the job every hour, it always fails between 1 and 10 seconds into the job...It only returns the error "Failed During Step 1."

I'm wondering if this feature won't work with MSDE? Does anyone have any ideas?

View 6 Replies View Related

Scheduling A DTS Package

Mar 7, 2007

I have setup a DTS Package using ActiveX to update some information via the SQL DB.

When i execute the DTS Package manually by right clicking on it and clicking on Execute Package, it works fine everytime.

When I try to schedule the package to run twice a day automatically, when the package starts, it executes once, however the status still shows executing and the next run time is listed as unknown. It seems to hang up and never finish. I do have the Step to quit after success and it still seems not to quit.

Any suggestions. This is the first DTS package I have ever used.
Thanks for any help

View 2 Replies View Related

Scheduling A Package To Run!

Dec 11, 2007

Hi All

I am having a slight problem with building a SSIS package and then scheduling it to run.

Basically I have created a .dtsx package and gone to Build Package and I can find it anywhare in SSMS. This is the first time I have created one of these so I apologise if thi sis a really stupid question.

Thanks

Gopher

View 1 Replies View Related

Scheduling A DTS Package

Jul 20, 2005

I have created a DTS package that imports a comma delimited (CSV) textfile into a table. One field uses a VB script that parses a date intothe proper format for the transformation. All other fields are copiedas is. When I run the package directly the date field is importedcorrectly (ie MM/DD/YYY). When I schedule the package to run (the jobis in SQL Server Agent, Jobs) the month and day entries aretransposed. In other words the date field is imported as DD/MM/YYYY(ie Dec 4, 2003 should be 12/4/2003 but the scheduled job imports itas 4/12/2003)Does anyone have a fix for this ?Thanks

View 1 Replies View Related

How To I Run A DTS Package From A Stored Procedure? Thank

Jul 12, 2006

I created a DTS package to transfer data from a remote database into my local database, I want to run this DTS in my stored procedure, can I do that?

Please help me, thanks a lot

View 7 Replies View Related

Dts Package - Stored Procedure

Aug 10, 2007

I created a dts package and I can execute it.
I want to include the dts package execution in a stored procedure, but I can't get the stored procedure to execute it from the cmdshell.
I have sql integration services and mssql 2005 services running under a domain account.
I have saved the package as a FILE System stored package.
I just can't find a reason why it won't execute from stored procedure.....

Ron

View 4 Replies View Related

Scheduling DTS Package Problem

Jul 3, 2002

If I am scheduling a DTS package from a client workstation, do I need a particular type of authority on the server? I'm asking because I was able to schedule packages, now I cannot and have made no changes on the client.

I assume since packages move int SQL Server Agent there is some authority required. We are using NT Authentication (SQL7/NT Server).

View 4 Replies View Related

Dts Package (local) And Scheduling

May 23, 2001

hello

I have the following problem with a local dts package which I created with a domain adminuser (domainadmdba) and runs ms oledb for oracle to execute access for a oracle database. thereafter the data fills up into a sqlserver table, this runs as local package perfectly. if i place the package into the scheduler and run the dts-job with an another domain user (domainsrv_sql=service account) i get connecting problems. the service account (domainsrv_sql) has sysadmin right in sqlserver and local administrator right on the operating system. which right must the service account possess, so that he has the necessary right to execute this dts package?

single dump from the log file:

with admdba:

The execution of the following DTS Package succeeded:

Package Name: DTS__000000006
Package Description: Import CUSPERS from ZMA
Package ID: {E13C874F-4ABF-11D5-92A4-0008C70D8D6E}
Package Version: {768B2A40-4ECE-11D5-92A4-0008C70D8D6E}
Package Execution Lineage: {9FCF25E5-4ED1-11D5-92A4-0008C70D8D6E}
Executed On: xxxZS270
Executed By: ADMDBA
Execution Started: 22/05/2001 6:44:58 PM
Execution Completed: 22/05/2001 6:45:09 PM

Package Steps execution information:

Step 'Delete from Table [HELPDESK].[dbo].[cuspers] Step' succeeded
Step 'Copy Data from Results to [HELPDESK].[dbo].[cuspers] Step' succeeded

with srv_sql

The execution of the following DTS Package ?succeeded?:

Package Name: DTS__000000006
Package Description: Import CUSPERS from ZMA
Package ID: {E13C874F-4ABF-11D5-92A4-0008C70D8D6E}
Package Version: {768B2A40-4ECE-11D5-92A4-0008C70D8D6E}
Package Execution Lineage: {AF7B9D41-4ECF-11D5-92A4-0008C70D8D6E}
Executed On: xxxZS270
Executed By: SRV_SQL
Execution Started: 5/22/01 6:30:00 PM
Execution Completed: 5/22/01 6:30:00 PM

Package Steps execution information:

Step 'Delete from Table [HELPDESK].[dbo].[cuspers] Step' succeeded
Step 'Copy Data from Results to [HELPDESK].[dbo].[cuspers] Step' failed

Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Unspecified error
(Microsoft OLE DB Provider for Oracle (80004005): The Oracle(tm) client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 (or greater) client software installation.

You will be unable to use this provider until these components have been installed.)
Step Error code: 80074005
Step Error Help File:sqldts.hlp
Step Error Help Context ID:1100

Thanks for any help !

Greets Indio

View 1 Replies View Related

Shopping For DTS Package Scheduling Help

Mar 3, 2004

Good Afternoon

First time shopper on this board...hopefully you can help me.

I am trying to figure out how to schedule a DTS package located on a Server to run at a certain time. The DTS package will contain the path to a V/B 6 executable file residing on the same server.

So to test my methodology, I made a package on a server with code to run the V/B 6 executable off of my local machine. When executing the
package manually it works fine. However, when I schedule it the job does not start. I could not find a way to edit the job I had previously scheduled so I made another one in case I "fat fingered" something.
The same thing happens...no job starts.

Any ideas?

Thanks,
Ed

View 3 Replies View Related

SSIS Package Scheduling

Apr 3, 2008

Hi,

I have created the package in a local machine and would like to schedule the package without integration services.

Is there any workaround for the above scenario.

Thanks in advance

Regards
Ramesh.R

View 4 Replies View Related

Scheduling SSIS Package In SQL JOB

May 12, 2008

Hi All

I'm new in SSIS Development. I have manage to design and deploy my Package to production SQL(MSDB) Security rely on server storage.

The problem start when I start scheduling in SQL JOB I'm getting the following error.


Executed as user: UCC-SQLSYSTEM. ...ion 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:57:01 AM Error: 2008-05-12 08:57:02.08 Code: 0xC0202009 Source: Test_deployment_Table Connection manager "BLAKE-DBN12.Staging" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Communication link failure". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "TCP Provider: An existing connection was forcibly closed by the remote host. ". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'BLAKEUCC-SQL$'.". End Error Error: 2008-05-12 08:57:02.08 Code: 0xC020801C Source: Data Flow Task Test_Deployment [97] ... The package execution fa... The step failed.

What can I do to avoid this.

View 2 Replies View Related

Scheduling SSIS Package

Jan 15, 2008

Hi All

I have created a SSIS Package to complete various tasks and I have managed to deploy it on SQL, however I can't find it in the Management Studio. I just want to be able to schedule this package to run as a job.

Any Ideas?

View 2 Replies View Related

Scheduling A Legacy DTS Package

Mar 24, 2006

I have a legacy DTS package on my test SQL Server 2005 in the ManagementLegacyData Transformation Services folder. I can run the package, but how can I schedule it?? this doesn't appear to be an option anymore like it was in 2000.

Thanks





View 4 Replies View Related

Scheduling SSIS Package

Feb 13, 2006

Hi ,

How do I schedule SSIS package.

View 6 Replies View Related

Execution Of DTS Package After Scheduling.

Sep 13, 2006

Hi All,

I have a problem in getting the info about the execution of a DTS Package.

I have a SQL-2000 Client installation on my Desktop to connect to SQL-2000 Server Database system to do all of my work. i had a DTS package stored in my SQL Server2000 Local Packages.  Now i have to schedule the DTS Package from my Desktop. And the DTS package is processing some files from a shared network folder. when i used to run the DTS package manually from my desktop my desktop login have access to that shared folder so there is no problem to run the package.

suppose i scheduled the DTS to execute on daily basis at 10:00 AM. Now my doubt is wher the DTS will be executed after scheduling, which starts DTS execution and is it required my Desktop to be up daily at 10:00 AM, and how the network shared folder is going to be accessed by the DTS, means where to specify the authentication to the shared folder.

somebudy plz help me out.

Thanks in advance.

View 2 Replies View Related

Scheduling SSIS Package

Dec 27, 2005

Hi

I am trying to scheduling package using "Executing Sql Server Agent Task" before that I create new job,But there is error in STEP "Command Line Parameter are Invalid"

Anybody help me for configure jobs for Scheduling

I am new to SQL Server

Thanks in Advance

  

View 5 Replies View Related

Scheduling The SSIS Package

May 30, 2006

Hi,

I want to schedule an SSIS package to run always. So I tried scheduling the SSIS Package as a Job in the SQL Server Agent and I have set the Schedule Type as "Start automatically when SQL Server Agent Starts".

I have used an WMI Event Watcher task to raise an event when the file dropped in a folder.

And I have provided the configuration file also properly in the job. But still my package is not running properly. The application is running, but it is not raising any events even when the files are dropped in that folder.

Pls anyone of you provide some solution for this.

Thanks & Regards,

Prakash Srinivasan

View 16 Replies View Related

Scheduling An Encrypted Package

Apr 20, 2007

I have a package that I have set the protection level to EncryptSensitiveWithPassword. How do I setup SQL Agent to run this package. I have checked books online and google and can't find a decent article that walks you through the steps of setting it up...specifically how to use the /DECRYPT switch with sql agent.

View 19 Replies View Related

SSIS Package Scheduling

Feb 8, 2008

Hi
I have a strange scheduling problem.
I have a job, i test it through BIDS - Works
I test it through SSMS - works
I run the job - Works
I schedule for a run time job - Works
I schedule for a recurring job - Works

However (here is the really annoying part) it fails to run at the night run (11.20PM)
there are 6 other packages that run fine after it , 11.30 , 1AM .. 4 AM etc ...

So
last night I changed the package to run at a later time, and it worked, but the other package that
ran 2nd (which now ran 1st) failed !!

its almost as if the 1st package has to fail !!!!!
When i look at the history and detailed log all it says is "The step Failed"

anyway , I can set up a workaround by starting a dummy job that is supposed to fail..

FYI the jobs are run under administrator and they are protected by a password (encryptSensitiveWithpassword) .

View 6 Replies View Related

How To Invoke DTS Package From A Stored Procedure

Jan 15, 2001

I have a DTS Package to populate data from excel sheet to direct simple table. I like to call this DTS from User interface. thanks in advance

View 2 Replies View Related

Calling A DTS Package From A Stored Procedure

Aug 30, 2000

I have a DTS package written to import data from a txt file into a SQL Server 7.0 table
but before importing the data i need to delete the existing data from the table...
for this i need to use the stored procedure to delete data first...
how do i run the dts package from the stored procedure..does anyone know the command for this...

regards,
reshma

View 2 Replies View Related

Execute DTS Package From A Stored Procedure?

Jun 27, 2001

When I run the code microsoft give to launch a DTS package from a stored procedure, the procedure runs continuously, never ending.

When I run the DTS package manually, or from a dtsrun utility, it only takes seconds.

I'm trying to automate this package so it will run after a field in a table has been updated.

Any suggestions are more than welcome.

PS. Here's the code I've been using (that doesn't seem to work):

--- Declare Variables
Declare @hr int
Declare @oPkg int

--- Create Package Object
Exec @hr = sp_OACreate 'DTS.Package', @oPkg OUT
If @hr <> 0
Begin
Print '*** Create Package Object Failed'
Exec sp_oageterrorinfo @oPkg, @hr
Return
End

--- Load Package
---DTSSQLStorageFlags :
---DTSSQLFlag_Default = 0
---DTSSQLStgFlag_UseTrustedConnection = 256

Exec @hr = sp_OAMEthod @oPkg,'LoadFromSqlServer("JENNSERVER", "", "", 256, , , , "RunMS")',null
If @hr <> 0
Begin
Print '*** Load Package Failed'
Exec sp_oageterrorinfo @oPkg, @hr
Return
End

--- Execute Package
Exec @hr = sp_OAMethod @oPkg, 'Execute'
If @hr <> 0
Begin
Print '*** Execute Failed'
Exec sp_oageterrorinfo @oPkg, @hr
Return
End

--- Clean Up Package
Exec @hr = sp_oadestroy @oPkg
If @hr <> 0
Begin
Print '*** Destroy Package Failed'
Exec sp_oageterrorinfo @oPkg, @hr
Return
End

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved