May 22, 2014 at 10:36 am
We have the next problem:
When we execute a package from a Job of SQL Server agent, it shows the success messege, but reviewing the results, the package didnt do nothing.
When we run it mannually by MSIS the package, it shows the success message and it works fine.
The workflow of the package is :
1) Shrink the databases
2) Backup the databases
3) MSDOS command to rename the files to .BAK extension
4) Execute command to compress the it
5) Move the compress file to another location
Manually run correct, but when is a a SQL AGent Job that execute the package it does only the first 2 steps.
we are ussing Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (coffee) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
We are using a user with administrator privilegies
Cheers
May 22, 2014 at 12:03 pm
How do you know the package didn't do anything? If the you aren't dynamically creating the backup file names then the existing backup files with either be overwritten or appended to by the new backups and any copying you do will overwrite an existing file.
Do you have any logging in y our SSIS package?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 22, 2014 at 12:03 pm
Are you using configuration files or any other type of congiurations? Could be pointing to a different direction.
May 22, 2014 at 12:14 pm
Make sure the behavior of each job step is "Go to the next step".
Be still, and know that I am God - Psalm 46:10
May 22, 2014 at 12:15 pm
Administrator privileges on the database or on the network?
May 22, 2014 at 4:26 pm
Jack Corbett (5/22/2014)
How do you know the package didn't do anything? If the you aren't dynamically creating the backup file names then the existing backup files with either be overwritten or appended to by the new backups and any copying you do will overwrite an existing file.Do you have any logging in y our SSIS package?
Thanks. The names are generated dynamical, the problem is that the package only run the shrink and the backup but the other steps dont. Any Idea? TIA
May 22, 2014 at 4:27 pm
Nevyn (5/22/2014)
Administrator privileges on the database or on the network?
If we run it manualy step by step it run fine.
But if we run it called by a scheduled Job it didnt run correct.
May 23, 2014 at 2:17 am
I don't know the reason for steps not being executed, but I do question why you are doing some of those steps anyway.
1. Shrink databases. Not a good idea as it causes fragmentation and the database is likely to grow again anyway. Better to size it appropriately from the start and use sensible autogrowth settings.
2. Backup using MSDOS command?? Why are you doing that? If you using the native SQL backups, the default extension is .bak and you can specify that you want to use compression too.
Regards
Lempster
May 23, 2014 at 8:19 am
Lempster (5/23/2014)
I don't know the reason for steps not being executed, but I do question why you are doing some of those steps anyway.1. Shrink databases. Not a good idea as it causes fragmentation and the database is likely to grow again anyway. Better to size it appropriately from the start and use sensible autogrowth settings.
2. Backup using MSDOS command?? Why are you doing that? If you using the native SQL backups, the default extension is .bak and you can specify that you want to use compression too.
Regards
Lempster
1.- the first task, am talking about the shrink is a requirement from my boss
2.- Really am using the "Back up Database task" node of SSIS...
The really steps are:
The workflow of the package is :
1) Shrink the databases (executing a sql file)
2) Backup the databases (Back up Database task of MSIS)
3) Rename the files to .BAK extension (by the Foreach loop container and fyle system task)
4) Execute command to compress the it (by a .bat)
5) Move the compress file to another location (by another Foreach loop)
but the package is doing these steps fine, the issue come when the package have to rename the '.bak' files, and this issue only occurs when the package is called from inside a Scheduled Job because if I execute the package from another place doesnt have issues
TIA
May 23, 2014 at 8:53 am
Sorry, I misread your post, but don't the backups get created with .bak extension anyway?
With regard to shrinking databases, I don't know why your boss has that as a requirement, but they are wrong. Do a search on this forum for 'shrink database' and show your boss some of the results! 😉
Does the account under which SQL Agent is running have access to the backup location?
Regards
Lempster
May 23, 2014 at 9:20 am
Lempster (5/23/2014)
Sorry, I misread your post, but don't the backups get created with .bak extension anyway?With regard to shrinking databases, I don't know why your boss has that as a requirement, but they are wrong. Do a search on this forum for 'shrink database' and show your boss some of the results! 😉
Does the account under which SQL Agent is running have access to the backup location?
Regards
Lempster
Dont worry!
Yes, the objetive for the rename to the .bak is to add the name of the day of week when the bak was made, I mean if the bak's name is Bd_backup_YYYYMMDD_HHSS.bak it change to bd_F.bak (F = Friday)...
And yes I did this with a clone of the administrator user, and did the same with the administrator user, and the user SA...
Note: had been transported the package to a local instance with the same features, and surprise! the package called inside a scheduled job ran fine! so am thinking the trouble resides in the configuration of the server machine but I cannot find it...
TIA
May 29, 2014 at 10:25 am
anybody?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply