Scheduling DTS Package using a stored procedure problem

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
\\ServerName\folder\filename.xls', no_output
IF (@.findfile=0)
BEGIN
Exec master.dbo.xp_cmdshell 'dtsrun -E -Server\Instance
-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

KRI found the problem was the path in the log file : was using a mapped
drive vs a UNC path.

Scheduling does not work!

Hi Guys
On one of my RS Servers the scheduling does not work. That is the "schedule"
is created. It even exists as a job in SQL Server that runs however the
actual report is not run. In the log file I have the following entries:
ReportingServicesService!library!10ac!11/08/2004-18:04:22:: e ERROR:
Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDisabledExcept
ion: The report server cannot decrypt the symmetric key used to access
sensitive or encrypted data in a report server database. You must either
restore a backup key or delete all encrypted content and then restart the
service. Check the documentation for more information., ;
Info:
Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDisabledExcept
ion: The report server cannot decrypt the symmetric key used to access
sensitive or encrypted data in a report server database. You must either
restore a backup key or delete all encrypted content and then restart the
service. Check the documentation for more information. -->
System.Runtime.InteropServices.COMException (0x80090005): Bad Data.
at System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32
errorCode, IntPtr errorInfo)
at RSManagedCrypto.RSCrypto.ImportSymmetricKey(Byte[] pSymKeyBlob)
at
Microsoft.ReportingServices.Library.ConnectionManager.GetEncryptionKey()
Does anyone have any ideas as how to fix this problem?
(I have deleted all schedules and having the server rebooted tonight but
dont know if that will fix the problem).
Thanks.
Regards
JonasDid you recently change the user that the reportserver service runs under?
Did you back up your symmetric key?
If you changed the user but did not back up the symmetric key, then change
the user back to the original and use rskeymgmt to back up the symmetric
key. Change the user again to what you want and user rskeymgmt to insert
the symmetric key.
If you have already saved your symmetric key then just insert it using
rskeymgmt.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:ezvNqMBgEHA.2536@.TK2MSFTNGP09.phx.gbl...
> Hi Guys
> On one of my RS Servers the scheduling does not work. That is the
"schedule"
> is created. It even exists as a job in SQL Server that runs however the
> actual report is not run. In the log file I have the following entries:
> ReportingServicesService!library!10ac!11/08/2004-18:04:22:: e ERROR:
> Throwing
>
Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDisabledExcept
> ion: The report server cannot decrypt the symmetric key used to access
> sensitive or encrypted data in a report server database. You must either
> restore a backup key or delete all encrypted content and then restart the
> service. Check the documentation for more information., ;
> Info:
>
Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDisabledExcept
> ion: The report server cannot decrypt the symmetric key used to access
> sensitive or encrypted data in a report server database. You must either
> restore a backup key or delete all encrypted content and then restart the
> service. Check the documentation for more information. -->
> System.Runtime.InteropServices.COMException (0x80090005): Bad Data.
> at System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32
> errorCode, IntPtr errorInfo)
> at RSManagedCrypto.RSCrypto.ImportSymmetricKey(Byte[] pSymKeyBlob)
> at
> Microsoft.ReportingServices.Library.ConnectionManager.GetEncryptionKey()
> Does anyone have any ideas as how to fix this problem?
> (I have deleted all schedules and having the server rebooted tonight but
> dont know if that will fix the problem).
> Thanks.
> Regards
> Jonas
>|||Thanks. Problem solved.
Next problem. Due to this issue I now have 2 jobs on my SQLServer created by
Reporting Services that does not show in RS. Can I just delete these jobs
or?
Regards
Jonas
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:%23fN5hFJgEHA.536@.TK2MSFTNGP11.phx.gbl...
> Did you recently change the user that the reportserver service runs under?
> Did you back up your symmetric key?
> If you changed the user but did not back up the symmetric key, then change
> the user back to the original and use rskeymgmt to back up the symmetric
> key. Change the user again to what you want and user rskeymgmt to insert
> the symmetric key.
> If you have already saved your symmetric key then just insert it using
> rskeymgmt.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
> news:ezvNqMBgEHA.2536@.TK2MSFTNGP09.phx.gbl...
> > Hi Guys
> >
> > On one of my RS Servers the scheduling does not work. That is the
> "schedule"
> > is created. It even exists as a job in SQL Server that runs however the
> > actual report is not run. In the log file I have the following entries:
> >
> > ReportingServicesService!library!10ac!11/08/2004-18:04:22:: e ERROR:
> > Throwing
> >
>
Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDisabledExcept
> > ion: The report server cannot decrypt the symmetric key used to access
> > sensitive or encrypted data in a report server database. You must either
> > restore a backup key or delete all encrypted content and then restart
the
> > service. Check the documentation for more information., ;
> > Info:
> >
>
Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDisabledExcept
> > ion: The report server cannot decrypt the symmetric key used to access
> > sensitive or encrypted data in a report server database. You must either
> > restore a backup key or delete all encrypted content and then restart
the
> > service. Check the documentation for more information. -->
> > System.Runtime.InteropServices.COMException (0x80090005): Bad Data.
> > at System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32
> > errorCode, IntPtr errorInfo)
> > at RSManagedCrypto.RSCrypto.ImportSymmetricKey(Byte[] pSymKeyBlob)
> > at
> > Microsoft.ReportingServices.Library.ConnectionManager.GetEncryptionKey()
> >
> > Does anyone have any ideas as how to fix this problem?
> > (I have deleted all schedules and having the server rebooted tonight but
> > dont know if that will fix the problem).
> >
> > Thanks.
> >
> > Regards
> > Jonas
> >
> >
>|||Yes, RS will recreate jobs if they are deleted from SQL Agent and RS still
needs them. By default it will check this every 12 hours. You can force
this to happen by recycling the ReportServer service.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:OG$#KRMgEHA.4024@.TK2MSFTNGP10.phx.gbl...
> Thanks. Problem solved.
> Next problem. Due to this issue I now have 2 jobs on my SQLServer created
by
> Reporting Services that does not show in RS. Can I just delete these jobs
> or?
> Regards
> Jonas
> "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> news:%23fN5hFJgEHA.536@.TK2MSFTNGP11.phx.gbl...
> > Did you recently change the user that the reportserver service runs
under?
> > Did you back up your symmetric key?
> >
> > If you changed the user but did not back up the symmetric key, then
change
> > the user back to the original and use rskeymgmt to back up the symmetric
> > key. Change the user again to what you want and user rskeymgmt to
insert
> > the symmetric key.
> >
> > If you have already saved your symmetric key then just insert it using
> > rskeymgmt.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
> > news:ezvNqMBgEHA.2536@.TK2MSFTNGP09.phx.gbl...
> > > Hi Guys
> > >
> > > On one of my RS Servers the scheduling does not work. That is the
> > "schedule"
> > > is created. It even exists as a job in SQL Server that runs however
the
> > > actual report is not run. In the log file I have the following
entries:
> > >
> > > ReportingServicesService!library!10ac!11/08/2004-18:04:22:: e ERROR:
> > > Throwing
> > >
> >
>
Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDisabledExcept
> > > ion: The report server cannot decrypt the symmetric key used to access
> > > sensitive or encrypted data in a report server database. You must
either
> > > restore a backup key or delete all encrypted content and then restart
> the
> > > service. Check the documentation for more information., ;
> > > Info:
> > >
> >
>
Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDisabledExcept
> > > ion: The report server cannot decrypt the symmetric key used to access
> > > sensitive or encrypted data in a report server database. You must
either
> > > restore a backup key or delete all encrypted content and then restart
> the
> > > service. Check the documentation for more information. -->
> > > System.Runtime.InteropServices.COMException (0x80090005): Bad Data.
> > > at System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32
> > > errorCode, IntPtr errorInfo)
> > > at RSManagedCrypto.RSCrypto.ImportSymmetricKey(Byte[] pSymKeyBlob)
> > > at
> > >
Microsoft.ReportingServices.Library.ConnectionManager.GetEncryptionKey()
> > >
> > > Does anyone have any ideas as how to fix this problem?
> > > (I have deleted all schedules and having the server rebooted tonight
but
> > > dont know if that will fix the problem).
> > >
> > > Thanks.
> > >
> > > Regards
> > > Jonas
> > >
> > >
> >
> >
>

Scheduling approach

We user SQL sever 2005, I have a reports runs very slow since big chunk
of data, I just want to run it once a day. This report has few
selections fields, for different people they want to see different
selection criteria, is there a way I can just schedule it once for wide
open selection and each person based on this generic report to narrow
down to their own small set of data? thanksI use email to notify the end user for the "new" genereated report, but
why each time the user click the link, instead of giving the snapshot,
the link do the RE-Generat report? how do i let user only see the
snapshot, it is a big report.|||On the report options, set the execution to execute from a snapshot and set
the snapshot creation for a quiet time to avoid impacting OLTP. Then set up a
subscription to run when content is refreshed (available only for snapshot
reports). You will need to set up default parameters, but if chosen to return
NULL data, you effectively get an email with a notice of report content
update with a dummy report attached.
"Wang Xiaoning" wrote:
> I use email to notify the end user for the "new" genereated report, but
> why each time the user click the link, instead of giving the snapshot,
> the link do the RE-Generat report? how do i let user only see the
> snapshot, it is a big report.
>sql

Scheduling an SSIS package that invokes a web service

Hey,

I have an SSIS package that invokes a web service and then updates a table. It runs fine as long as I am running it on the local machine. However, as soon as I save this package to the sql server, and try to schedule this as a job, it starts to fail. Now, the web service writes to an xml file and also uses an xsd and and an xsl file. When I save a dts package to the sql server, whats the proper way of referencing these files? I think this probably is what is making the package to fail, ut I am not sure.

Any help is greatly appreciated!!

Thanks!

You should use a configuration (right-click in the package and choose configurations) to set the ConnectionString property of the connection managers for the files. Or you could use expressions to set the connection strings (paths and filenames) based on variables. The variables can be set at runtime using the /SET option of DTEXEC.|||Also make sure you've configured SSIS logging, so you can find out why the package fails now or (once you fix the problem and go to production) if something goes wrong with scheduled package in production.|||That depends on where you want to keep them. I prefer to keep them in files on the disk. If your package is in SQL and you prefer to avoid the disk entirely, you can keep them in the database and just load them into variables via the Execute SQL task. The XML task and the XML Source component support receiving the XSD/XSLT from variables.
|||Thanks a lot for the suggestions. I will try them out and see how it works.|||

Hey,

Sorry for this delayed reply. Since I posted this question a lot of issues cropped up with my SQL server which eventually led to a total reinstallation of all apps on my pc. Anyway, I discovered that the problem I have been having was because of permission issues. I was able to fix that problem and just when I thought that I had everything going, I came across a new problem. After I save the SSIS package in sql server and create a job, the job starts failing. This is the error that I am getting:

-1073548540,0x,An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: The Web Service threw an error during method execution. The error is: Unable to connect to the remote server.

Any suggestions would be very helpful.

Thanks!

|||Could it be a authentication issue? Is there any security on the web service?|||

Guys!!Thanks a lot for all the suggestions. Really appreciate your help. Problem was a combination of many issues. One was related to 32bit/64bit differences, the other was authentication, and finally some syntax problems when invoking the web service. Seems it is working really well now.

Thanks again!

Scheduling an sp

Hi All,
I hope I can explain this well.
I have an sp that populates certain tables with historical data and I've
scheduled
a job (using EM) that runs this sp on the first day of each quarter.
My question is, within the sp, if anything goes wrong, I do a rollback and
issue
a Return(5). Will this be interpeted as a failure within the job? Somehow I
think not.
So, how do I indicate failure to the job that schedules the sp?
Dan
Dan,
Add a RAISERROR statement to your stored procedure.
HTH
Jerry
"dan artuso" <dartuso@.NOSPAMpagepearls.com> wrote in message
news:OjXqmJfxFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> I hope I can explain this well.
> I have an sp that populates certain tables with historical data and I've
> scheduled
> a job (using EM) that runs this sp on the first day of each quarter.
> My question is, within the sp, if anything goes wrong, I do a rollback and
> issue
> a Return(5). Will this be interpeted as a failure within the job? Somehow
> I think not.
> So, how do I indicate failure to the job that schedules the sp?
>
> Dan
>
>
|||Thanks Jerry
Dan
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u7haHMfxFHA.1148@.TK2MSFTNGP11.phx.gbl...
> Dan,
> Add a RAISERROR statement to your stored procedure.
> HTH
> Jerry
> "dan artuso" <dartuso@.NOSPAMpagepearls.com> wrote in message
> news:OjXqmJfxFHA.2924@.TK2MSFTNGP15.phx.gbl...
>

Scheduled SQL Server Agent SSIS Package Job Problem

HELP! I have been banging my head against a brick wall on this one all this morning AAAAAAGGGHHH!

1. I have an SSIS package that runs a simple SQL script and then updates a few tables by downloading some XML of the web. It runs fine when I kick it off manually under SSMS.

2. I created a SQL Server Agent job to run it every day. This always fails. The error information in the log is useless ("Executed as user: domain\user. The package execution failed. The step failed." - I had already figured that out!). It fails almost straight away, and when I enable logging for the SSIS package, no info is ever logged (text file, windows event log, whatever).

3. Out of desperation I have changed Agent to run under the same domain user account that I created the package with. No use.

My questions:

1. How can I get more detailed logging from SQL Server Agent?

2 Any ideas about why it's failing in the first place.

Many thanks in advance.

Ben

If you store the package in the filesystem you should give read/write and maybe execute permission on the file packagename.dtsx to the account sql server agent is running.

To all Microsoft people:

I propose to put on top of the forum one task with solutions for those permission issue as a lot of the posts are because of wrong permission settings. I had this problem too in the past and I would appreciate such a "top" post

Regards

Nobs

|||

Thanks for your quick response. The SSIS package is actually stored in SQL Server though (in Stored Packages->MSDB).

Any more ideas? How about the more detailed logging for SQL Server Agent?

Thanks again,

Ben

|||

Have a look at some issues here-

http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

If we get some answers (mark it as so) then I'll get it locked at the top, that is one of the goals for these forums.

|||

Thank you!

Running the SSIS package as a command line step instead of an SSIS Package step enabled me to obtain all the error info I needed to fix the problem.

Why does Microsoft not give full error information for SSIS packages run through the SQL Server Agent job scheduler (even in SP1)?!?!

Many thanks indeed for helping me to solve the problem.

Ben

|||

Give them the feeback- http://lab.msdn.microsoft.com/productfeedback/default.aspx

|||

Can you explain what "running as a command line step" means? I am having the same problem as you, my SSIS packages won't run as a SQL Agent job, and "the job failed" gives me no information.

Thanks.

|||

JustJFe wrote:

Can you explain what "running as a command line step" means? I am having the same problem as you, my SSIS packages won't run as a SQL Agent job, and "the job failed" gives me no information.

Thanks.

When you are creating a SQL Agent job; you have to create 'steps'; well, there is a dropdown list for 'Type' where you can select 'Operative System (CmdExec)' that is what "Running as a command line step" should mean. This approach is helpful under some scenarios like wehn you want to run your package in a 64-bit machine in a 32-bit mode.

BTW, SQL Agent is not that good providing error descriptions; but you can enable logging in you packages, so you can have more details.

I hope this clarify your doubts.

Rafael Salas

|||

Thanks for the quick response - I will change the Step as you listed - currently, it's an SSIS package type of step.

