July 19, 2012 at 12:32 pm
Hi All,
I am not a DBA, nor a system administrator, and I do not play one on television.
However, I do have an SSIS package I'm trying to run that fails with:
To run a SSIS package outside of Business Intelligence Development Studio you must install Standard Edition of Integration Services or higher.
I'm told that Standard Edition is installed (how do I check? We have 2008 R2 installed). Googling this error seems to indicate a permissions problem with the Sql Agent User.
Here's the job in a nutshell:
For each user database:
1. DBCC Checkdb
2. Backup the database
3. Zip the backup file
4. Delete the backup file
5. Delete extraneous zipped backups (we're keeping 3 days of backups).
The error occurs at step 3.
What I have attempted to date:
1. Set permissions on the backup directories for the Sql Agent user.
2. Set the Sql Agent user to be an account with system administrative privileges.
3. Pulled hair out.
4. Tried to set the sql agent user as various other accounts.
Is there a step-by-step for getting this set up correctly? Going through BOL leaves me with too many options to consider, and I'm not sure I know what they all mean.
Thanks in advance,
Tom
July 19, 2012 at 1:53 pm
Right click My Computer wizard and choose the manage;
Go to the Services folder and browse whether or not you have SQL Server Integration Services there.
May I answer your question.
July 20, 2012 at 6:15 am
There is a Sql Server Agent service, but not SQL Server Integration Services. How many levels of indirection are there in this product?
July 20, 2012 at 7:22 am
You have SQL2008R2 so you get database backup compression with the product
run commands
sp_configure 'backup compression',1
reconfigure
to enable it.
the just set up a maintenance plan to do your backups, it can do all the steps you list and you won't need SSIS, keep it simple.
---------------------------------------------------------------------
July 20, 2012 at 7:35 am
I thought you needed Enterprise edition to get backup compression. We have Standard.
July 20, 2012 at 7:46 am
with R2 it works in standard as well
---------------------------------------------------------------------
July 20, 2012 at 8:16 am
That was easy. Grrrr. I was doing all that work not knowing backup compression was available in Standard edition.
Thanks.
July 20, 2012 at 9:37 am
🙂
---------------------------------------------------------------------
July 21, 2012 at 8:22 am
george sibbald (7/20/2012)
You have SQL2008R2 so you get database backup compression with the productrun commands
sp_configure 'backup compression',1
reconfigure
to enable it.
the just set up a maintenance plan to do your backups, it can do all the steps you list and you won't need SSIS, keep it simple.
For clarity you do not need to enable compression per se. sp_configure sets the instance's default behavior when WITH COMPRESSION or WITH NO_COMPRESSION is not specified. Using the WITH COMPRESSION option on the BACKUP statement will cause a backup to be compressed even when the server configuration is 0.
backup compression default Option
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply