Scheduling SSIS Package

Hi

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

Anybody help me for configure jobs for Scheduling

I am new to SQL Server

Thanks in Advance

Now I want to save package in MSDB database but its gives en error as follows

"Login timeout expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Named Pipes Provider: Could not open a connection to SQL Server [2]. "

Can anybody helpout for this error

Note : Whats modification in SQL Server Setting so did not get this error

Actually here i am trying to save a package in MSDB database for run package through Sql Server job

thnaks in advance for your help

|||

Hi,

I am new to SSIS but have worked on other ETL tools. I was trying to schedule and run a SSIS package using SQL Server Agent. I created the job but when i tried to make a step of the Job, it asks for a type of step in a drop down. I cant see the step type required for running the SSIS packages as mentioned in the help files.

Please help me on this.

Regards,

Samaksh

|||

Doesn't matter. Execute your packages from the cmdexec subsystem using dtexec. You will get more useful error messages as explained here: http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

-Jamie

|||Don't you see the type "SQL Server Integration Services Package" as one option in the dropdown? If you choose that, you can select where your package is.|||

Yes, the help file says to look at the "SQL Server Integration Services Package" as the option in the drop down, but i am unable to see it as an option. I tried various ways to check if the i can get to it, but each time "SQL Server Integration Services Package" is not there as an option.

Can this be the issue with the licence of the product or something similar. Because this is bizarre that the option is not coming in the list.

Regards,

Samaksh

Scheduling SSIS Package

Hi

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

Anybody help me for configure jobs for Scheduling

I am new to SQL Server

Thanks in Advance

Now I want to save package in MSDB database but its gives en error as follows

"Login timeout expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Named Pipes Provider: Could not open a connection to SQL Server [2]. "

Can anybody helpout for this error

Note : Whats modification in SQL Server Setting so did not get this error

Actually here i am trying to save a package in MSDB database for run package through Sql Server job

thnaks in advance for your help

|||

Hi,

I am new to SSIS but have worked on other ETL tools. I was trying to schedule and run a SSIS package using SQL Server Agent. I created the job but when i tried to make a step of the Job, it asks for a type of step in a drop down. I cant see the step type required for running the SSIS packages as mentioned in the help files.

Please help me on this.

Regards,

Samaksh

|||

Doesn't matter. Execute your packages from the cmdexec subsystem using dtexec. You will get more useful error messages as explained here: http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

-Jamie

|||Don't you see the type "SQL Server Integration Services Package" as one option in the dropdown? If you choose that, you can select where your package is.|||

Yes, the help file says to look at the "SQL Server Integration Services Package" as the option in the drop down, but i am unable to see it as an option. I tried various ways to check if the i can get to it, but each time "SQL Server Integration Services Package" is not there as an option.

Can this be the issue with the licence of the product or something similar. Because this is bizarre that the option is not coming in the list.

Regards,

Samaksh

Scheduling Maintenance Jobs

Hi,

I have setup some maintenance jobs in SQL 2005 but when I try to schedule them I and then "save" the changes I get an error in a popup dialog saying

Caption "Microsoft SQL server management studio"

Error "Guid should contain 32 digits with 4 dashes (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx)"

I have no idea what is causing this but it is preventing me from schduling my maintanance tasks. I would really appriciate any assistance with this.

Many thanks

Derek

Hi Derek,

The link http://support.microsoft.com/kb/922651 provides a workaround for the problem.

Thanks,

Kuntal

Scheduler not yielding?

All,

I'm getting the following error in my SQL logs:

Process 51:16 (2bc) UMS Context 0x1249F118 appears to be non-yielding on Scheduler 0.

I killed the process, but it's been rolling back for 3 hours. It's done zero IO and consumed a TON of CPU time.

Any suggestions as to what I can do?

Thanks,

Isaacpost details abt scheduler job.Are u executing DBCC DBREINDEX in that scheduler? what version of SQL server u are using? I hope it is SQL server 2000.If it is did u applied latest service pack?

Scheduled transaction log backup never finish

Cancel the job using the Process Info under Current
Activity.
Have a look and see if there are any error messages being
generated in the SQL Server Logs, then try again.
Peter
"Never ascribe to malice, that which can be explained by
incompetence."
Napoleon

>--Original Message--
>Hi,
>I run a scheduled transaction log job back up serval
database's log
>everyday, it was fine before, however, today, one of jobs
seems can not stop
>running. Checked no deadlock process, checked log file in
disk, only two
>database can not finish. using dbcc opentran, no active
tran. change those
>two database to another job, same thing happening, What
could I do?
>Any help would be appreciated.
>Cheers
>Yifei
>
>.
>
Hi Peter,
Thank you for reply, I do cancel the job and restart it again, the problem
still exist.
Actually I restart sql server, it's been fixed.
But, it looks strange.
Yifei
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:18a901c51b1f$feb702f0$a601280a@.phx.gbl...[vbcol=seagreen]
> Cancel the job using the Process Info under Current
> Activity.
> Have a look and see if there are any error messages being
> generated in the SQL Server Logs, then try again.
> Peter
> "Never ascribe to malice, that which can be explained by
> incompetence."
> Napoleon
>
> database's log
> seems can not stop
> disk, only two
> tran. change those
> could I do?

Scheduled transaction log backup never finish

Cancel the job using the Process Info under Current
Activity.
Have a look and see if there are any error messages being
generated in the SQL Server Logs, then try again.
Peter
"Never ascribe to malice, that which can be explained by
incompetence."
Napoleon

>--Original Message--
>Hi,
>I run a scheduled transaction log job back up serval
database's log
>everyday, it was fine before, however, today, one of jobs
seems can not stop
>running. Checked no deadlock process, checked log file in
disk, only two
>database can not finish. using dbcc opentran, no active
tran. change those
>two database to another job, same thing happening, What
could I do?
>Any help would be appreciated.
>Cheers
>Yifei
>
>.
>Hi Peter,
Thank you for reply, I do cancel the job and restart it again, the problem
still exist.
Actually I restart sql server, it's been fixed.
But, it looks strange.
Yifei
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:18a901c51b1f$feb702f0$a601280a@.phx.gbl...[vbcol=seagreen]
> Cancel the job using the Process Info under Current
> Activity.
> Have a look and see if there are any error messages being
> generated in the SQL Server Logs, then try again.
> Peter
> "Never ascribe to malice, that which can be explained by
> incompetence."
> Napoleon
>
>
> database's log
> seems can not stop
> disk, only two
> tran. change those
> could I do?

Scheduled report by email with attached report fails

Can anyone offer any help with this?