How do I "enable logging"? (My DBA is out on maternity leave, and I'm trying to cover - my VS skills are good, but SQL Server 2005 is Beginner...)

Thanks!

|||

My Suggestion about using logging may require changes in the packages; I would check fisrt if logging is not being already used first; the table SSIS uses for logging, by default, is sysdtslog90 but that could have been changed by a custom log table; in both cases is something that you can check by opening the packages.

Rafael Salas

|||

By default SQL Agent is not great at giving you output as you say, the View job History stuff is truncated. The whole point of my use CmdExec step recommendation, as illustrated in the link, is that you can then turn on the step level logging in SQL Agent, either to text file or SQL table. When using DTEXEC this means you get the same much the console output in your log files as you would get if running in BIDS looking in the output window, or exactly the same as if using DTEXEC from a command prompt and watching what comes out. SSIS logging is good if the package is running, but what happens if the package cannot be found, or permissions prevent access to the package file even? You will not get SSIS logging, or anything useful in the Job level history. The step log however will have the information you need. Check out the link I posted for an illustration of this which solved an annoying file permission issue for me, when the package could not even be loaded.

Personally I use both logging and a CmdExec steps with step logging.

|||In VS, the package has no logging options - I'm guess that means it's not turned on. Also, there is no table sysdtslog90 in our SQL Server. Does that help direct your answer?|||Thanks for the quick reply, but the link you posted does not have any information on how to set up logging or how to find where the messages are listed. I still only get "job failed" in the Job History screen.|||

DarrenSQLIS wrote:

SSIS logging is good if the package is running, but what happens if the package cannot be found, or permissions prevent access to the package file even? You will not get SSIS logging, or anything useful in the Job level history.

That is a good point...

I have not used the combination you described before: CmdExec and step level logging in SQL Server Agent but seems to be a powerfull tool to debug Agent execution issues.

Thanks

Rafael Salas

|||

Never mind the "Job History screen", can you find the Advanced tab on the job !STEP!, if so set some logging there.

To get started with SSIS logging, have a look on the SSIS menu in VS. You need to ensure the package has the focus to see the SSIS menu, it has a habit of hiding.

Scheduled SQL Server Agent SSIS Package Job Problem

HELP! I have been banging my head against a brick wall on this one all this morning AAAAAAGGGHHH!

1. I have an SSIS package that runs a simple SQL script and then updates a few tables by downloading some XML of the web. It runs fine when I kick it off manually under SSMS.

2. I created a SQL Server Agent job to run it every day. This always fails. The error information in the log is useless ("Executed as user: domain\user. The package execution failed. The step failed." - I had already figured that out!). It fails almost straight away, and when I enable logging for the SSIS package, no info is ever logged (text file, windows event log, whatever).

3. Out of desperation I have changed Agent to run under the same domain user account that I created the package with. No use.

My questions:

1. How can I get more detailed logging from SQL Server Agent?

2 Any ideas about why it's failing in the first place.

Many thanks in advance.

Ben

If you store the package in the filesystem you should give read/write and maybe execute permission on the file packagename.dtsx to the account sql server agent is running.

To all Microsoft people:

I propose to put on top of the forum one task with solutions for those permission issue as a lot of the posts are because of wrong permission settings. I had this problem too in the past and I would appreciate such a "top" post

Regards

Nobs

|||

Thanks for your quick response. The SSIS package is actually stored in SQL Server though (in Stored Packages->MSDB).

Any more ideas? How about the more detailed logging for SQL Server Agent?

Thanks again,

Ben

|||

Have a look at some issues here-

http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

If we get some answers (mark it as so) then I'll get it locked at the top, that is one of the goals for these forums.

|||

Thank you!

Running the SSIS package as a command line step instead of an SSIS Package step enabled me to obtain all the error info I needed to fix the problem.

Why does Microsoft not give full error information for SSIS packages run through the SQL Server Agent job scheduler (even in SP1)?!?!

Many thanks indeed for helping me to solve the problem.

Ben

|||

Give them the feeback- http://lab.msdn.microsoft.com/productfeedback/default.aspx

|||

Can you explain what "running as a command line step" means? I am having the same problem as you, my SSIS packages won't run as a SQL Agent job, and "the job failed" gives me no information.

Thanks.

|||

JustJFe wrote:

Can you explain what "running as a command line step" means? I am having the same problem as you, my SSIS packages won't run as a SQL Agent job, and "the job failed" gives me no information.

Thanks.

When you are creating a SQL Agent job; you have to create 'steps'; well, there is a dropdown list for 'Type' where you can select 'Operative System (CmdExec)' that is what "Running as a command line step" should mean. This approach is helpful under some scenarios like wehn you want to run your package in a 64-bit machine in a 32-bit mode.

BTW, SQL Agent is not that good providing error descriptions; but you can enable logging in you packages, so you can have more details.

I hope this clarify your doubts.

Rafael Salas

|||

Thanks for the quick response - I will change the Step as you listed - currently, it's an SSIS package type of step.

How do I "enable logging"? (My DBA is out on maternity leave, and I'm trying to cover - my VS skills are good, but SQL Server 2005 is Beginner...)

Thanks!

|||

My Suggestion about using logging may require changes in the packages; I would check fisrt if logging is not being already used first; the table SSIS uses for logging, by default, is sysdtslog90 but that could have been changed by a custom log table; in both cases is something that you can check by opening the packages.

Rafael Salas

|||

By default SQL Agent is not great at giving you output as you say, the View job History stuff is truncated. The whole point of my use CmdExec step recommendation, as illustrated in the link, is that you can then turn on the step level logging in SQL Agent, either to text file or SQL table. When using DTEXEC this means you get the same much the console output in your log files as you would get if running in BIDS looking in the output window, or exactly the same as if using DTEXEC from a command prompt and watching what comes out. SSIS logging is good if the package is running, but what happens if the package cannot be found, or permissions prevent access to the package file even? You will not get SSIS logging, or anything useful in the Job level history. The step log however will have the information you need. Check out the link I posted for an illustration of this which solved an annoying file permission issue for me, when the package could not even be loaded.

Personally I use both logging and a CmdExec steps with step logging.

|||In VS, the package has no logging options - I'm guess that means it's not turned on. Also, there is no table sysdtslog90 in our SQL Server. Does that help direct your answer?|||Thanks for the quick reply, but the link you posted does not have any information on how to set up logging or how to find where the messages are listed. I still only get "job failed" in the Job History screen.|||

DarrenSQLIS wrote:

SSIS logging is good if the package is running, but what happens if the package cannot be found, or permissions prevent access to the package file even? You will not get SSIS logging, or anything useful in the Job level history.

That is a good point...

I have not used the combination you described before: CmdExec and step level logging in SQL Server Agent but seems to be a powerfull tool to debug Agent execution issues.

Thanks

Rafael Salas

|||

Never mind the "Job History screen", can you find the Advanced tab on the job !STEP!, if so set some logging there.

To get started with SSIS logging, have a look on the SSIS menu in VS. You need to ensure the package has the focus to see the SSIS menu, it has a habit of hiding.

Scheduled SQL Server Agent SSIS Package Job Problem

HELP! I have been banging my head against a brick wall on this one all this morning AAAAAAGGGHHH!

1. I have an SSIS package that runs a simple SQL script and then updates a few tables by downloading some XML of the web. It runs fine when I kick it off manually under SSMS.

2. I created a SQL Server Agent job to run it every day. This always fails. The error information in the log is useless ("Executed as user: domain\user. The package execution failed. The step failed." - I had already figured that out!). It fails almost straight away, and when I enable logging for the SSIS package, no info is ever logged (text file, windows event log, whatever).

3. Out of desperation I have changed Agent to run under the same domain user account that I created the package with. No use.

My questions:

1. How can I get more detailed logging from SQL Server Agent?

2 Any ideas about why it's failing in the first place.

Many thanks in advance.

Ben

If you store the package in the filesystem you should give read/write and maybe execute permission on the file packagename.dtsx to the account sql server agent is running.

To all Microsoft people:

I propose to put on top of the forum one task with solutions for those permission issue as a lot of the posts are because of wrong permission settings. I had this problem too in the past and I would appreciate such a "top" post

Regards

Nobs

|||

Thanks for your quick response. The SSIS package is actually stored in SQL Server though (in Stored Packages->MSDB).

Any more ideas? How about the more detailed logging for SQL Server Agent?

Thanks again,

Ben

|||

Have a look at some issues here-

http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

If we get some answers (mark it as so) then I'll get it locked at the top, that is one of the goals for these forums.

|||

Thank you!

Running the SSIS package as a command line step instead of an SSIS Package step enabled me to obtain all the error info I needed to fix the problem.

Why does Microsoft not give full error information for SSIS packages run through the SQL Server Agent job scheduler (even in SP1)?!?!

Many thanks indeed for helping me to solve the problem.

Ben

|||

Give them the feeback- http://lab.msdn.microsoft.com/productfeedback/default.aspx

|||

Can you explain what "running as a command line step" means? I am having the same problem as you, my SSIS packages won't run as a SQL Agent job, and "the job failed" gives me no information.

Thanks.

|||

JustJFe wrote:

Can you explain what "running as a command line step" means? I am having the same problem as you, my SSIS packages won't run as a SQL Agent job, and "the job failed" gives me no information.

Thanks.

When you are creating a SQL Agent job; you have to create 'steps'; well, there is a dropdown list for 'Type' where you can select 'Operative System (CmdExec)' that is what "Running as a command line step" should mean. This approach is helpful under some scenarios like wehn you want to run your package in a 64-bit machine in a 32-bit mode.

BTW, SQL Agent is not that good providing error descriptions; but you can enable logging in you packages, so you can have more details.

I hope this clarify your doubts.

Rafael Salas

|||

Thanks for the quick response - I will change the Step as you listed - currently, it's an SSIS package type of step.

How do I "enable logging"? (My DBA is out on maternity leave, and I'm trying to cover - my VS skills are good, but SQL Server 2005 is Beginner...)

Thanks!

|||

My Suggestion about using logging may require changes in the packages; I would check fisrt if logging is not being already used first; the table SSIS uses for logging, by default, is sysdtslog90 but that could have been changed by a custom log table; in both cases is something that you can check by opening the packages.

Rafael Salas

|||

By default SQL Agent is not great at giving you output as you say, the View job History stuff is truncated. The whole point of my use CmdExec step recommendation, as illustrated in the link, is that you can then turn on the step level logging in SQL Agent, either to text file or SQL table. When using DTEXEC this means you get the same much the console output in your log files as you would get if running in BIDS looking in the output window, or exactly the same as if using DTEXEC from a command prompt and watching what comes out. SSIS logging is good if the package is running, but what happens if the package cannot be found, or permissions prevent access to the package file even? You will not get SSIS logging, or anything useful in the Job level history. The step log however will have the information you need. Check out the link I posted for an illustration of this which solved an annoying file permission issue for me, when the package could not even be loaded.

Personally I use both logging and a CmdExec steps with step logging.

|||In VS, the package has no logging options - I'm guess that means it's not turned on. Also, there is no table sysdtslog90 in our SQL Server. Does that help direct your answer?|||Thanks for the quick reply, but the link you posted does not have any information on how to set up logging or how to find where the messages are listed. I still only get "job failed" in the Job History screen.|||

DarrenSQLIS wrote:

SSIS logging is good if the package is running, but what happens if the package cannot be found, or permissions prevent access to the package file even? You will not get SSIS logging, or anything useful in the Job level history.

That is a good point...

I have not used the combination you described before: CmdExec and step level logging in SQL Server Agent but seems to be a powerfull tool to debug Agent execution issues.

Thanks

Rafael Salas

|||

Never mind the "Job History screen", can you find the Advanced tab on the job !STEP!, if so set some logging there.

To get started with SSIS logging, have a look on the SSIS menu in VS. You need to ensure the package has the focus to see the SSIS menu, it has a habit of hiding.

sql

Scheduled SQL Server Agent SSIS Package Job Problem

HELP! I have been banging my head against a brick wall on this one all this morning AAAAAAGGGHHH!

1. I have an SSIS package that runs a simple SQL script and then updates a few tables by downloading some XML of the web. It runs fine when I kick it off manually under SSMS.

2. I created a SQL Server Agent job to run it every day. This always fails. The error information in the log is useless ("Executed as user: domain\user. The package execution failed. The step failed." - I had already figured that out!). It fails almost straight away, and when I enable logging for the SSIS package, no info is ever logged (text file, windows event log, whatever).

3. Out of desperation I have changed Agent to run under the same domain user account that I created the package with. No use.

My questions:

1. How can I get more detailed logging from SQL Server Agent?

2 Any ideas about why it's failing in the first place.

Many thanks in advance.

Ben

If you store the package in the filesystem you should give read/write and maybe execute permission on the file packagename.dtsx to the account sql server agent is running.

To all Microsoft people:

I propose to put on top of the forum one task with solutions for those permission issue as a lot of the posts are because of wrong permission settings. I had this problem too in the past and I would appreciate such a "top" post

Regards

Nobs

|||

Thanks for your quick response. The SSIS package is actually stored in SQL Server though (in Stored Packages->MSDB).

Any more ideas? How about the more detailed logging for SQL Server Agent?

Thanks again,

Ben

|||

Have a look at some issues here-

http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

If we get some answers (mark it as so) then I'll get it locked at the top, that is one of the goals for these forums.

|||

Thank you!

Running the SSIS package as a command line step instead of an SSIS Package step enabled me to obtain all the error info I needed to fix the problem.

Why does Microsoft not give full error information for SSIS packages run through the SQL Server Agent job scheduler (even in SP1)?!?!

Many thanks indeed for helping me to solve the problem.

Ben

|||

Give them the feeback- http://lab.msdn.microsoft.com/productfeedback/default.aspx

|||

Can you explain what "running as a command line step" means? I am having the same problem as you, my SSIS packages won't run as a SQL Agent job, and "the job failed" gives me no information.

Thanks.

|||

JustJFe wrote:

Can you explain what "running as a command line step" means? I am having the same problem as you, my SSIS packages won't run as a SQL Agent job, and "the job failed" gives me no information.

Thanks.

When you are creating a SQL Agent job; you have to create 'steps'; well, there is a dropdown list for 'Type' where you can select 'Operative System (CmdExec)' that is what "Running as a command line step" should mean. This approach is helpful under some scenarios like wehn you want to run your package in a 64-bit machine in a 32-bit mode.

BTW, SQL Agent is not that good providing error descriptions; but you can enable logging in you packages, so you can have more details.

I hope this clarify your doubts.

Rafael Salas

|||

Thanks for the quick response - I will change the Step as you listed - currently, it's an SSIS package type of step.

How do I "enable logging"? (My DBA is out on maternity leave, and I'm trying to cover - my VS skills are good, but SQL Server 2005 is Beginner...)

Thanks!

|||

My Suggestion about using logging may require changes in the packages; I would check fisrt if logging is not being already used first; the table SSIS uses for logging, by default, is sysdtslog90 but that could have been changed by a custom log table; in both cases is something that you can check by opening the packages.

Rafael Salas

|||

By default SQL Agent is not great at giving you output as you say, the View job History stuff is truncated. The whole point of my use CmdExec step recommendation, as illustrated in the link, is that you can then turn on the step level logging in SQL Agent, either to text file or SQL table. When using DTEXEC this means you get the same much the console output in your log files as you would get if running in BIDS looking in the output window, or exactly the same as if using DTEXEC from a command prompt and watching what comes out. SSIS logging is good if the package is running, but what happens if the package cannot be found, or permissions prevent access to the package file even? You will not get SSIS logging, or anything useful in the Job level history. The step log however will have the information you need. Check out the link I posted for an illustration of this which solved an annoying file permission issue for me, when the package could not even be loaded.

Personally I use both logging and a CmdExec steps with step logging.

|||In VS, the package has no logging options - I'm guess that means it's not turned on. Also, there is no table sysdtslog90 in our SQL Server. Does that help direct your answer?|||Thanks for the quick reply, but the link you posted does not have any information on how to set up logging or how to find where the messages are listed. I still only get "job failed" in the Job History screen.|||

DarrenSQLIS wrote:

SSIS logging is good if the package is running, but what happens if the package cannot be found, or permissions prevent access to the package file even? You will not get SSIS logging, or anything useful in the Job level history.

That is a good point...

I have not used the combination you described before: CmdExec and step level logging in SQL Server Agent but seems to be a powerfull tool to debug Agent execution issues.

Thanks

Rafael Salas

|||

Never mind the "Job History screen", can you find the Advanced tab on the job !STEP!, if so set some logging there.

To get started with SSIS logging, have a look on the SSIS menu in VS. You need to ensure the package has the focus to see the SSIS menu, it has a habit of hiding.

Scheduled Jobs

I have a job that runs hourly every day from 7:00 to 19:00.
I'd like to be able to detect the last run of the day.
The problem is, I may change one (or both) or the scheduled run times,
so I don't want to hard code 19:00 into my detection scheme.

I stumbled across the sysjobsschedules table in the msdb database,
and I think the Next_Run_date and Next_Run_time fields will get me where
I need to be.

I'm trying to build a second job that runs at 10 minutes after the hour, 24 hours a day that will somehow detect whether or not the primary job just finished it's last run of the day, and if so, insert some records into a table.

Here's what I have so far...

DECLARE @.intFlg1 INTEGER
SET @.intFlg1= (SELECT CASE WHEN CONVERT(DATETIME, CAST(next_run_date AS CHAR(8)), 102) = Prod_Plan.dbo.RemoveTime(GETDATE()) THEN 1 ELSE 0 END
FROM msdb.dbo.sysjobschedules
WHERE (name = N'prodplan_importorders'))
IF @.intFlg1=0
INSERT INTO BLDOFF_INV_DAILY()
SELECT GETDATE() AS Expr1, Product, Whse, Qty
FROM BldOff_Inv_Hourly

The problem with this is that it will append records every hour after the last run until midnight. I only want it to append them once.How about this ... check to see if the date has already been written to your table. If it has not, insert a new row with the date and time you pulled from sysjobschedules. If it does exist, update the row with the time you pulled from sysjobschedules.

That way you will only end up with one row per day, and it will hold your last run time. You can't tell when the last job of the day has run until the day is over ;)|||That did it...I think - won't know for sure until tomorrow...

DECLARE @.intFlg1 INTEGER
DECLARE @.intFlg2 INTEGER
SET @.intFlg1= (SELECT CASE WHEN CONVERT(DATETIME, CAST(next_run_date AS CHAR(8)), 102) = Prod_Plan.dbo.RemoveTime(GETDATE()) THEN 1 ELSE 0 END
FROM msdb.dbo.sysjobschedules
WHERE (name = N'prodplan_importorders'))

SET @.intFlg2=(SELECT COUNT(*) FROM BLDOFF_INV_DAILY
WHERE prod_plan.mcorron.removetime(BLDDATE)=(
SELECT (CONVERT(DATETIME, CAST(NEXT_RUN_DATE AS CHAR(8)), 102)) AS DTM
FROM MSDB.DBO.SYSJOBSCHEDULES
WHERE [NAME]='PRODPLAN_IMPORTORDERS'))

IF @.intFlg1=0 AND @.intFlg2=0
INSERT INTO BLDOFF_INV_DAILY()
SELECT GETDATE() AS Expr1, Product, Whse, Qty
FROM BldOff_Inv_Hourly

Thanks|||This doesn't seem to want to work as a scheduled job.

DECLARE @.intFlg1 INTEGER
DECLARE @.intFlg2 INTEGER
SET @.intFlg1= (SELECT CASE WHEN CONVERT(DATETIME, CAST(next_run_date AS CHAR(8)), 102) = Prod_Plan.dbo.RemoveTime(GETDATE()) THEN 1 ELSE 0 END
FROM msdb.dbo.sysjobschedules
WHERE (name = N'prodplan_importorders'))

SET @.intFlg2=(SELECT COUNT(*) FROM BLDOFF_INV_DAILY
WHERE prod_plan.dbo.removetime(BLDDATE)=(
SELECT (CONVERT(DATETIME, CAST(NEXT_RUN_DATE AS CHAR(8)), 102)) AS DTM
FROM MSDB.DBO.SYSJOBSCHEDULES
WHERE [NAME]='PRODPLAN_IMPORTORDERS'))

IF @.intFlg1=0 AND @.intFlg2=0
INSERT INTO BLDOFF_INV_DAILY(BLDDATE, PRODUCT, WHSE, QTY)
SELECT GETDATE() AS Expr1, Product, Whse, Qty
FROM BldOff_Inv_Hourly
GO

The job says it ran succesfully as scheduled, but for whatever reason it doesn't insert the rows. If copy it and past it into QA, it inserts ~1100 rows.|||You could use msdb.dbo.sysjobhistory using the job_id from sysjobs table. sysjobhistory has the step (0 = Job outcome), run_date, run_time, and the message column will tell you if the job succeeded or failed. Just get the max runtime for the rundate in question ... careful though ... run_date and run_time are stored as integers.

scheduled job runs forewer

Hello,
I have scheduled job that are scheduled to run every 2 hours.
Its exec stored procedures. Normal execution time is up to 10 min.
But recently job not running successfully, its just executes forever. So I had to kill that job and I run it manually.
Please help,
I have no idea why it stops executing on its own by the schedule.

Thank you,did you use sp_who2 to see if it is blocked?

did you use dbcc_inputbuffer or sql_handle to see if the code executing changed?

did you create an output file in the job scheduler to capture output from the job steps?

did you change the procs to write to the output file as each starts, runs, and ends?

did you logically think "what can i do to troubleshoot this issue?"

did you google for similar situations?

did you look at the "sticky" at the top of the forum to see what you might need to add to your post to help us help you?|||Thank you for all of the suggestions you have for me.
I will try them one by one.|||How about trying one of the easiest things, stop and start the sql agent. Sometimes the scheduler gets wacked out and a simple stop and start will fix it.|||Does the code contains loops or cursors?|||Does the code contains loops or cursors?

while (1=1)
begin
print 'probably'
end|||The procedure does contain cursors and it runs Ok via SQL Query Analyzer, but once I add it up to SQL Server Agent Job it takes forever...
I do have an output file to capture output from the job steps, but since the job never been completed, and I had to kill it, there are no completion info.
If I will stop and start the sql agent before execution of this job, how the other scheduled processes be affected by that?

Thank you|||Whatever other issues may be occurring, step #1 is to drop the cursors and use set-based operations.|||Unless the jobs sends separate e-mails to employees, for example...|||The procedure does send an e-mail to few employees. The proc was written way before I started my work within the company, I already suggested to go trough code, and rewrite some sql - got rejection, so not sure how else to handle the issues.
Thank you.|||...I already suggested to go trough code, and rewrite some sql - got rejectionBlindman's principle of employement: Never work for people who aren't as smart as you.|||Does that make you unemployed Blinddude? :p|||Does that make you unemployed Blinddude? :p

More like unemployable.;)

hmscott|||that's how you guys have so much time to post.|||your boss doesn't have to be as smart as you.

what's important is that he/she does what you tell them to do. :)

Scheduled DTS Package not running

I have a DTS package setup that runs just fine when I fire
it off as a DTS package, but when I schedule it to run it
fails.
I'm running SQL2K, sp3. SQLAgent is running.
Has anyone seen this before ?
TIA...
Michael.What is the package trying to do when it fails? This can happen because of
privileges (or lack thereof) of the SQL Agent service account that differ
from the privileges used when running the package from Enterprise Manager.
Bob
Microsoft Consulting Services
--
This posting is provided AS IS with no warranties, and confers no rights.|||as indicated before it depends on a number of things . .
1) if DTS references a file that SQLAgent Account cannot Access/does not hav
e access to
2) if non-sysadmin created SQLAgentJob and SQLProxy a/c exist then job will
be executed in SQLProxy a/c context
3) if DTS is executed using exec xp_cmdshell 'DTSRun' or xp_cmdshell exists
inside DTS and SQLAgent job creator account does not have rights to execute
xp_cmdshell and no sqlproxy account then could get the problem.
hope this helps|||The first part is an ActiveX script that gets file names
off a dir. and inserts them into a table.
The acct mesSQL01 is the account that SQL logs in under as
well as SQLAgent. It is also the owner of the DTS Package,
and has full control on the dir that it is looking on.
heres the fail message that is returned by sendmail:
STATUS: Failed
MESSAGES: The job failed. Unable to determine if
the owner (WILL\mesql01) of job GetFileName has server
access (reason: Could not obtain information about Windows
NT group/user 'WILL\mesql01'. [SQLSTATE 42000] (Error
8198))
I'm gonna start tearing it apart to see if there are other
parts of it that it CAN run. I am quite at a loss here.
Thanks for your help !!
m.

