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.

Schedule Stored Procedure: Changing one Field based on another

Hi all,

I want to do something that I can't get my head around. Here is the info..

I have a table, Tips. This is a table holding playing tips for a department of music web site. The tips are broken down into areas (brass, woodwind, etc...). Each area has its own .aspx page which has a user control that will display a "tip of the week." That tip will be taken from the table. Columns are:

ID, int, pk
AreaID, int
CategoryID, int
Active, bit
Selected, bit
Author, nvarchar
TipText, text

For what I want to do the only fields that are in play are the ID, AreaID and Selected. The table will have multiple rows for each AreaID, and at any one given time only one of the multiple rows with the same AreaID will be selected (Selected = 1). The others will be 0.

I'm trying to figure out how to write a stored procedure that, scheduled to run weekly, will select one random tip for each disctinct area id (setting selected = 1) that is not currently selected and then deselecting the previously selected items (setting selected = 0).

Doing this for a single record in the table would be easy. The tricky part for me is doing this for each of the distinct AreaIDs.

Ideas?

RichardWow. No replies.

Did I not ask the question right? I'm still trying to figure this out. If it's a stupid question I'd appreciate a heads up about where I should look to find this info.

Thanks!

Richard|||Have you had any luck? This is actually trickier than it sounds and I am not coming up with anything clever. I think you are going to have to resort to using a cursor -- the cursor will contain a unique set of AreaIDs, and you will loop through it.

What I was able to come up with was this (note I didn't put the cursor creation and looping code in there because I don't know the syntax off of the top of my head -- BOL is our friend):

-- temporarily make the currently selected items = 2 to keep them out of consideration
UPDATE
 tips
SET
 Selected = 2
WHERE
 Selected = 1
-- create a cursor consisting of the unique AreaIDs
-- then loop through cursor, updating Selected for each AreaID
UPDATE
 tips
SET
 Selected = 1
FROM
 tips
INNER JOIN
(SELECT TOP 1 ID FROM tips WHERE areaID = @.cursor_AreaID AND Selected <> 2 ORDER BY NEWID()) AS SubSelect ON Tips.ID = SubSelect.ID
WHERE
 Selected <> 2 AND
 AreaID = @.cursor_AreaID
-- when done looping, make the originally selected IDs unselected
UPDATE
 tips
SET
 Selected = 0
WHERE
 Selected = 2

I am hopeful this could be done without a cursor but it's not coming to me...

Terri|||Terri,

Clever! I didn't think about the setting the 0's to 2 part. I don't know what a cursor is, but I'll poke around and figure it out - give me a day or so. :) The rest make perfect sense, and should help me get it done. Thanks for your help!

Richard|||Since I now know you are still working on this, I still stand by my suggestion, but I can give you code to use instead of using a cursor (which is resource intensive). Same approach, but you would put all of your AreaIDs into a #temp table instead and loop though that:

DECLARE @.areaID int
-- temporarily make the currently selected items = 2 to keep them out of consideration
UPDATE
 tips
SET
 Selected = 2
WHERE
 Selected = 1
-- create a temp table consisting of the unique AreaIDs
CREATE TABLE #Temp (areaID int)
INSERT INTO #Temp SELECT DISTINCT areaID FROM tips
-- get first areaID to process
SELECT TOP 1 @.areaID = areaID FROM #temp ORDER BY areaID
-- loop
WHILE @.areaID IS NOT NULL
BEGIN
-- update Selected for each AreaID
UPDATE
 tips
SET
 Selected = 1
FROM
 tips
INNER JOIN
(SELECT TOP 1 ID FROM tips WHERE areaID = @.areaID AND Selected <> 2 ORDER BY NEWID()) AS SubSelect ON Tips.ID = SubSelect.ID
WHERE
 Selected <> 2 AND
 AreaID = @.AreaID
-- get next AreaID to process
DELETE FROM #temp WHERE areaID = @.areaID
SELECT @.areaID = NULL
SELECT TOP 1 @.areaID = areaID FROM #temp ORDER BY areaID
END
-- when done looping, make the originally selected IDs unselected
UPDATE
 tips
SET
 Selected = 0
WHERE
 Selected = 2
-- drop temporary table
DROP TABLE #TEMP

Still wishing this could be done in one statement...

Terri|||Terri,

Worked perfectly! All I had to do is change the column type from bit to int (couldn't update all the 1's to 2 in a bit field). Thank you!!

RH|||That's good news; thanks for updating us! (And yet another reason why I don't like bit fields ;-)) You should be able to use a tinyint here and save some storage space.

Terro