Error maesage:
Status:
Failure sending mail: The Report Server has encountered a configuration error; more details in the log files

Windows 200 Server: SP4
RS 2000: SP2
SQL Server 2000, on the same server as RS.

We

get this error whenever we try to run a report with the report

attached. However, the email sends fine if the report is not attached.

Reports can also be exported into different formats without problem when viewing the report through report manager.
I received a email telling me there had been a reply to this thread, but I see nothing.

Can anyone help with this?|||Did you check the log files to see what the error is?|||Bringing this topic back from the dead a little I know, but I've finally had chance to get back to trying to use this since being switched onto other projects.

Which log would likely contain the information needed.
The logs located at: C:\Program Files\Microsoft SQL Server\MSSQL\LOG
called: SQLAGENT.OUT ?|||I am having the same problem. The report service log file only re-iterates the message, and does not provide any further details.
I checked the Report Server configuration file and it looks okay, nothing unusual.
Does anyone have any idea what may be causing the problem?|||

The RS log files are located here:

%Program Files%\Microsft SQL Server\MSSQL.2\Reporting Services\LogFiles

The Windows Service log file (which processes subscription events) should have a name like this:

ReportServerService__03_19_2007_00_01_09.log

|||

No entry is made in that folder when it fails the job.

However, we have noticed that if my colleague creates the subscription then it works fine without the error. So I suspect it might be related to permissions in sql server or on the win 2000 server box. So far I've been unable to determine which permission is causing it. Despite ensuring I am in the same permission groups as he is, my subscriptions still fail.

Scheduled report by email with attached report fails

Can anyone offer any help with this?

Error maesage:
Status:
Failure sending mail: The Report Server has encountered a configuration error; more details in the log files

Windows 200 Server: SP4
RS 2000: SP2
SQL Server 2000, on the same server as RS.

We

get this error whenever we try to run a report with the report

attached. However, the email sends fine if the report is not attached.

Reports can also be exported into different formats without problem when viewing the report through report manager.
I received a email telling me there had been a reply to this thread, but I see nothing.

Can anyone help with this?|||Did you check the log files to see what the error is?|||Bringing this topic back from the dead a little I know, but I've finally had chance to get back to trying to use this since being switched onto other projects.

Which log would likely contain the information needed.
The logs located at: C:\Program Files\Microsoft SQL Server\MSSQL\LOG
called: SQLAGENT.OUT ?|||I am having the same problem. The report service log file only re-iterates the message, and does not provide any further details.
I checked the Report Server configuration file and it looks okay, nothing unusual.
Does anyone have any idea what may be causing the problem?|||

The RS log files are located here:

%Program Files%\Microsft SQL Server\MSSQL.2\Reporting Services\LogFiles

The Windows Service log file (which processes subscription events) should have a name like this:

ReportServerService__03_19_2007_00_01_09.log

|||

No entry is made in that folder when it fails the job.

However, we have noticed that if my colleague creates the subscription then it works fine without the error. So I suspect it might be related to permissions in sql server or on the win 2000 server box. So far I've been unable to determine which permission is causing it. Despite ensuring I am in the same permission groups as he is, my subscriptions still fail.

Scheduled report by email with attached report fails

Can anyone offer any help with this?

Error maesage:
Status:
Failure sending mail: The Report Server has encountered a configuration error; more details in the log files

Windows 200 Server: SP4
RS 2000: SP2
SQL Server 2000, on the same server as RS.

We get this error whenever we try to run a report with the report attached. However, the email sends fine if the report is not attached.

Reports can also be exported into different formats without problem when viewing the report through report manager.
I received a email telling me there had been a reply to this thread, but I see nothing.

Can anyone help with this?
|||Did you check the log files to see what the error is?|||Bringing this topic back from the dead a little I know, but I've finally had chance to get back to trying to use this since being switched onto other projects.

Which log would likely contain the information needed.
The logs located at: C:\Program Files\Microsoft SQL Server\MSSQL\LOG
called: SQLAGENT.OUT ?
|||I am having the same problem. The report service log file only re-iterates the message, and does not provide any further details.
I checked the Report Server configuration file and it looks okay, nothing unusual.
Does anyone have any idea what may be causing the problem?|||

The RS log files are located here:

%Program Files%\Microsft SQL Server\MSSQL.2\Reporting Services\LogFiles

The Windows Service log file (which processes subscription events) should have a name like this:

ReportServerService__03_19_2007_00_01_09.log

|||

No entry is made in that folder when it fails the job.

However, we have noticed that if my colleague creates the subscription then it works fine without the error. So I suspect it might be related to permissions in sql server or on the win 2000 server box. So far I've been unable to determine which permission is causing it. Despite ensuring I am in the same permission groups as he is, my subscriptions still fail.

Scheduled Jobs & emails ( Sql 2k)

Hi All,
The name of the Server was changed which in turn gave me the following error when I tried to delete the jobs

Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server.
The job was not saved.

Knowing that it has to do with the Originator server in the sysjobs table; I hacked the table ( now I think not a good idea ) and deleted all the jobs that I wanted to delete and performed the same task on sysjobservers.

Now although I have accomplished the deletion of the jobs.
But I still get emails ( job failure notifications) for the deleted jobs exactly at the time they were scheduled.
This is driving me crazy as I do not how to stop these emails .

Any help is appreciated.

Thank you

1. Check sysnotifications table, if it still has notifications for those delted jobs, clean them as well.

2. If sysnotifications table does not have notifications then restarting SQL Agent service should defintely work.

How did you delete the job ? If you had used sp_delete_job SP then it would have taken care of cleaning all the dependent objects. Did you delete them usign TSQL delete statement ?

In case when server name was changed, instead of deleting the jobs, better option would have been to updating sysoriginatingservers table with the new server name.

Thanks,

Gops Dwarak, MSFT

|||

Thank you for your response.

The sysnotification table has been cleared.

I did restart that SQL Server Agent ( hoping this will fix the issue; but I guess I have to wait till tonight when the emails get generated ).

I did use TSQL to delete the jobs ( my bad).

Is there any other way to confirm that I will not be receiving those emails any more.

Thank you

|||

That is really surprising!

Did you try to recreate jobs that you deleted ? If not you can try recreating dummy jobs with same name and with no notification this time.

Other option I can think of is to disable the mail profile and restart SQL Agent . Now enable the profile back and restart SQL Agent and check for the behavior.

Gops Dwarak

|||

I did restart the SQL Server Agant and the notifications stoppped coming.

Thank you for your help.

|||

Gops,

I picked up this useful tip as am experiencing similar problems when server was cloned and renamed.

