Scheduling Tasks

In Data Transformation Service, Local Packages,
I need to pull data from a SAGE Line 50 v9 database into SQL . I have
created a simple DTS Package to pull one of the tables and prove the concept
.
It is set
up so it drops the existing table, creates it again and then copies
the data across. It runs well when I run it as a package, however it
fails when I run it as a scheduled job.
It seems to drop the table, it then creates the table (this somehow tells me
that I
don't have a permissions problem because the table structure has to be
read from the SAGE database), but finally fails to copy the data.
Error
Step Error Description:The driver returned invalid (or failed to return)
SQL_DRIVER_ODBC_VER: 2.00 (Microsoft OLE DB Provider for ODBC Drivers
(80004005): Cannot find all files in data path)When you run a package interactively, it runs under YOUR permissions. When
you schedule a package it runs under the permissions for the login that SQL
Agent uses... Make sure the SQL Agent login has the appropriate permissions
everywhere
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"GEmsley" <GEmsley@.discussions.microsoft.com> wrote in message
news:56B83EDA-653F-4E3F-83FA-179A645F4824@.microsoft.com...
> In Data Transformation Service, Local Packages,
> I need to pull data from a SAGE Line 50 v9 database into SQL . I have
> created a simple DTS Package to pull one of the tables and prove the
concept.
> It is set
> up so it drops the existing table, creates it again and then copies
> the data across. It runs well when I run it as a package, however it
> fails when I run it as a scheduled job.
> It seems to drop the table, it then creates the table (this somehow tells
me
> that I
> don't have a permissions problem because the table structure has to be
> read from the SAGE database), but finally fails to copy the data.
> Error
> Step Error Description:The driver returned invalid (or failed to return)
> SQL_DRIVER_ODBC_VER: 2.00 (Microsoft OLE DB Provider for ODBC Drivers
> (80004005): Cannot find all files in data path)|||Thanks Wayne Spot on
"Wayne Snyder" wrote:

> When you run a package interactively, it runs under YOUR permissions. When
> you schedule a package it runs under the permissions for the login that SQ
L
> Agent uses... Make sure the SQL Agent login has the appropriate permission
s
> everywhere
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "GEmsley" <GEmsley@.discussions.microsoft.com> wrote in message
> news:56B83EDA-653F-4E3F-83FA-179A645F4824@.microsoft.com...
> concept.
> me
>
>sql

Scheduling Tasks

In Data Transformation Service, Local Packages,
I need to pull data from a SAGE Line 50 v9 database into SQL . I have
created a simple DTS Package to pull one of the tables and prove the concept.
It is set
up so it drops the existing table, creates it again and then copies
the data across. It runs well when I run it as a package, however it
fails when I run it as a scheduled job.
It seems to drop the table, it then creates the table (this somehow tells me
that I
don't have a permissions problem because the table structure has to be
read from the SAGE database), but finally fails to copy the data.
Error
Step Error Description:The driver returned invalid (or failed to return)
SQL_DRIVER_ODBC_VER: 2.00 (Microsoft OLE DB Provider for ODBC Drivers
(80004005): Cannot find all files in data path)When you run a package interactively, it runs under YOUR permissions. When
you schedule a package it runs under the permissions for the login that SQL
Agent uses... Make sure the SQL Agent login has the appropriate permissions
everywhere
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"GEmsley" <GEmsley@.discussions.microsoft.com> wrote in message
news:56B83EDA-653F-4E3F-83FA-179A645F4824@.microsoft.com...
> In Data Transformation Service, Local Packages,
> I need to pull data from a SAGE Line 50 v9 database into SQL . I have
> created a simple DTS Package to pull one of the tables and prove the
concept.
> It is set
> up so it drops the existing table, creates it again and then copies
> the data across. It runs well when I run it as a package, however it
> fails when I run it as a scheduled job.
> It seems to drop the table, it then creates the table (this somehow tells
me
> that I
> don't have a permissions problem because the table structure has to be
> read from the SAGE database), but finally fails to copy the data.
> Error
> Step Error Description:The driver returned invalid (or failed to return)
> SQL_DRIVER_ODBC_VER: 2.00 (Microsoft OLE DB Provider for ODBC Drivers
> (80004005): Cannot find all files in data path)|||Thanks Wayne Spot on
"Wayne Snyder" wrote:
> When you run a package interactively, it runs under YOUR permissions. When
> you schedule a package it runs under the permissions for the login that SQL
> Agent uses... Make sure the SQL Agent login has the appropriate permissions
> everywhere
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "GEmsley" <GEmsley@.discussions.microsoft.com> wrote in message
> news:56B83EDA-653F-4E3F-83FA-179A645F4824@.microsoft.com...
> > In Data Transformation Service, Local Packages,
> > I need to pull data from a SAGE Line 50 v9 database into SQL . I have
> > created a simple DTS Package to pull one of the tables and prove the
> concept.
> > It is set
> > up so it drops the existing table, creates it again and then copies
> > the data across. It runs well when I run it as a package, however it
> > fails when I run it as a scheduled job.
> > It seems to drop the table, it then creates the table (this somehow tells
> me
> > that I
> > don't have a permissions problem because the table structure has to be
> > read from the SAGE database), but finally fails to copy the data.
> >
> > Error
> > Step Error Description:The driver returned invalid (or failed to return)
> > SQL_DRIVER_ODBC_VER: 2.00 (Microsoft OLE DB Provider for ODBC Drivers
> > (80004005): Cannot find all files in data path)
>
>