>--Original Message--
>What is the package trying to do when it fails? This can
happen because of
>privileges (or lack thereof) of the SQL Agent service
account that differ
>from the privileges used when running the package from
Enterprise Manager.
>--
>Bob
>Microsoft Consulting Services
>--
>This posting is provided AS IS with no warranties, and
confers no rights.
>
>.
>|||Ok, I think I've nailed down what is causing it to fail,
but I can't seem to find a solution.
Here's the ActiveX script...
Function Main()
Dim FSys
Dim Folder
Dim Files
Dim Comm
Dim Conn
Set FSys = CreateObject
("Scripting.FileSystemObject")
Set Folder = FSys.GetFolder
("\\fsq\Files\DM\Lab\TechReports")
Set Files = Folder.files
Set Conn = CreateObject("ADODB.Connection")
Set Comm = CreateObject("ADODB.Command")
With Conn
.Provider = "sqloledb"'
.open "Data Source = fsq\Mes_SQL01;
Initial Catalog =Lab Tech Library; User id=MesSQL01;
password=xxxxx"
end with
Set Comm.ActiveConnection = Conn
It all works with the WITH statement stuff rem'ed out, so
it's the stuff in the WITH statement that is causing the
fail, even though the MesSQL01 acct is a Domain account,
and has Admin priv on the SQL server. It even fails when I
log on as MesSQL01 and run the package local.
Why would this fail, even though it has the permissions ?
This is the domain acct that the server and SQLAgent logs
in under ?
help ?
TIA