However, cannot find the sysoriginatingservers table.

Please tell me where this table resides.

Thanks,

Dickin

Scheduled Jobs & emails ( Sql 2k)

Hi All,
The name of the Server was changed which in turn gave me the following error when I tried to delete the jobs

Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server.
The job was not saved.

Knowing that it has to do with the Originator server in the sysjobs table; I hacked the table ( now I think not a good idea ) and deleted all the jobs that I wanted to delete and performed the same task on sysjobservers.

Now although I have accomplished the deletion of the jobs.
But I still get emails ( job failure notifications) for the deleted jobs exactly at the time they were scheduled.
This is driving me crazy as I do not how to stop these emails .

Any help is appreciated.

Thank you

1. Check sysnotifications table, if it still has notifications for those delted jobs, clean them as well.

2. If sysnotifications table does not have notifications then restarting SQL Agent service should defintely work.

How did you delete the job ? If you had used sp_delete_job SP then it would have taken care of cleaning all the dependent objects. Did you delete them usign TSQL delete statement ?

In case when server name was changed, instead of deleting the jobs, better option would have been to updating sysoriginatingservers table with the new server name.

Thanks,

Gops Dwarak, MSFT

|||

Thank you for your response.

The sysnotification table has been cleared.

I did restart that SQL Server Agent ( hoping this will fix the issue; but I guess I have to wait till tonight when the emails get generated ).

I did use TSQL to delete the jobs ( my bad).

Is there any other way to confirm that I will not be receiving those emails any more.

Thank you

|||

That is really surprising!

Did you try to recreate jobs that you deleted ? If not you can try recreating dummy jobs with same name and with no notification this time.

Other option I can think of is to disable the mail profile and restart SQL Agent . Now enable the profile back and restart SQL Agent and check for the behavior.

Gops Dwarak

|||

I did restart the SQL Server Agant and the notifications stoppped coming.

Thank you for your help.

|||

Gops,

I picked up this useful tip as am experiencing similar problems when server was cloned and renamed.

However, cannot find the sysoriginatingservers table.

Please tell me where this table resides.

Thanks,

Dickin

Scheduled Job Sometimes Error Sometimes Not

I have a scheduled job contain 2 steps. Step 1 is execute DTS Package
using CmdExec 'DTSRun', step 2 using TSQL to execute just simple query.
This job execute recurely every day, every 6 hours.
The problem is, sometimes this job was successfull, but sometimes
failed with error as below :

...
...
Process Exit Code -1073741819. The step failed.

Please help me, what the cause of the error?
Any help would be greatly appreciated.Resant (resant_v@.yahoo.com) writes:
> I have a scheduled job contain 2 steps. Step 1 is execute DTS Package
> using CmdExec 'DTSRun', step 2 using TSQL to execute just simple query.
> This job execute recurely every day, every 6 hours.
> The problem is, sometimes this job was successfull, but sometimes
> failed with error as below :
> ...
> ...
> Process Exit Code -1073741819. The step failed.
> Please help me, what the cause of the error?
> Any help would be greatly appreciated.

In View Job History, there is a check box for "Show step details". Check
that box, and you may find some more details.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for your reply.
The error that I've post is taken after I've checked "Show step
details". OK, I'll post the error again completely :

Executed as user: XXXX\xxxx. ...un OnStart:
DTSStep_DTSExecuteSQLTask_6 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_6 DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_10 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_10 DTSRun OnStart:
DTSStep_DTSDataPumpTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_2
DTSRun OnStart: DTSStep_DTSDataPumpTask_4 DTSRun OnStart:
DTSStep_DTSDataPumpTask_3 DTSRun OnStart: DTSStep_DTSDataPumpTask_2
DTSRun OnStart: DTSStep_DTSDataPumpTask_4 DTSRun OnStart:
DTSStep_DTSDataPumpTask_4 DTSRun OnStart: DTSStep_DTSDataPumpTask_4
DTSRun OnStart: DTSStep_DTSDataPumpTask_4 DTSRun OnStart:
DTSStep_DTSDataPumpTask_4 DTSRun OnStart: DTSStep_DTSDataPumpTask_4
DTSRun OnStart: DTSStep_DTSDataPumpTask_4 DTSRun OnStart:
DTSStep_DTSDataPumpTask_4 DTSRun OnStart: DTSStep_DTSDataPumpTask_4
DTSRun OnStart: DTSStep_DTSDataPumpTask_4 DTSRun OnStart:
DTSStep_DTSDataPumpTask_4 DTSRun OnStart: DTSStep_DTS... Process
Exit Code -1073741819. The step failed.|||Resant (resant_v@.yahoo.com) writes:
> Thanks for your reply.
> The error that I've post is taken after I've checked "Show step
> details". OK, I'll post the error again completely :
> Executed as user: XXXX\xxxx. ...un OnStart:
> DTSStep_DTSExecuteSQLTask_6 DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_6 DTSRun OnStart:
> DTSStep_DTSExecuteSQLTask_10 DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_10 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_2
> DTSRun OnStart: DTSStep_DTSDataPumpTask_4 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_3 DTSRun OnStart: DTSStep_DTSDataPumpTask_2
> DTSRun OnStart: DTSStep_DTSDataPumpTask_4 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_4 DTSRun OnStart: DTSStep_DTSDataPumpTask_4
> DTSRun OnStart: DTSStep_DTSDataPumpTask_4 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_4 DTSRun OnStart: DTSStep_DTSDataPumpTask_4
> DTSRun OnStart: DTSStep_DTSDataPumpTask_4 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_4 DTSRun OnStart: DTSStep_DTSDataPumpTask_4
> DTSRun OnStart: DTSStep_DTSDataPumpTask_4 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_4 DTSRun OnStart: DTSStep_DTS... Process
> Exit Code -1073741819. The step failed.

That's pretty anonymous. Unfortunately I don't know DTS, so I don't
have any suggestions to offer. Maybe the people in
microsoft.public.sqlserver.dts has any ideas.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Some Googling suggests that the error may be caused by a Windows bug:

http://support.microsoft.com/defaul...kb;EN-US;306497
http://groups-beta.google.com/group...lr=&sa=N&tab=wg

If you don't already have the latest servicepacks for Windows and
MSSQL, it would probably be worth installing them to see if that
resolves the issue. Since a few people seem to have seen this error in
connection with a virus infection, that would be something else to
check. If this doesn't help, then it might be worth calling Microsoft.

Simon

Scheduled job for backup