Scheduling Tasks

In Data Transformation Service, Local Packages,
I need to pull data from a SAGE Line 50 v9 database into SQL . I have
created a simple DTS Package to pull one of the tables and prove the concept.
It is set
up so it drops the existing table, creates it again and then copies
the data across. It runs well when I run it as a package, however it
fails when I run it as a scheduled job.
It seems to drop the table, it then creates the table (this somehow tells me
that I
don't have a permissions problem because the table structure has to be
read from the SAGE database), but finally fails to copy the data.
Error
Step Error Description:The driver returned invalid (or failed to return)
SQL_DRIVER_ODBC_VER: 2.00 (Microsoft OLE DB Provider for ODBC Drivers
(80004005): Cannot find all files in data path)
When you run a package interactively, it runs under YOUR permissions. When
you schedule a package it runs under the permissions for the login that SQL
Agent uses... Make sure the SQL Agent login has the appropriate permissions
everywhere
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"GEmsley" <GEmsley@.discussions.microsoft.com> wrote in message
news:56B83EDA-653F-4E3F-83FA-179A645F4824@.microsoft.com...
> In Data Transformation Service, Local Packages,
> I need to pull data from a SAGE Line 50 v9 database into SQL . I have
> created a simple DTS Package to pull one of the tables and prove the
concept.
> It is set
> up so it drops the existing table, creates it again and then copies
> the data across. It runs well when I run it as a package, however it
> fails when I run it as a scheduled job.
> It seems to drop the table, it then creates the table (this somehow tells
me
> that I
> don't have a permissions problem because the table structure has to be
> read from the SAGE database), but finally fails to copy the data.
> Error
> Step Error Description:The driver returned invalid (or failed to return)
> SQL_DRIVER_ODBC_VER: 2.00 (Microsoft OLE DB Provider for ODBC Drivers
> (80004005): Cannot find all files in data path)
|||Thanks Wayne Spot on
"Wayne Snyder" wrote:

> When you run a package interactively, it runs under YOUR permissions. When
> you schedule a package it runs under the permissions for the login that SQL
> Agent uses... Make sure the SQL Agent login has the appropriate permissions
> everywhere
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "GEmsley" <GEmsley@.discussions.microsoft.com> wrote in message
> news:56B83EDA-653F-4E3F-83FA-179A645F4824@.microsoft.com...
> concept.
> me
>
>

Scheduling Data Transformation Services?