>--Original Message--
>What is the package trying to do when it fails? This can
happen because of
>privileges (or lack thereof) of the SQL Agent service
account that differ
>from the privileges used when running the package from
Enterprise Manager.
>--
>Bob
>Microsoft Consulting Services
>--
>This posting is provided AS IS with no warranties, and
confers no rights.
>
>.
>|||Need to connect with trusted connection(not )
your account is a trusted connection therefore no requirement to have userid
and password
Dim IsTrusted ' IsTrusted is 0 by default
Dim oServer
Dim strServer
' Create DMO server object
Set oServer = CreateObject("SQLDMO.SQLServer")
' Use secure login and then login (trusted connection)
IsTrusted = 0
oServer.LoginSecure = True
' connect to the server requested
oServer.Connect (strServer)

Scheduled DTS Package not running

I have a DTS package setup that runs just fine when I fire
it off as a DTS package, but when I schedule it to run it
fails.
I'm running SQL2K, sp3. SQLAgent is running.
Has anyone seen this before ?
TIA...
Michael.What is the package trying to do when it fails? This can happen because of
privileges (or lack thereof) of the SQL Agent service account that differ
from the privileges used when running the package from Enterprise Manager.
--
Bob
Microsoft Consulting Services
--
This posting is provided AS IS with no warranties, and confers no rights.|||as indicated before it depends on a number of things . .
1) if DTS references a file that SQLAgent Account cannot Access/does not have access t
2) if non-sysadmin created SQLAgentJob and SQLProxy a/c exist then job will be executed in SQLProxy a/c contex
3) if DTS is executed using exec xp_cmdshell 'DTSRun' or xp_cmdshell exists inside DTS and SQLAgent job creator account does not have rights to execute xp_cmdshell and no sqlproxy account then could get the problem
hope this helps|||The first part is an ActiveX script that gets file names
off a dir. and inserts them into a table.
The acct mesSQL01 is the account that SQL logs in under as
well as SQLAgent. It is also the owner of the DTS Package,
and has full control on the dir that it is looking on.
heres the fail message that is returned by sendmail:
STATUS: Failed
MESSAGES: The job failed. Unable to determine if
the owner (WILL\mesql01) of job GetFileName has server
access (reason: Could not obtain information about Windows
NT group/user 'WILL\mesql01'. [SQLSTATE 42000] (Error
8198))
I'm gonna start tearing it apart to see if there are other
parts of it that it CAN run. I am quite at a loss here.
Thanks for your help !!
m.
>--Original Message--
>What is the package trying to do when it fails? This can
happen because of
>privileges (or lack thereof) of the SQL Agent service
account that differ
>from the privileges used when running the package from
Enterprise Manager.
>--
>Bob
>Microsoft Consulting Services
>--
>This posting is provided AS IS with no warranties, and
confers no rights.
>
>.
>|||Ok, I think I've nailed down what is causing it to fail,
but I can't seem to find a solution.
Here's the ActiveX script...
Function Main()
Dim FSys
Dim Folder
Dim Files
Dim Comm
Dim Conn
Set FSys = CreateObject
("Scripting.FileSystemObject")
Set Folder = FSys.GetFolder
("\\fsq\Files\DM\Lab\TechReports")
Set Files = Folder.files
Set Conn = CreateObject("ADODB.Connection")
Set Comm = CreateObject("ADODB.Command")
With Conn
.Provider = "sqloledb"'
.open "Data Source = fsq\Mes_SQL01;
Initial Catalog =Lab Tech Library; User id=MesSQL01;
password=xxxxx"
end with
Set Comm.ActiveConnection = Conn
It all works with the WITH statement stuff rem'ed out, so
it's the stuff in the WITH statement that is causing the
fail, even though the MesSQL01 acct is a Domain account,
and has Admin priv on the SQL server. It even fails when I
log on as MesSQL01 and run the package local.
Why would this fail, even though it has the permissions ?
This is the domain acct that the server and SQLAgent logs
in under ?
help ?
TIA
>--Original Message--
>What is the package trying to do when it fails? This can
happen because of
>privileges (or lack thereof) of the SQL Agent service
account that differ
>from the privileges used when running the package from
Enterprise Manager.
>--
>Bob
>Microsoft Consulting Services
>--
>This posting is provided AS IS with no warranties, and
confers no rights.
>
>.
>|||Need to connect with trusted connection(not
your account is a trusted connection therefore no requirement to have userid and passwor
Dim IsTrusted ' IsTrusted is 0 by defaul
Dim oServe
Dim strServe
' Create DMO server object
Set oServer = CreateObject("SQLDMO.SQLServer"
' Use secure login and then login (trusted connection
IsTrusted = 0
oServer.LoginSecure = Tru
' connect to the server requeste
oServer.Connect (strServer)

Scheduled DTS job to run a cmd file not working

Hi,

I have created a DTS job that contains one 'Execute SQL Task' job. This SQL task runs a cmd file. The cmd file runs a few windows commands and then runs a Micorosoft Access Macro. Once finished both access and the cmd screen close down.

If I open up the DTS job and execute it manually, it works fine (takes about 1/2 an hour to run). My problem is that when I schedule the DTS job, the job starts up at the correct time but it never actually starts running the cmd file and it gives no error. It says Executing until I actually stop the job manually.

The Job details are:

Type: Operating System Command [CmdExec]

Command: DTSRun /~Z0x2F3FF84472BB6E7FF356EB006BA1AEC62C95AB3BF506F3 4A241F228CE148AB09DBC66B8651A450B725E6C4E6A1D328E4 EC2F2C0F8E323F1C7D501FD5B8FD00E25656514AF2224407DB 1C569163CBE383A8E7D8BE4974A0911F5CEB

The DTS details are:

C:\Batches\DTSrunofUpdatesqlpmi.cmd

The cmd program details are:

echo on
:Start

if not exist "c:\apps\CI_Databases\pmi\pmiload.mdb" exit

rem cleanup unfinsihed runs
if exist "c:\apps\CI_Databases\pmi\pmiload.mdb" if exist "c:\apps\CI_Databases\pmi\pmiloadold.mdb" del "c:\apps\CI_Databases\pmi\pmiloadold.mdb"

rem main file locked assume its being work on so don't run this job.
if exist "c:\apps\CI_Databases\pmi\pmiload.ldb" exit

rem if a tmp exits then the last job never completed - continue to add to tmp file

if exist "c:\apps\CI_Databases\pmi\pmiloadtmp.mdb" goto Load

copy "c:\apps\CI_Databases\pmi\pmiload.mdb" "c:\apps\CI_Databases\pmi\pmiloadtmp.mdb"

:Load
"C:\Program Files\microsoft office 2003\OFFICE11\msaccess.exe" "c:\apps\CI_Databases\pmi\pmiloadtmp.mdb"/x updatedata
rem Compact databases
"C:\Program Files\microsoft office 2003\OFFICE11\msaccess.exe" "c:\apps\CI_Databases\pmi\pmiloadtmp.mdb"/compact

ren "c:\apps\CI_Databases\pmi\pmiload.mdb" "pmiloadold.mdb"
ren "c:\apps\CI_Databases\pmi\pmiloadtmp.mdb" "pmiload.mdb"
if exist "c:\apps\CI_Databases\pmi\pmiload.mdb" if exist "c:\apps\CI_Databases\pmi\pmiloadold.mdb" del "c:\apps\CI_Databases\pmi\pmiloadold.mdb"

All help is greatly appreciated as this has been bugging me for some time now.

Thanks
SamIf all your DTS package is doing is running a .cmd (batch) file, why not run it instead from a scheduled task. In the schedule task step, select task type of Operating system command and type in the path and name of the batch file.

This may not resolve the problem, however. I suspect that the problem lies within permissions. What account is the SQL Agent user set up to run under? Then think about what permissions your account has that the SQL Agent account might not have.

Regards,

hmscott|||Thanks hmscott.
That works when i change the SQL Server Agent startup account username and password.

Scheduled DTS issue - copying of data

Hi all,
I have a problem.
I have a scheduled DTS package that runs every week that copies data
only from staging server to production into a table. When the job is
finished it states that all is successful. When I check the row count
for this table there is a discrepancy with what exists in prod to that
which exists in staging. In prod there is about 200,000 records
less. If I run the DTS job manually...then all is correct - rowcounts
are the same. When I run it from the schedule it does not.
Does anyone know why this is occurring and how to resolve the issue?
Thanks.Hi
You don't say which version of SQL Server you are running on, or if this is
a straight forward import/export or how you are handling errors.
Has data been added after the import has occurred?
John
"woohoo30@.hotmail.com" wrote:
> Hi all,
> I have a problem.
>
> I have a scheduled DTS package that runs every week that copies data
> only from staging server to production into a table. When the job is
> finished it states that all is successful. When I check the row count
> for this table there is a discrepancy with what exists in prod to that
> which exists in staging. In prod there is about 200,000 records
> less. If I run the DTS job manually...then all is correct - rowcounts
> are the same. When I run it from the schedule it does not.
> Does anyone know why this is occurring and how to resolve the issue?
> Thanks.
>|||On Nov 1, 12:20 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> You don't say which version of SQL Server you are running on, or if this is
> a straight forward import/export or how you are handling errors.
> Has data been added after the import has occurred?
> John
>
>
Hi John,
1st it truncates the table and then it does a transform of data on
staging to production server.
there are no errors...it says successful.
It inserts about 2million rows into the table...there is a discrepancy
of 200,000.
I don't understand why it's occurring and how to fix this issue.
woohoo30|||Hi
If you use a linked server to the staging server and use a INSERT... SELECT
statement from the live server does this bring all the rows you expect?
Does the live server have a primary key? Does the staged data have this key,
if not how many rows are unique for the primary key columns?
John
"woohoo30@.hotmail.com" wrote:
> On Nov 1, 12:20 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> > Hi
> >
> > You don't say which version of SQL Server you are running on, or if this is
> > a straight forward import/export or how you are handling errors.
> >
> > Has data been added after the import has occurred?
> >
> > John
> >
> >
> >
>
> Hi John,
> 1st it truncates the table and then it does a transform of data on
> staging to production server.
> there are no errors...it says successful.
> It inserts about 2million rows into the table...there is a discrepancy
> of 200,000.
> I don't understand why it's occurring and how to fix this issue.
> woohoo30
>

scheduled dts fails but runs when done manually

I'm a newbie to sql2000. i have some jobs that copy stuff from a non microsoft database(titanium) to SQL2000. i use the propriety odbc drivers provided by titanium. when run manually in enterprize manager,it works. but scheduled as a job it fails with the message "System cannot find the specified File" i've read up and tried microsofts suggestions ie ensuring that the sql server agent has rights to the folders used etc. i've also tried puttin in the the dts owner and user passwords. each time it fails with the same error.
any one out there who can help?:confused:I'm a SQL2K newbie and I've experienced a similar problem.

When you run the DTS pkg manually, it runs on your client machine;
when you run it as a job it runs on the SQL server.

If you can run the DTS pkg manually on the SQL server (from its console), do that. You should get more details on the error. Most likely
something is missing from the PATH environment variable on the SQL
server (and you can compare to what you have for this on your client
machine).

Good luck!

Jeff|||Jeff's on the right track. When you develop a DTS package remotely, it remembers the paths based on where you developed it. You would either need to
- develop the package locally (on the server), in which case it would not run interactively from a remote machine, or

- use a unc path, ie. \\servername\sharename\filename. In this case, it should run interactively or scheduled.

Steve|||I'd also add to check permissions. You need to keep in mind that the job on the server is not going to run as "you" but as the sql server agent, so it maynot have the same authorization to access drives and shares as you do. That's the one that always catches me.

Scheduled DTS always fails

I have a DTS that runs fine when executed at the package level. However, it
always fails when it runs as a scheduled job through the SQL Server Agent.
It always fails at ~93 seconds .
Anybody else experiencing this?
Here is the text for the failed scheduled DTS:
The execution of the following DTS Package succeeded:
Package Name: ORDER_HISTORY
Package Description: (null)
Package ID: {64B36A3E-7E07-46B5-BE7B-6DED729B7F70}
Package Version: {7A9368AF-3407-4708-9AFB-3CAFAD5ADB12}
Package Execution Lineage: {530DAB60-7837-406A-BDB3-37E577D79EE4}
Executed On: MSCSQL
Executed By: Administrator
Execution Started: 11/10/04 3:33:22 PM
Execution Completed: 11/10/04 3:34:55 PM
Total Execution Time: 92.984 seconds
Package Steps execution information:
Step 'Create Table [backend].[dbo].[ORDER_HISTORY] Step' succeeded
Step Execution Started: 11/10/04 3:33:22 PM
Step Execution Completed: 11/10/04 3:33:22 PM
Total Step Execution Time: 0.047 seconds
Progress count in Step: 0
Step 'Copy Data from ORDER_HISTORY to [backend].[dbo].[ORDER_HISTORY] Step'
failed
Step Error Source: Microsoft OLE DB Provider for ODBC Drivers
Step Error Description:[TOD][ODBC][Unknown]CONFIG: Expected a CONFIG call
Step Error code: 80074005
Step Error Help File:
Step Error Help Context ID:0
Step Execution Started: 11/10/04 3:33:22 PM
Step Execution Completed: 11/10/04 3:34:55 PM
Total Step Execution Time: 92.891 seconds
Progress count in Step: 72000
Step 'DTSStep_DTSExecuteSQLTask_1' was not executed
Step 'DTSStep_DTSExecuteSQLTask_2' was not executed
************************************************** **************************
****************
Here is the DTS log for the successful manually run DTS:
The execution of the following DTS Package succeeded:
Package Name: ORDER_HISTORY
Package Description: (null)
Package ID: {64B36A3E-7E07-46B5-BE7B-6DED729B7F70}
Package Version: {7A9368AF-3407-4708-9AFB-3CAFAD5ADB12}
Package Execution Lineage: {578A9ED8-A483-4D6B-A169-EB952D5AF27B}
Executed On: MSCSQL
Executed By: Administrator
Execution Started: 11/10/04 3:28:54 PM
Execution Completed: 11/10/04 3:32:35 PM
Total Execution Time: 220.906 seconds
Package Steps execution information:
Step 'Create Table [backend].[dbo].[ORDER_HISTORY] Step' succeeded
Step Execution Started: 11/10/04 3:28:54 PM
Step Execution Completed: 11/10/04 3:28:54 PM
Total Step Execution Time: 0.078 seconds
Progress count in Step: 0
Step 'Copy Data from ORDER_HISTORY to [backend].[dbo].[ORDER_HISTORY] Step'
succeeded
Step Execution Started: 11/10/04 3:28:54 PM
Step Execution Completed: 11/10/04 3:32:34 PM
Total Step Execution Time: 220.688 seconds
Progress count in Step: 118744
Step 'DTSStep_DTSExecuteSQLTask_1' succeeded
Step Execution Started: 11/10/04 3:32:34 PM
Step Execution Completed: 11/10/04 3:32:34 PM
Total Step Execution Time: 0.078 seconds
Progress count in Step: 0
Step 'DTSStep_DTSExecuteSQLTask_2' succeeded
Step Execution Started: 11/10/04 3:32:34 PM
Step Execution Completed: 11/10/04 3:32:35 PM
Total Step Execution Time: 0.016 seconds
Progress count in Step: 0
Michael,
DTS Packages run in the context of the calling process. You should check
permissions of the job, and also check to see if the connections are valid
for the job user. For example, if you run the package from Enterprise
Manager on another machine than the server, the connections may be valid,
but run from the server they may fail.
Jon Jahren
"Michael D. McGill" <mcgillmd@.hotmail.com> wrote in message
news:#4wQAV2xEHA.1076@.TK2MSFTNGP10.phx.gbl...
> I have a DTS that runs fine when executed at the package level. However,
it
> always fails when it runs as a scheduled job through the SQL Server Agent.
> It always fails at ~93 seconds .
> Anybody else experiencing this?
>
> Here is the text for the failed scheduled DTS:
> The execution of the following DTS Package succeeded:
> Package Name: ORDER_HISTORY
> Package Description: (null)
> Package ID: {64B36A3E-7E07-46B5-BE7B-6DED729B7F70}
> Package Version: {7A9368AF-3407-4708-9AFB-3CAFAD5ADB12}
> Package Execution Lineage: {530DAB60-7837-406A-BDB3-37E577D79EE4}
> Executed On: MSCSQL
> Executed By: Administrator
> Execution Started: 11/10/04 3:33:22 PM
> Execution Completed: 11/10/04 3:34:55 PM
> Total Execution Time: 92.984 seconds
> Package Steps execution information:
>
> Step 'Create Table [backend].[dbo].[ORDER_HISTORY] Step' succeeded
> Step Execution Started: 11/10/04 3:33:22 PM
> Step Execution Completed: 11/10/04 3:33:22 PM
> Total Step Execution Time: 0.047 seconds
> Progress count in Step: 0
> Step 'Copy Data from ORDER_HISTORY to [backend].[dbo].[ORDER_HISTORY]
Step'
> failed
> Step Error Source: Microsoft OLE DB Provider for ODBC Drivers
> Step Error Description:[TOD][ODBC][Unknown]CONFIG: Expected a CONFIG call
> Step Error code: 80074005
> Step Error Help File:
> Step Error Help Context ID:0
> Step Execution Started: 11/10/04 3:33:22 PM
> Step Execution Completed: 11/10/04 3:34:55 PM
> Total Step Execution Time: 92.891 seconds
> Progress count in Step: 72000
> Step 'DTSStep_DTSExecuteSQLTask_1' was not executed
> Step 'DTSStep_DTSExecuteSQLTask_2' was not executed
>
************************************************** **************************
> ****************
> Here is the DTS log for the successful manually run DTS:
> The execution of the following DTS Package succeeded:
> Package Name: ORDER_HISTORY
> Package Description: (null)
> Package ID: {64B36A3E-7E07-46B5-BE7B-6DED729B7F70}
> Package Version: {7A9368AF-3407-4708-9AFB-3CAFAD5ADB12}
> Package Execution Lineage: {578A9ED8-A483-4D6B-A169-EB952D5AF27B}
> Executed On: MSCSQL
> Executed By: Administrator
> Execution Started: 11/10/04 3:28:54 PM
> Execution Completed: 11/10/04 3:32:35 PM
> Total Execution Time: 220.906 seconds
> Package Steps execution information:
>
> Step 'Create Table [backend].[dbo].[ORDER_HISTORY] Step' succeeded
> Step Execution Started: 11/10/04 3:28:54 PM
> Step Execution Completed: 11/10/04 3:28:54 PM
> Total Step Execution Time: 0.078 seconds
> Progress count in Step: 0
> Step 'Copy Data from ORDER_HISTORY to [backend].[dbo].[ORDER_HISTORY]
Step'
> succeeded
> Step Execution Started: 11/10/04 3:28:54 PM
> Step Execution Completed: 11/10/04 3:32:34 PM
> Total Step Execution Time: 220.688 seconds
> Progress count in Step: 118744
> Step 'DTSStep_DTSExecuteSQLTask_1' succeeded
> Step Execution Started: 11/10/04 3:32:34 PM
> Step Execution Completed: 11/10/04 3:32:34 PM
> Total Step Execution Time: 0.078 seconds
> Progress count in Step: 0
> Step 'DTSStep_DTSExecuteSQLTask_2' succeeded
> Step Execution Started: 11/10/04 3:32:34 PM
> Step Execution Completed: 11/10/04 3:32:35 PM
> Total Step Execution Time: 0.016 seconds
> Progress count in Step: 0
>
|||Check the job owner, too. Jobs owned by users not in the sa role will not
run in the context of the sql server service.
How to run a dts package as a scheduled job:
http://support.microsoft.com/default...b;en-us;269074
"Jon Jahren" <jon.jahren.fightspam@.sqlkompetanse.no> wrote in message
news:%23C0Nt18xEHA.1308@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Michael,
> DTS Packages run in the context of the calling process. You should check
> permissions of the job, and also check to see if the connections are valid
> for the job user. For example, if you run the package from Enterprise
> Manager on another machine than the server, the connections may be valid,
> but run from the server they may fail.
> Jon Jahren
> "Michael D. McGill" <mcgillmd@.hotmail.com> wrote in message
> news:#4wQAV2xEHA.1076@.TK2MSFTNGP10.phx.gbl...
> it
Agent.[vbcol=seagreen]
> Step'
call
>
************************************************** **************************
> Step'
>

Scheduled DTS always fails

I have a DTS that runs fine when executed at the package level. However, it
always fails when it runs as a scheduled job through the SQL Server Agent.
It always fails at ~93 seconds .
Anybody else experiencing this?
Here is the text for the failed scheduled DTS:
The execution of the following DTS Package succeeded:
Package Name: ORDER_HISTORY
Package Description: (null)
Package ID: {64B36A3E-7E07-46B5-BE7B-6DED729B7F70}
Package Version: {7A9368AF-3407-4708-9AFB-3CAFAD5ADB12}
Package Execution Lineage: {530DAB60-7837-406A-BDB3-37E577D79EE4}
Executed On: MSCSQL
Executed By: Administrator
Execution Started: 11/10/04 3:33:22 PM
Execution Completed: 11/10/04 3:34:55 PM
Total Execution Time: 92.984 seconds
Package Steps execution information:
Step 'Create Table [backend].[dbo].[ORDER_HISTORY] Step' succeeded
Step Execution Started: 11/10/04 3:33:22 PM
Step Execution Completed: 11/10/04 3:33:22 PM
Total Step Execution Time: 0.047 seconds
Progress count in Step: 0
Step 'Copy Data from ORDER_HISTORY to [backend].[dbo].[ORDER_HISTORY] Step'
failed
Step Error Source: Microsoft OLE DB Provider for ODBC Drivers
Step Error Description:[TOD][ODBC][Unknown]CONFIG: Expected a CONFIG call
Step Error code: 80074005
Step Error Help File:
Step Error Help Context ID:0
Step Execution Started: 11/10/04 3:33:22 PM
Step Execution Completed: 11/10/04 3:34:55 PM
Total Step Execution Time: 92.891 seconds
Progress count in Step: 72000
Step 'DTSStep_DTSExecuteSQLTask_1' was not executed
Step 'DTSStep_DTSExecuteSQLTask_2' was not executed
************************************************** **************************
****************
Here is the DTS log for the successful manually run DTS:
The execution of the following DTS Package succeeded:
Package Name: ORDER_HISTORY
Package Description: (null)
Package ID: {64B36A3E-7E07-46B5-BE7B-6DED729B7F70}
Package Version: {7A9368AF-3407-4708-9AFB-3CAFAD5ADB12}
Package Execution Lineage: {578A9ED8-A483-4D6B-A169-EB952D5AF27B}
Executed On: MSCSQL
Executed By: Administrator
Execution Started: 11/10/04 3:28:54 PM
Execution Completed: 11/10/04 3:32:35 PM
Total Execution Time: 220.906 seconds
Package Steps execution information:
Step 'Create Table [backend].[dbo].[ORDER_HISTORY] Step' succeeded
Step Execution Started: 11/10/04 3:28:54 PM
Step Execution Completed: 11/10/04 3:28:54 PM
Total Step Execution Time: 0.078 seconds
Progress count in Step: 0
Step 'Copy Data from ORDER_HISTORY to [backend].[dbo].[ORDER_HISTORY] Step'
succeeded
Step Execution Started: 11/10/04 3:28:54 PM
Step Execution Completed: 11/10/04 3:32:34 PM
Total Step Execution Time: 220.688 seconds
Progress count in Step: 118744
Step 'DTSStep_DTSExecuteSQLTask_1' succeeded
Step Execution Started: 11/10/04 3:32:34 PM
Step Execution Completed: 11/10/04 3:32:34 PM
Total Step Execution Time: 0.078 seconds
Progress count in Step: 0
Step 'DTSStep_DTSExecuteSQLTask_2' succeeded
Step Execution Started: 11/10/04 3:32:34 PM
Step Execution Completed: 11/10/04 3:32:35 PM
Total Step Execution Time: 0.016 seconds
Progress count in Step: 0
Michael,
DTS Packages run in the context of the calling process. You should check
permissions of the job, and also check to see if the connections are valid
for the job user. For example, if you run the package from Enterprise
Manager on another machine than the server, the connections may be valid,
but run from the server they may fail.
Jon Jahren
"Michael D. McGill" <mcgillmd@.hotmail.com> wrote in message
news:#4wQAV2xEHA.1076@.TK2MSFTNGP10.phx.gbl...
> I have a DTS that runs fine when executed at the package level. However,
it
> always fails when it runs as a scheduled job through the SQL Server Agent.
> It always fails at ~93 seconds .
> Anybody else experiencing this?
>
> Here is the text for the failed scheduled DTS:
> The execution of the following DTS Package succeeded:
> Package Name: ORDER_HISTORY
> Package Description: (null)
> Package ID: {64B36A3E-7E07-46B5-BE7B-6DED729B7F70}
> Package Version: {7A9368AF-3407-4708-9AFB-3CAFAD5ADB12}
> Package Execution Lineage: {530DAB60-7837-406A-BDB3-37E577D79EE4}
> Executed On: MSCSQL
> Executed By: Administrator
> Execution Started: 11/10/04 3:33:22 PM
> Execution Completed: 11/10/04 3:34:55 PM
> Total Execution Time: 92.984 seconds
> Package Steps execution information:
>
> Step 'Create Table [backend].[dbo].[ORDER_HISTORY] Step' succeeded
> Step Execution Started: 11/10/04 3:33:22 PM
> Step Execution Completed: 11/10/04 3:33:22 PM
> Total Step Execution Time: 0.047 seconds
> Progress count in Step: 0
> Step 'Copy Data from ORDER_HISTORY to [backend].[dbo].[ORDER_HISTORY]
Step'
> failed
> Step Error Source: Microsoft OLE DB Provider for ODBC Drivers
> Step Error Description:[TOD][ODBC][Unknown]CONFIG: Expected a CONFIG call
> Step Error code: 80074005
> Step Error Help File:
> Step Error Help Context ID:0
> Step Execution Started: 11/10/04 3:33:22 PM
> Step Execution Completed: 11/10/04 3:34:55 PM
> Total Step Execution Time: 92.891 seconds
> Progress count in Step: 72000
> Step 'DTSStep_DTSExecuteSQLTask_1' was not executed
> Step 'DTSStep_DTSExecuteSQLTask_2' was not executed
>
************************************************** **************************
> ****************
> Here is the DTS log for the successful manually run DTS:
> The execution of the following DTS Package succeeded:
> Package Name: ORDER_HISTORY
> Package Description: (null)
> Package ID: {64B36A3E-7E07-46B5-BE7B-6DED729B7F70}
> Package Version: {7A9368AF-3407-4708-9AFB-3CAFAD5ADB12}
> Package Execution Lineage: {578A9ED8-A483-4D6B-A169-EB952D5AF27B}
> Executed On: MSCSQL
> Executed By: Administrator
> Execution Started: 11/10/04 3:28:54 PM
> Execution Completed: 11/10/04 3:32:35 PM
> Total Execution Time: 220.906 seconds
> Package Steps execution information:
>
> Step 'Create Table [backend].[dbo].[ORDER_HISTORY] Step' succeeded
> Step Execution Started: 11/10/04 3:28:54 PM
> Step Execution Completed: 11/10/04 3:28:54 PM
> Total Step Execution Time: 0.078 seconds
> Progress count in Step: 0
> Step 'Copy Data from ORDER_HISTORY to [backend].[dbo].[ORDER_HISTORY]
Step'
> succeeded
> Step Execution Started: 11/10/04 3:28:54 PM
> Step Execution Completed: 11/10/04 3:32:34 PM
> Total Step Execution Time: 220.688 seconds
> Progress count in Step: 118744
> Step 'DTSStep_DTSExecuteSQLTask_1' succeeded
> Step Execution Started: 11/10/04 3:32:34 PM
> Step Execution Completed: 11/10/04 3:32:34 PM
> Total Step Execution Time: 0.078 seconds
> Progress count in Step: 0
> Step 'DTSStep_DTSExecuteSQLTask_2' succeeded
> Step Execution Started: 11/10/04 3:32:34 PM
> Step Execution Completed: 11/10/04 3:32:35 PM
> Total Step Execution Time: 0.016 seconds
> Progress count in Step: 0
>
|||Check the job owner, too. Jobs owned by users not in the sa role will not
run in the context of the sql server service.
How to run a dts package as a scheduled job:
http://support.microsoft.com/default...b;en-us;269074
"Jon Jahren" <jon.jahren.fightspam@.sqlkompetanse.no> wrote in message
news:%23C0Nt18xEHA.1308@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Michael,
> DTS Packages run in the context of the calling process. You should check
> permissions of the job, and also check to see if the connections are valid
> for the job user. For example, if you run the package from Enterprise
> Manager on another machine than the server, the connections may be valid,
> but run from the server they may fail.
> Jon Jahren
> "Michael D. McGill" <mcgillmd@.hotmail.com> wrote in message
> news:#4wQAV2xEHA.1076@.TK2MSFTNGP10.phx.gbl...
> it
Agent.[vbcol=seagreen]
> Step'
call
>
************************************************** **************************
> Step'
>

Scheduled DTS always fails

I have a DTS that runs fine when executed at the package level. However, it
always fails when it runs as a scheduled job through the SQL Server Agent.
It always fails at ~93 seconds .
Anybody else experiencing this?
Here is the text for the failed scheduled DTS:
The execution of the following DTS Package succeeded:
Package Name: ORDER_HISTORY
Package Description: (null)
Package ID: {64B36A3E-7E07-46B5-BE7B-6DED729B7F70}
Package Version: {7A9368AF-3407-4708-9AFB-3CAFAD5ADB12}
Package Execution Lineage: {530DAB60-7837-406A-BDB3-37E577D79EE4}
Executed On: MSCSQL
Executed By: Administrator
Execution Started: 11/10/04 3:33:22 PM
Execution Completed: 11/10/04 3:34:55 PM
Total Execution Time: 92.984 seconds
Package Steps execution information:
Step 'Create Table [backend].[dbo].[ORDER_HISTORY] Step' succeeded
Step Execution Started: 11/10/04 3:33:22 PM
Step Execution Completed: 11/10/04 3:33:22 PM
Total Step Execution Time: 0.047 seconds
Progress count in Step: 0
Step 'Copy Data from ORDER_HISTORY to [backend].[dbo].[ORDER_HISTORY] Step'
failed
Step Error Source: Microsoft OLE DB Provider for ODBC Drivers
Step Error Description:[TOD][ODBC][Unknown]CONFIG: Expected a CONFIG call
Step Error code: 80074005
Step Error Help File:
Step Error Help Context ID:0
Step Execution Started: 11/10/04 3:33:22 PM
Step Execution Completed: 11/10/04 3:34:55 PM
Total Step Execution Time: 92.891 seconds
Progress count in Step: 72000
Step 'DTSStep_DTSExecuteSQLTask_1' was not executed
Step 'DTSStep_DTSExecuteSQLTask_2' was not executed
****************************************************************************
****************
Here is the DTS log for the successful manually run DTS:
The execution of the following DTS Package succeeded:
Package Name: ORDER_HISTORY
Package Description: (null)
Package ID: {64B36A3E-7E07-46B5-BE7B-6DED729B7F70}
Package Version: {7A9368AF-3407-4708-9AFB-3CAFAD5ADB12}
Package Execution Lineage: {578A9ED8-A483-4D6B-A169-EB952D5AF27B}
Executed On: MSCSQL
Executed By: Administrator
Execution Started: 11/10/04 3:28:54 PM
Execution Completed: 11/10/04 3:32:35 PM
Total Execution Time: 220.906 seconds
Package Steps execution information:
Step 'Create Table [backend].[dbo].[ORDER_HISTORY] Step' succeeded
Step Execution Started: 11/10/04 3:28:54 PM
Step Execution Completed: 11/10/04 3:28:54 PM
Total Step Execution Time: 0.078 seconds
Progress count in Step: 0
Step 'Copy Data from ORDER_HISTORY to [backend].[dbo].[ORDER_HISTORY] Step'
succeeded
Step Execution Started: 11/10/04 3:28:54 PM
Step Execution Completed: 11/10/04 3:32:34 PM
Total Step Execution Time: 220.688 seconds
Progress count in Step: 118744
Step 'DTSStep_DTSExecuteSQLTask_1' succeeded
Step Execution Started: 11/10/04 3:32:34 PM
Step Execution Completed: 11/10/04 3:32:34 PM
Total Step Execution Time: 0.078 seconds
Progress count in Step: 0
Step 'DTSStep_DTSExecuteSQLTask_2' succeeded
Step Execution Started: 11/10/04 3:32:34 PM
Step Execution Completed: 11/10/04 3:32:35 PM
Total Step Execution Time: 0.016 seconds
Progress count in Step: 0Michael,
DTS Packages run in the context of the calling process. You should check
permissions of the job, and also check to see if the connections are valid
for the job user. For example, if you run the package from Enterprise
Manager on another machine than the server, the connections may be valid,
but run from the server they may fail.
Jon Jahren
"Michael D. McGill" <mcgillmd@.hotmail.com> wrote in message
news:#4wQAV2xEHA.1076@.TK2MSFTNGP10.phx.gbl...
> I have a DTS that runs fine when executed at the package level. However,
it
> always fails when it runs as a scheduled job through the SQL Server Agent.
> It always fails at ~93 seconds .
> Anybody else experiencing this?
>
> Here is the text for the failed scheduled DTS:
> The execution of the following DTS Package succeeded:
> Package Name: ORDER_HISTORY
> Package Description: (null)
> Package ID: {64B36A3E-7E07-46B5-BE7B-6DED729B7F70}
> Package Version: {7A9368AF-3407-4708-9AFB-3CAFAD5ADB12}
> Package Execution Lineage: {530DAB60-7837-406A-BDB3-37E577D79EE4}
> Executed On: MSCSQL
> Executed By: Administrator
> Execution Started: 11/10/04 3:33:22 PM
> Execution Completed: 11/10/04 3:34:55 PM
> Total Execution Time: 92.984 seconds
> Package Steps execution information:
>
> Step 'Create Table [backend].[dbo].[ORDER_HISTORY] Step' succeeded
> Step Execution Started: 11/10/04 3:33:22 PM
> Step Execution Completed: 11/10/04 3:33:22 PM
> Total Step Execution Time: 0.047 seconds
> Progress count in Step: 0
> Step 'Copy Data from ORDER_HISTORY to [backend].[dbo].[ORDER_HISTORY]
Step'
> failed
> Step Error Source: Microsoft OLE DB Provider for ODBC Drivers
> Step Error Description:[TOD][ODBC][Unknown]CONFIG: Expected a CONFIG call
> Step Error code: 80074005
> Step Error Help File:
> Step Error Help Context ID:0
> Step Execution Started: 11/10/04 3:33:22 PM
> Step Execution Completed: 11/10/04 3:34:55 PM
> Total Step Execution Time: 92.891 seconds
> Progress count in Step: 72000
> Step 'DTSStep_DTSExecuteSQLTask_1' was not executed
> Step 'DTSStep_DTSExecuteSQLTask_2' was not executed
>
****************************************************************************
> ****************
> Here is the DTS log for the successful manually run DTS:
> The execution of the following DTS Package succeeded:
> Package Name: ORDER_HISTORY
> Package Description: (null)
> Package ID: {64B36A3E-7E07-46B5-BE7B-6DED729B7F70}
> Package Version: {7A9368AF-3407-4708-9AFB-3CAFAD5ADB12}
> Package Execution Lineage: {578A9ED8-A483-4D6B-A169-EB952D5AF27B}
> Executed On: MSCSQL
> Executed By: Administrator
> Execution Started: 11/10/04 3:28:54 PM
> Execution Completed: 11/10/04 3:32:35 PM
> Total Execution Time: 220.906 seconds
> Package Steps execution information:
>
> Step 'Create Table [backend].[dbo].[ORDER_HISTORY] Step' succeeded
> Step Execution Started: 11/10/04 3:28:54 PM
> Step Execution Completed: 11/10/04 3:28:54 PM
> Total Step Execution Time: 0.078 seconds
> Progress count in Step: 0
> Step 'Copy Data from ORDER_HISTORY to [backend].[dbo].[ORDER_HISTORY]
Step'
> succeeded
> Step Execution Started: 11/10/04 3:28:54 PM
> Step Execution Completed: 11/10/04 3:32:34 PM
> Total Step Execution Time: 220.688 seconds
> Progress count in Step: 118744
> Step 'DTSStep_DTSExecuteSQLTask_1' succeeded
> Step Execution Started: 11/10/04 3:32:34 PM
> Step Execution Completed: 11/10/04 3:32:34 PM
> Total Step Execution Time: 0.078 seconds
> Progress count in Step: 0
> Step 'DTSStep_DTSExecuteSQLTask_2' succeeded
> Step Execution Started: 11/10/04 3:32:34 PM
> Step Execution Completed: 11/10/04 3:32:35 PM
> Total Step Execution Time: 0.016 seconds
> Progress count in Step: 0
>|||Check the job owner, too. Jobs owned by users not in the sa role will not
run in the context of the sql server service.
How to run a dts package as a scheduled job:
http://support.microsoft.com/default.aspx?scid=kb;en-us;269074
"Jon Jahren" <jon.jahren.fightspam@.sqlkompetanse.no> wrote in message
news:%23C0Nt18xEHA.1308@.TK2MSFTNGP09.phx.gbl...
> Michael,
> DTS Packages run in the context of the calling process. You should check
> permissions of the job, and also check to see if the connections are valid
> for the job user. For example, if you run the package from Enterprise
> Manager on another machine than the server, the connections may be valid,
> but run from the server they may fail.
> Jon Jahren
> "Michael D. McGill" <mcgillmd@.hotmail.com> wrote in message
> news:#4wQAV2xEHA.1076@.TK2MSFTNGP10.phx.gbl...
> > I have a DTS that runs fine when executed at the package level. However,
> it
> > always fails when it runs as a scheduled job through the SQL Server
Agent.
> >
> > It always fails at ~93 seconds .
> >
> > Anybody else experiencing this?
> >
> >
> > Here is the text for the failed scheduled DTS:
> >
> > The execution of the following DTS Package succeeded:
> >
> > Package Name: ORDER_HISTORY
> > Package Description: (null)
> > Package ID: {64B36A3E-7E07-46B5-BE7B-6DED729B7F70}
> > Package Version: {7A9368AF-3407-4708-9AFB-3CAFAD5ADB12}
> > Package Execution Lineage: {530DAB60-7837-406A-BDB3-37E577D79EE4}
> > Executed On: MSCSQL
> > Executed By: Administrator
> > Execution Started: 11/10/04 3:33:22 PM
> > Execution Completed: 11/10/04 3:34:55 PM
> > Total Execution Time: 92.984 seconds
> >
> > Package Steps execution information:
> >
> >
> > Step 'Create Table [backend].[dbo].[ORDER_HISTORY] Step' succeeded
> > Step Execution Started: 11/10/04 3:33:22 PM
> > Step Execution Completed: 11/10/04 3:33:22 PM
> > Total Step Execution Time: 0.047 seconds
> > Progress count in Step: 0
> >
> > Step 'Copy Data from ORDER_HISTORY to [backend].[dbo].[ORDER_HISTORY]
> Step'
> > failed
> >
> > Step Error Source: Microsoft OLE DB Provider for ODBC Drivers
> > Step Error Description:[TOD][ODBC][Unknown]CONFIG: Expected a CONFIG
call
> > Step Error code: 80074005
> > Step Error Help File:
> > Step Error Help Context ID:0
> >
> > Step Execution Started: 11/10/04 3:33:22 PM
> > Step Execution Completed: 11/10/04 3:34:55 PM
> > Total Step Execution Time: 92.891 seconds
> > Progress count in Step: 72000
> >
> > Step 'DTSStep_DTSExecuteSQLTask_1' was not executed
> >
> > Step 'DTSStep_DTSExecuteSQLTask_2' was not executed
> >
>
****************************************************************************
> > ****************
> >
> > Here is the DTS log for the successful manually run DTS:
> >
> > The execution of the following DTS Package succeeded:
> >
> > Package Name: ORDER_HISTORY
> > Package Description: (null)
> > Package ID: {64B36A3E-7E07-46B5-BE7B-6DED729B7F70}
> > Package Version: {7A9368AF-3407-4708-9AFB-3CAFAD5ADB12}
> > Package Execution Lineage: {578A9ED8-A483-4D6B-A169-EB952D5AF27B}
> > Executed On: MSCSQL
> > Executed By: Administrator
> > Execution Started: 11/10/04 3:28:54 PM
> > Execution Completed: 11/10/04 3:32:35 PM
> > Total Execution Time: 220.906 seconds
> >
> > Package Steps execution information:
> >
> >
> > Step 'Create Table [backend].[dbo].[ORDER_HISTORY] Step' succeeded
> > Step Execution Started: 11/10/04 3:28:54 PM
> > Step Execution Completed: 11/10/04 3:28:54 PM
> > Total Step Execution Time: 0.078 seconds
> > Progress count in Step: 0
> >
> > Step 'Copy Data from ORDER_HISTORY to [backend].[dbo].[ORDER_HISTORY]
> Step'
> > succeeded
> > Step Execution Started: 11/10/04 3:28:54 PM
> > Step Execution Completed: 11/10/04 3:32:34 PM
> > Total Step Execution Time: 220.688 seconds
> > Progress count in Step: 118744
> >
> > Step 'DTSStep_DTSExecuteSQLTask_1' succeeded
> > Step Execution Started: 11/10/04 3:32:34 PM
> > Step Execution Completed: 11/10/04 3:32:34 PM
> > Total Step Execution Time: 0.078 seconds
> > Progress count in Step: 0
> >
> > Step 'DTSStep_DTSExecuteSQLTask_2' succeeded
> > Step Execution Started: 11/10/04 3:32:34 PM
> > Step Execution Completed: 11/10/04 3:32:35 PM
> > Total Step Execution Time: 0.016 seconds
> > Progress count in Step: 0
> >
> >
>