My scheduled job didnt run. there's no error message that appear on my my
sql server even when i set up the job, that' why i concluded that i did it
correctly. What could be the problem?
Thanks a lot!Verify that Agent service is running.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"newbie" <newbie@.discussions.microsoft.com> wrote in message
news:FEC20577-CA41-44A0-AF41-366835F62A54@.microsoft.com...
> My scheduled job didnt run. there's no error message that appear on my my
> sql server even when i set up the job, that' why i concluded that i did it
> correctly. What could be the problem?
> Thanks a lot!

Scheduled Job Error

I am having an issue with a dts package.
I am able to execute the package as myself from enterprise manager but
I recieve an error when running it as a scheduled job. I have read
http://support.microsoft.com/kb/269074/en-us and am either missing
something (highly likly) or it doesn't apply to my particular set of
circumstances.
The dts task uses a "Text File (destination)" Object with a unc path in
its properties
\\remoteserver\d$\nested\folder\input.txt
The job is owned by SA and is executed by our domains sql account
\\mydomain\sqlservice
\\mydomain\sqlservice has been set up with identical permission as
\\mydomain\myaccount to \\remoteserver\d$\nested\folder\

>From the header of the package logs
Executed as user:
MYDOMAIN\sqlservice.
..Start: DTSStep_DTSExecuteSQLTask_1
SQL Task 1 runs just fine (import routines which massage data from
\\anotherserver\inputdir)
Then as soon as it tries to export the file it fails.
DTSRun OnError: DTSStep_DTSDataPumpTask_3, Error = -2147467259
(80004005)
Error string: Error opening datafile: Access is denied.
Error source: Microsoft Data Transformation Services Flat File Rowset
Provider
Help file: DTSFFile.hlp Help co...
Process Exit Code 1.
The step failed.
Seems like a cut and dried case of permissions - but
\\MYDOMAIN\sqlservice has access to the location.
ARRRG! Please advise.
Maxmake sure that your sql server agent is running. Look under the management
folder in IE and it should have a green arrow in front of it, if it had a re
d
square it is not running.
--
Paul G
Software engineer.
"xamfear@.comcast.net" wrote:

> I am having an issue with a dts package.
> I am able to execute the package as myself from enterprise manager but
> I recieve an error when running it as a scheduled job. I have read
> http://support.microsoft.com/kb/269074/en-us and am either missing
> something (highly likly) or it doesn't apply to my particular set of
> circumstances.
> The dts task uses a "Text File (destination)" Object with a unc path in
> its properties
> \\remoteserver\d$\nested\folder\input.txt
> The job is owned by SA and is executed by our domains sql account
> \\mydomain\sqlservice
> \\mydomain\sqlservice has been set up with identical permission as
> \\mydomain\myaccount to \\remoteserver\d$\nested\folder\
>
> Executed as user:
> MYDOMAIN\sqlservice.
> ...Start: DTSStep_DTSExecuteSQLTask_1
> SQL Task 1 runs just fine (import routines which massage data from
> \\anotherserver\inputdir)
> Then as soon as it tries to export the file it fails.
> DTSRun OnError: DTSStep_DTSDataPumpTask_3, Error = -2147467259
> (80004005)
> Error string: Error opening datafile: Access is denied.
> Error source: Microsoft Data Transformation Services Flat File Rowset
> Provider
> Help file: DTSFFile.hlp Help co...
> Process Exit Code 1.
> The step failed.
> Seems like a cut and dried case of permissions - but
> \\MYDOMAIN\sqlservice has access to the location.
> ARRRG! Please advise.
> Max
>|||look under the management folder in Enterprise manager, not IE as I specifie
d
below.
--
Paul G
Software engineer.
"Paul" wrote:
[vbcol=seagreen]
> make sure that your sql server agent is running. Look under the managemen
t
> folder in IE and it should have a green arrow in front of it, if it had a
red
> square it is not running.
> --
> Paul G
> Software engineer.
>
> "xamfear@.comcast.net" wrote:
>|||Thanks for taking the time to reply Paul. The agent is running, and we
know it runs fine since other jobs are run as the agent just fine, and
in this case the other steps of the Job execute fine as well.
Thanks
Max
Paul wrote:[vbcol=seagreen]
> look under the management folder in Enterprise manager, not IE as I specif
ied
> below.
> --
> Paul G
> Software engineer.
>
> "Paul" wrote:
>|||ok, not sure off hand what the problem is, hopefully someone will respond
shortly.
--
Paul G
Software engineer.
"xamfear@.comcast.net" wrote:

> Thanks for taking the time to reply Paul. The agent is running, and we
> know it runs fine since other jobs are run as the agent just fine, and
> in this case the other steps of the Job execute fine as well.
> Thanks
> Max
> Paul wrote:
>|||That is a permissions error. Is the SQL Agent service
account also running under the domain account? The way you
have this set up, the SQL Agent service account would need
to be an administrator on the remote server - if it's not
then that's probably the issue.
Try creating a share on the remote server and give the SQL
Agent service account full permissions to this share. Then
reference the share you just created instead of the Admin
share you referenced using for the file location.
-Sue
On 29 Aug 2006 14:09:39 -0700, xamfear@.comcast.net wrote:

>I am having an issue with a dts package.
>I am able to execute the package as myself from enterprise manager but
>I recieve an error when running it as a scheduled job. I have read
>http://support.microsoft.com/kb/269074/en-us and am either missing
>something (highly likly) or it doesn't apply to my particular set of
>circumstances.
>The dts task uses a "Text File (destination)" Object with a unc path in
>its properties
>\\remoteserver\d$\nested\folder\input.txt
>The job is owned by SA and is executed by our domains sql account
>\\mydomain\sqlservice
>\\mydomain\sqlservice has been set up with identical permission as
>\\mydomain\myaccount to \\remoteserver\d$\nested\folder\
>
>Executed as user:
> MYDOMAIN\sqlservice.
> ...Start: DTSStep_DTSExecuteSQLTask_1
>SQL Task 1 runs just fine (import routines which massage data from
>\\anotherserver\inputdir)
>Then as soon as it tries to export the file it fails.
> DTSRun OnError: DTSStep_DTSDataPumpTask_3, Error = -2147467259
>(80004005)
> Error string: Error opening datafile: Access is denied.
> Error source: Microsoft Data Transformation Services Flat File Rowset
>Provider
> Help file: DTSFFile.hlp Help co...
> Process Exit Code 1.
> The step failed.
>Seems like a cut and dried case of permissions - but
>\\MYDOMAIN\sqlservice has access to the location.
>ARRRG! Please advise.
>Max

Scheduled Job Error