Hi,
Our vendor created a series of dts local packages and I'd like to have them
run each night. When I right-click one of the packages and select Schedule
Package it comes up with a default schedule. So it looks like it is already
schedule, which is fine but how do I know if the package was run? Is this
activity tracked in a log somewhere? When I look under Package Log I see
it's empty. Any suggestions?
Thanks in advance,
LinnIn enterprize manager, go to
Management / SQL Server Agent / Jobs
From there you can view job history and edit the job itself.. as well as the
schedule.
"Linn Kubler" <lkubler@.chartwellwisc2.com> wrote in message
news:uBL6RelMGHA.3432@.tk2msftngp13.phx.gbl...
> Hi,
> Our vendor created a series of dts local packages and I'd like to have
> them run each night. When I right-click one of the packages and select
> Schedule Package it comes up with a default schedule. So it looks like it
> is already schedule, which is fine but how do I know if the package was
> run? Is this activity tracked in a log somewhere? When I look under
> Package Log I see it's empty. Any suggestions?
> Thanks in advance,
> Linn
>|||First..I assume you are using ss2k.
If you open up the DTS package, and click Package, Proprties..and the
Logging tab, you will see in the bottom how to create an error file.
You could also set up a task to email yourself when the task completes
successfully(of course for this to work your server must be configured
to use Exchange.)
MJKulangara
http://sqladventures.blogspot.com

Scheduling a legacy DTS package

I have a legacy DTS package on my test SQL Server 2005 in the Management\Legacy\Data 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

Use the command-line option in SQL Agent and use DTSRun.

[I am assuming at this point that DTSRun is still distributed. Apologies if not]

-Jamie

|||

it is but it's called dtexec now, still doesn't work mind,

if I save the package as a .dts files and try to run it with

C:\>dtexec /f "c:\poval.dts"

I get:

--

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 14:32:26
Error: 2006-03-24 14:32:26.95
Code: 0xC0011007
Source: {F6D6F0F3-30A1-40F6-94B5-D14419672776}
Description: Unable to load the package as XML because of package does not ha
ve a valid XML format. A specific XML parser error will be posted.
End Error
Error: 2006-03-24 14:32:26.95
Code: 0xC0011002
Source: {F6D6F0F3-30A1-40F6-94B5-D14419672776}
Description: Failed to open package file "c:\poval.dts" due to error 0xC00CE5
08 "An invalid character was found in text content.". This happens when loading
a package and the file cannot be opened or loaded correctly into the XML docume
nt. This can be the result of either providing an incorrect file name was specif
ied when calling LoadPackage or the XML file was specified and has an incorrect
format.
End Error
Could not load package "c:\poval.dts" because of error 0xC0011002.
Description: Failed to open package file "c:\poval.dts" due to error 0xC00CE508
"An invalid character was found in text content.". This happens when loading a
package and the file cannot be opened or loaded correctly into the XML document.
This can be the result of either providing an incorrect file name was specified
when calling LoadPackage or the XML file was specified and has an incorrect for
mat.
Source: {F6D6F0F3-30A1-40F6-94B5-D14419672776}
Started: 14:32:26
Finished: 14:32:27
Elapsed: 0.218 seconds

-

This, I suspect, is because there are invalid characters somewhere in the package, although it's odd that it runs fine when I run it from the DTS designer itself? Therefore I must apologise to Microsoft for not having the foresight not to include any characters that may one day be invalid when I wrote the DTS package about 4 years ago....

and If I attempt to run it directly from the server using

C:\>dtexec /SQL POVal

I get:-

-

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 14:39:16
Could not load package "POVal" because of error 0xC001000A.
Description: The specified package could not be loaded from the SQL Server datab
ase.
Source:
Started: 14:39:16
Finished: 14:39:17
Elapsed: 0.484 seconds

Am I to assume from this that the legacy packages aren't stored in msdb , Can anyone tell me where my package is then!?

I've had enough today

|||

You cannot run a DTS2000 package with DTExec.exe. You need DTSRun.exe.

-Jamie

|||d'oh, cheers