I am having an issue with a dts package.
I am able to execute the package as myself from enterprise manager but
I recieve an error when running it as a scheduled job. I have read
http://support.microsoft.com/kb/269074/en-us and am either missing
something (highly likly) or it doesn't apply to my particular set of
circumstances.
The dts task uses a "Text File (destination)" Object with a unc path in
its properties
\\remoteserver\d$\nested\folder\input.txt
The job is owned by SA and is executed by our domains sql account
\\mydomain\sqlservice
\\mydomain\sqlservice has been set up with identical permission as
\\mydomain\myaccount to \\remoteserver\d$\nested\folder\
>From the header of the package logs
Executed as user:
MYDOMAIN\sqlservice.
...Start: DTSStep_DTSExecuteSQLTask_1
SQL Task 1 runs just fine (import routines which massage data from
\\anotherserver\inputdir)
Then as soon as it tries to export the file it fails.
DTSRun OnError: DTSStep_DTSDataPumpTask_3, Error = -2147467259
(80004005)
Error string: Error opening datafile: Access is denied.
Error source: Microsoft Data Transformation Services Flat File Rowset
Provider
Help file: DTSFFile.hlp Help co...
Process Exit Code 1.
The step failed.
Seems like a cut and dried case of permissions - but
\\MYDOMAIN\sqlservice has access to the location.
ARRRG! Please advise.
Maxmake sure that your sql server agent is running. Look under the management
folder in IE and it should have a green arrow in front of it, if it had a red
square it is not running.
--
Paul G
Software engineer.
"xamfear@.comcast.net" wrote:
> I am having an issue with a dts package.
> I am able to execute the package as myself from enterprise manager but
> I recieve an error when running it as a scheduled job. I have read
> http://support.microsoft.com/kb/269074/en-us and am either missing
> something (highly likly) or it doesn't apply to my particular set of
> circumstances.
> The dts task uses a "Text File (destination)" Object with a unc path in
> its properties
> \\remoteserver\d$\nested\folder\input.txt
> The job is owned by SA and is executed by our domains sql account
> \\mydomain\sqlservice
> \\mydomain\sqlservice has been set up with identical permission as
> \\mydomain\myaccount to \\remoteserver\d$\nested\folder\
> >From the header of the package logs
> Executed as user:
> MYDOMAIN\sqlservice.
> ...Start: DTSStep_DTSExecuteSQLTask_1
> SQL Task 1 runs just fine (import routines which massage data from
> \\anotherserver\inputdir)
> Then as soon as it tries to export the file it fails.
> DTSRun OnError: DTSStep_DTSDataPumpTask_3, Error = -2147467259
> (80004005)
> Error string: Error opening datafile: Access is denied.
> Error source: Microsoft Data Transformation Services Flat File Rowset
> Provider
> Help file: DTSFFile.hlp Help co...
> Process Exit Code 1.
> The step failed.
> Seems like a cut and dried case of permissions - but
> \\MYDOMAIN\sqlservice has access to the location.
> ARRRG! Please advise.
> Max
>|||look under the management folder in Enterprise manager, not IE as I specified
below.
--
Paul G
Software engineer.
"Paul" wrote:
> make sure that your sql server agent is running. Look under the management
> folder in IE and it should have a green arrow in front of it, if it had a red
> square it is not running.
> --
> Paul G
> Software engineer.
>
> "xamfear@.comcast.net" wrote:
> > I am having an issue with a dts package.
> > I am able to execute the package as myself from enterprise manager but
> > I recieve an error when running it as a scheduled job. I have read
> > http://support.microsoft.com/kb/269074/en-us and am either missing
> > something (highly likly) or it doesn't apply to my particular set of
> > circumstances.
> >
> > The dts task uses a "Text File (destination)" Object with a unc path in
> > its properties
> > \\remoteserver\d$\nested\folder\input.txt
> >
> > The job is owned by SA and is executed by our domains sql account
> > \\mydomain\sqlservice
> >
> > \\mydomain\sqlservice has been set up with identical permission as
> > \\mydomain\myaccount to \\remoteserver\d$\nested\folder\
> >
> > >From the header of the package logs
> > Executed as user:
> > MYDOMAIN\sqlservice.
> > ...Start: DTSStep_DTSExecuteSQLTask_1
> >
> > SQL Task 1 runs just fine (import routines which massage data from
> > \\anotherserver\inputdir)
> > Then as soon as it tries to export the file it fails.
> >
> > DTSRun OnError: DTSStep_DTSDataPumpTask_3, Error = -2147467259
> > (80004005)
> > Error string: Error opening datafile: Access is denied.
> > Error source: Microsoft Data Transformation Services Flat File Rowset
> > Provider
> > Help file: DTSFFile.hlp Help co...
> > Process Exit Code 1.
> > The step failed.
> >
> > Seems like a cut and dried case of permissions - but
> > \\MYDOMAIN\sqlservice has access to the location.
> >
> > ARRRG! Please advise.
> > Max
> >
> >|||Thanks for taking the time to reply Paul. The agent is running, and we
know it runs fine since other jobs are run as the agent just fine, and
in this case the other steps of the Job execute fine as well.
Thanks
Max
Paul wrote:
> look under the management folder in Enterprise manager, not IE as I specified
> below.
> --
> Paul G
> Software engineer.
>
> "Paul" wrote:
> > make sure that your sql server agent is running. Look under the management
> > folder in IE and it should have a green arrow in front of it, if it had a red
> > square it is not running.
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "xamfear@.comcast.net" wrote:
> >
> > > I am having an issue with a dts package.
> > > I am able to execute the package as myself from enterprise manager but
> > > I recieve an error when running it as a scheduled job. I have read
> > > http://support.microsoft.com/kb/269074/en-us and am either missing
> > > something (highly likly) or it doesn't apply to my particular set of
> > > circumstances.
> > >
> > > The dts task uses a "Text File (destination)" Object with a unc path in
> > > its properties
> > > \\remoteserver\d$\nested\folder\input.txt
> > >
> > > The job is owned by SA and is executed by our domains sql account
> > > \\mydomain\sqlservice
> > >
> > > \\mydomain\sqlservice has been set up with identical permission as
> > > \\mydomain\myaccount to \\remoteserver\d$\nested\folder\
> > >
> > > >From the header of the package logs
> > > Executed as user:
> > > MYDOMAIN\sqlservice.
> > > ...Start: DTSStep_DTSExecuteSQLTask_1
> > >
> > > SQL Task 1 runs just fine (import routines which massage data from
> > > \\anotherserver\inputdir)
> > > Then as soon as it tries to export the file it fails.
> > >
> > > DTSRun OnError: DTSStep_DTSDataPumpTask_3, Error = -2147467259
> > > (80004005)
> > > Error string: Error opening datafile: Access is denied.
> > > Error source: Microsoft Data Transformation Services Flat File Rowset
> > > Provider
> > > Help file: DTSFFile.hlp Help co...
> > > Process Exit Code 1.
> > > The step failed.
> > >
> > > Seems like a cut and dried case of permissions - but
> > > \\MYDOMAIN\sqlservice has access to the location.
> > >
> > > ARRRG! Please advise.
> > > Max
> > >
> > >|||ok, not sure off hand what the problem is, hopefully someone will respond
shortly.
--
Paul G
Software engineer.
"xamfear@.comcast.net" wrote:
> Thanks for taking the time to reply Paul. The agent is running, and we
> know it runs fine since other jobs are run as the agent just fine, and
> in this case the other steps of the Job execute fine as well.
> Thanks
> Max
> Paul wrote:
> > look under the management folder in Enterprise manager, not IE as I specified
> > below.
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "Paul" wrote:
> >
> > > make sure that your sql server agent is running. Look under the management
> > > folder in IE and it should have a green arrow in front of it, if it had a red
> > > square it is not running.
> > > --
> > > Paul G
> > > Software engineer.
> > >
> > >
> > > "xamfear@.comcast.net" wrote:
> > >
> > > > I am having an issue with a dts package.
> > > > I am able to execute the package as myself from enterprise manager but
> > > > I recieve an error when running it as a scheduled job. I have read
> > > > http://support.microsoft.com/kb/269074/en-us and am either missing
> > > > something (highly likly) or it doesn't apply to my particular set of
> > > > circumstances.
> > > >
> > > > The dts task uses a "Text File (destination)" Object with a unc path in
> > > > its properties
> > > > \\remoteserver\d$\nested\folder\input.txt
> > > >
> > > > The job is owned by SA and is executed by our domains sql account
> > > > \\mydomain\sqlservice
> > > >
> > > > \\mydomain\sqlservice has been set up with identical permission as
> > > > \\mydomain\myaccount to \\remoteserver\d$\nested\folder\
> > > >
> > > > >From the header of the package logs
> > > > Executed as user:
> > > > MYDOMAIN\sqlservice.
> > > > ...Start: DTSStep_DTSExecuteSQLTask_1
> > > >
> > > > SQL Task 1 runs just fine (import routines which massage data from
> > > > \\anotherserver\inputdir)
> > > > Then as soon as it tries to export the file it fails.
> > > >
> > > > DTSRun OnError: DTSStep_DTSDataPumpTask_3, Error = -2147467259
> > > > (80004005)
> > > > Error string: Error opening datafile: Access is denied.
> > > > Error source: Microsoft Data Transformation Services Flat File Rowset
> > > > Provider
> > > > Help file: DTSFFile.hlp Help co...
> > > > Process Exit Code 1.
> > > > The step failed.
> > > >
> > > > Seems like a cut and dried case of permissions - but
> > > > \\MYDOMAIN\sqlservice has access to the location.
> > > >
> > > > ARRRG! Please advise.
> > > > Max
> > > >
> > > >
>|||That is a permissions error. Is the SQL Agent service
account also running under the domain account? The way you
have this set up, the SQL Agent service account would need
to be an administrator on the remote server - if it's not
then that's probably the issue.
Try creating a share on the remote server and give the SQL
Agent service account full permissions to this share. Then
reference the share you just created instead of the Admin
share you referenced using for the file location.
-Sue
On 29 Aug 2006 14:09:39 -0700, xamfear@.comcast.net wrote:
>I am having an issue with a dts package.
>I am able to execute the package as myself from enterprise manager but
>I recieve an error when running it as a scheduled job. I have read
>http://support.microsoft.com/kb/269074/en-us and am either missing
>something (highly likly) or it doesn't apply to my particular set of
>circumstances.
>The dts task uses a "Text File (destination)" Object with a unc path in
>its properties
>\\remoteserver\d$\nested\folder\input.txt
>The job is owned by SA and is executed by our domains sql account
>\\mydomain\sqlservice
>\\mydomain\sqlservice has been set up with identical permission as
>\\mydomain\myaccount to \\remoteserver\d$\nested\folder\
>>From the header of the package logs
>Executed as user:
> MYDOMAIN\sqlservice.
> ...Start: DTSStep_DTSExecuteSQLTask_1
>SQL Task 1 runs just fine (import routines which massage data from
>\\anotherserver\inputdir)
>Then as soon as it tries to export the file it fails.
> DTSRun OnError: DTSStep_DTSDataPumpTask_3, Error = -2147467259
>(80004005)
> Error string: Error opening datafile: Access is denied.
> Error source: Microsoft Data Transformation Services Flat File Rowset
>Provider
> Help file: DTSFFile.hlp Help co...
> Process Exit Code 1.
> The step failed.
>Seems like a cut and dried case of permissions - but
>\\MYDOMAIN\sqlservice has access to the location.
>ARRRG! Please advise.
>Max

Scheduled events along with regular events

Hi!

Does it allowable to use scheduled events and regular events together in one subscription class? I get the error when I use then simultaneously. The description of error is
New request is not allowed to start because it should come with valid transaction descriptor.

It is being generated when the rule tries to run.

If I understand your question, you want to know if one subscription class can have event-driven subscriptions and scheduled-subscriptions. Is that right?

The answer is yes. When you define the Subscription class in the ADF, make sure you specify an EventRule and a ScheduledRule node, complete with the appropriate matching rules for each scenario.

HTH....

Scheduled Backup for SQL Server Express

Not sure if this is the right forum, so please correct me if I'm in error.
I've written a Timeclock application in VB6 for our small chain of retail
stores and storing the data in a SQL Server Express database. All is
working well, but I want to perform a nighly backup of the database and
there appears to be no Maintenance Plan setup wizard in SQL Server Express.
Can anyone provide me with a query that I can launch at a scheduled time to
perform this or another alternative.
Thank you,
BarryThere is no SQL Server Agent in Express edition of SQL Server 2005, so no
scheduled tasks can be performed using standard tools. You may use OS
scheduler and SQLCMD to do this job or write your own backup app. Or, if
your Express instances can be accessed as linked servers from Standard or
Enterprise instance, you may do so by scheduling remote procedure calls on
this instance.
WBR, Evergray
--
Words mean nothing...
"BCS" <bswedeen@.tayloroil.com> wrote in message
news:NgmJf.21119$no3.16791@.tornado.southeast.rr.com...
> Not sure if this is the right forum, so please correct me if I'm in error.
> I've written a Timeclock application in VB6 for our small chain of retail
> stores and storing the data in a SQL Server Express database. All is
> working well, but I want to perform a nighly backup of the database and
> there appears to be no Maintenance Plan setup wizard in SQL Server
> Express.
> Can anyone provide me with a query that I can launch at a scheduled time
> to
> perform this or another alternative.
> Thank you,
> Barry
>|||The command to do a backup is BACKUP and you can see all the details in
BooksOnLine. But which options you use and the exact syntax depends on
exactly what you want to do and how. You might want to have a look at BOL
under BACKUP to get an idea what you want first to see what is available.
Andrew J. Kelly SQL MVP
"BCS" <bswedeen@.tayloroil.com> wrote in message
news:NgmJf.21119$no3.16791@.tornado.southeast.rr.com...
> Not sure if this is the right forum, so please correct me if I'm in error.
> I've written a Timeclock application in VB6 for our small chain of retail
> stores and storing the data in a SQL Server Express database. All is
> working well, but I want to perform a nighly backup of the database and
> there appears to be no Maintenance Plan setup wizard in SQL Server
> Express.
> Can anyone provide me with a query that I can launch at a scheduled time
> to
> perform this or another alternative.
> Thank you,
> Barry
>|||You could use a combination of SQL statements and Scheduled Tasks:
CREATE PROCEDURE nightlybackup
AS
BACKUP DATABASE [yourdb]
TO DISK = 'c:\backups\dbbackup.dat'
BACKUP LOG [yourdb]
TO DISK = 'c:\backups\dblogbackup.dat'
Now create a cmd script; run the below at a command prompt (all on one line,
of course):
echo osql -d [yourdb] -E -Q "EXEC
dbo.nightlybackup">>c:\backups\nightlybackup.cmd
Now add it as a scheduled task (all on one line again):
schtasks /create /tn nightlybackup /tr c:\backups\nightlybackup.cmd /sc
daily /st 00:05:00
This will do a daily backup at 5 minutes after midnight. Do all of this
logged on as a local administrator.
"BCS" wrote:

> Not sure if this is the right forum, so please correct me if I'm in error.
> I've written a Timeclock application in VB6 for our small chain of retail
> stores and storing the data in a SQL Server Express database. All is
> working well, but I want to perform a nighly backup of the database and
> there appears to be no Maintenance Plan setup wizard in SQL Server Express
.
> Can anyone provide me with a query that I can launch at a scheduled time t
o
> perform this or another alternative.
> Thank you,
> Barry
>
>|||Thanks to everyone for your input.
Barry
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:BD7B78CE-DB95-4E1D-89FC-41F689E94725@.microsoft.com...
> You could use a combination of SQL statements and Scheduled Tasks:
> CREATE PROCEDURE nightlybackup
> AS
> BACKUP DATABASE [yourdb]
> TO DISK = 'c:\backups\dbbackup.dat'
> BACKUP LOG [yourdb]
> TO DISK = 'c:\backups\dblogbackup.dat'
> Now create a cmd script; run the below at a command prompt (all on one
line,
> of course):
> echo osql -d [yourdb] -E -Q "EXEC
> dbo.nightlybackup">>c:\backups\nightlybackup.cmd
> Now add it as a scheduled task (all on one line again):
> schtasks /create /tn nightlybackup /tr c:\backups\nightlybackup.cmd /sc
> daily /st 00:05:00
> This will do a daily backup at 5 minutes after midnight. Do all of this
> logged on as a local administrator.
> --
> "BCS" wrote:
>
error.
retail
Express.
to

Schedule Job Keep on failing!

Hi,

Actually i saved a SSIS package into a SQL Server 2005, then scheduled to run a job from SQL Agent, but, the job keep on failing and no error message was returned in order for me to toubleshoot.

After the job failed, i restart the job again, and it actually continue to run, but unfortunately, it stopped again after some time.

And, before i sheduled the job, i actually tried to run the package itself in SQL Server Business Intelligent, it runs suceessfully.

In the package, i actually set the ProtectionLevel to DontSaveSensitive.

Another option that I used to run the pakage was using dtexex.exe:

dtexec.exe /FILE "E:\Package.dtsx" /MAXCONCURRENT " -1
"

But, the error message returned also not so useful.

So, can someone tell me how to troubleshoot this problem? What exactly the problem is that cause the job keep failing?

Thanks.

Hi, I dont know exactly about your problem but I hope this helps:

If you change the accounts for the SQL Server services you have to use the SQL Server Configuration Manager!

Otherwise (doing it by windows/services) the account wont be given the neccessary permissions in SQL Server.

Try to run SQL Server service and SQL Server Agent under the same account.

|||

New user to SQl 2005 wrote:

Hi,

Actually i saved a SSIS package into a SQL Server 2005, then scheduled to run a job from SQL Agent, but, the job keep on failing and no error message was returned in order for me to toubleshoot.

After the job failed, i restart the job again, and it actually continue to run, but unfortunately, it stopped again after some time.

And, before i sheduled the job, i actually tried to run the package itself in SQL Server Business Intelligent, it runs suceessfully.

In the package, i actually set the ProtectionLevel to DontSaveSensitive.

Another option that I used to run the pakage was using dtexex.exe:

dtexec.exe /FILE "E:\Package.dtsx" /MAXCONCURRENT " -1
"

But, the error message returned also not so useful.

So, can someone tell me how to troubleshoot this problem? What exactly the problem is that cause the job keep failing?

Thanks.

It might be helpfull if you post the error. Most of the time this kind of error are due to permissions: Protection level of the packge and/or permission of the account running SQL Agent service; so check that twice...

Rafael Salas

|||

Hi Suncream,

Actuallt the SQL Server service and SQL Server Agent service are using the same account (Local System). But the job anyhow will stop in the middle of executing.... But anyhow, when continue to start the job again, it still continue to run. And the error message returned:

Date 11/1/2006 5:49:02 PM
Log Job History (EXEC Package)

Step ID 2
Server SQLServer2005
Job Name EXEC Package
Step Name EXEC Package_Step2
Duration 02:24:19
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: SQLServer2005\SYSTEM. The package execution failed. The step failed.

Date 11/1/2006 5:48:35 PM
Log Job History (EXEC Package)

Step ID 0
Server SQLServer2005
Job Name EXEC Package
Step Name (Job outcome)
Duration 02:24:46
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
The job failed. The Job was invoked by User sa. The last step to run was step 2 (EXEC Package_Step2). The job was requested to start at step 1 (EXEC Package_Step1).

I still don't have any idea of solving this problem. Please help!!

Thanks.

|||Hi,
If using this protection level you would have to use configuration files to pass the credentials to your package at runtime when executed from the SQL Agent subsystem.
Try some research on this forum around Configuration, you will find plenty of answers.
Philippe|||

Hi Philippe,

Yes, actually I'm using a configuration file to pass all the credentials to my package at runtime when executing the package from SQL Agent. And the job is also running but just that not sure what has caused it to stop after running some time.

Do you have any idea?

Thanks.

|||When looking at your config file, did you set the format to be similar to the following?
Note that the password is not passed as the wizard suggest to put it in a separate xml tag (it would get stripped off the config).

<?xml

version="1.0" ?>

- <DTSConfiguration>

- <Configuration ConfiguredType="Property"

Path="\Package.Connections[datamart.SM.oledb].Properties[ConnectionString]" ValueType="String">

<ConfiguredValue>Data Source=datamart;User ID=ssis

agent;Password=VerySecret741;Initial Catalog=SM;Provider=SQLOLEDB.1;Persist Security

Info=True;</ConfiguredValue>

</Configuration>

</DTSConfiguration>

That would hold true for any provider you are connecting to.
Philippe|||Also try to set-up a text file SSIS log to see if you get any input.
Logs are not always generating errors logs as they should and this is a known problem but text logs are the best bet you have on hand.

On a personal note about logs, I would add to it that SQL Agent NEVER give a clue of what is wrong in an ssis package.
One can set-up an ssis log to be recorded in the MSDB database or in the text file.
MSDB is cool because you can easily generate a report but Text report seems to provide a little more insights of what is going wrong.
It is just not comfortable to work with text logs and they seems to be somewhat incomplete anyways, depending when the error occurs (pre-validation as an example) then text log is not helping you.

The problem here is that when you work with external datasources like Oracle, if Oracle is offline at the time, the log file does not tell you nothing while if you were working online you would get a tnsname error.

Philippe|||

Hi Philippe,

Below is the configuration file:

<?xml version="1.0"?>
<DTSConfiguration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[DB].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=Server;User ID=sa;Initial Catalog=DB;Provider=SQLNCLI.1;Auto Translate=False; Password=sa;</ConfiguredValue>
</Configuration>

<Configuration ConfiguredType="Property" Path="\Package.Connections[DB].Properties[InitialCatalog]" ValueType="String">
<ConfiguredValue>DB</ConfiguredValue>
</Configuration>

<Configuration ConfiguredType="Property" Path="\Package.Connections[DB].Properties[Password]" ValueType="String">
<ConfiguredValue>sa</ConfiguredValue>
</Configuration>

<Configuration ConfiguredType="Property" Path="\Package.Connections[DB].Properties[ServerName]" ValueType="String">
<ConfiguredValue>Server</ConfiguredValue>
</Configuration>

<Configuration ConfiguredType="Property" Path="\Package.Connections[DB].Properties[UserName]" ValueType="String">
<ConfiguredValue>sa</ConfiguredValue>
</Configuration>

<Configuration ConfiguredType="Property" Path="\Package.Properties[ProtectionLevel]" ValueType="Int32">
<ConfiguredValue>0</ConfiguredValue>
</Configuration>
</DTSConfiguration>

And I think it's the same as yours.

So, do you think that the configuration is correct?

Thanks

|||

Judging by the error you posted:

Date 11/1/2006 5:49:02 PM
Log Job History (EXEC Package)

Step ID 2
Server SQLServer2005
Job Name EXEC Package
Step Name EXEC Package_Step2
Duration 02:24:19
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: SQLServer2005\SYSTEM. The package execution failed. The step failed.

it seems you are running the package in SQL Server agent as an 'SQL Server Integretaion Services Package' step type. If you use 'Operative System(CmdExec)' as step type you will receive more details on the actual error. You can use DTExecUI to generate the command line you will need (http://msdn2.microsoft.com/en-US/library/ms141707.aspx); but in general should look like:

DTEXEC /SQL "\SSIS_Package_Name" /SERVER "Server\Instance" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF

This link have some example of what I am saying:

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

Rafael Salas

|||

Judging by the error you posted:

Date 11/1/2006 5:49:02 PM
Log Job History (EXEC Package)

Step ID 2
Server SQLServer2005
Job Name EXEC Package
Step Name EXEC Package_Step2
Duration 02:24:19
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: SQLServer2005\SYSTEM. The package execution failed. The step failed.

it seems you are running the package in SQL Server agent as an 'SQL Server Integretaion Services Package' step type. If you use 'Operative System(CmdExec)' as step type you will receive more details on the actual error. You can use DTExecUI to generate the command line you will need (http://msdn2.microsoft.com/en-US/library/ms141707.aspx); but in general should look like:

DTEXEC /SQL "\SSIS_Package_Name" /SERVER "Server\Instance" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF

This link have some example of what I am saying:

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

Rafael Salas

SCHEDULE DTS PACKAGE ERROR

I am having trouble scheduling a DTS package.
The error I receive is:
Executed as user: MAGIC-SQL-02\SYSTEM. DTSRun: Loading...
Error: -2147467259 (80004005); Provider Error: 17 (11) Error
string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access denied. Error source: Microsoft OLE DB Provider for SQL Server
Help file: Help context: 0. Process Exit Code 1. The step failed.
Any idea why
TIA
Grant
Check the SQL Server Agent startup account. It appears the system account
is being used. NOt sure which SQL Server is causing the SQL Server does not
exist or access denied error, but if it is a remote SQL Server then the
system account will not have access to it. Change teh SQL Server Agent
startup account to a domain account that has access to all SQL Servers
accessed by the package.
Rand
This posting is provided "as is" with no warranties and confers no rights.

SCHEDULE DTS PACKAGE ERROR

I am having trouble scheduling a DTS package.
The error I receive is:
Executed as user: MAGIC-SQL-02\SYSTEM. DTSRun: Loading...
Error: -2147467259 (80004005); Provider Error: 17 (11) Error
string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not
exist or
access denied. Error source: Microsoft OLE DB Provider for SQL Server
Help file: Help context: 0. Process Exit Code 1. The step failed.
Any idea why
TIA
GrantCheck the SQL Server Agent startup account. It appears the system account
is being used. NOt sure which SQL Server is causing the SQL Server does not
exist or access denied error, but if it is a remote SQL Server then the
system account will not have access to it. Change teh SQL Server Agent
startup account to a domain account that has access to all SQL Servers
accessed by the package.
Rand
This posting is provided "as is" with no warranties and confers no rights.