Maintenance Plan Tasks In SSIS(Sql 2005)
Oct 10, 2006
I am finding difficult to set Expression Property "SelectedDatabases" in Check Databases Integrity Task in SSIS.
I keep getting error:
TITLE: Expression Builder
------------------------------
Expression cannot be evaluated.
ADDITIONAL INFORMATION:
The data type of variable "User::varDataset" is not supported in an expression.
Reading the variable "User::varDataset" failed with error code 0xC00470D0.
(Microsoft.DataTransformationServices.Controls)
I need the flexibilty to be able to control the Task behaviour during runtime through Variables.
Any suggestions .... or even a different approach will be helpful..
cheers
aigbor
View 1 Replies
ADVERTISEMENT
Mar 31, 2008
in Management Studio, when i click on MODIFY a maintenance plan, the first thing I get is an alert "the diagram layout information was rebuilt" - "cannot set the default layout style of the diagram to 'microsoft.datawarehouse.layout.graphlayout', which i simply ignore, according the the limited info i was able to dig up on this alert.
my question is related to the color of the connectors between tasks. when i use the wizard to create a maintenance plan the connectors are blue, but when i add a task to an existing plan (by dragging an dropping from the available tasks into the diagram) and then choose "add precendence constraint" from the last task to the newly added task, the connector is green. WHY is that?
thanks
View 4 Replies
View Related
Dec 11, 2014
I have a scenario where i have to run update task on multiple servers in parallel and once all of them are completed (success or failure) another task is to be run on another server
1. in maintenance plan, if we add tasks which are not joined, will they run in paralled at the same time
2. if we link the last task to all the tasks with link type 'completed' will the last task complete after all tasks are completed or when any one of them is completed (i have big doubt here)
the business requirement behind this is to bring data from multiple servers into shadow copies locally and then process them together. its ok if some server data transfer fails, but its not ok to start processing centrally while data transfer is going on. further, we want to run data transfer from multiple servers in paralleled to save time.
View 0 Replies
View Related
Jul 12, 2007
Hye, I'm currently in charge of setting up maintenance plans on 100 plus servers. What I would like to do is to build an SSIS project that would build the maintenance plans on these servers. Is this possible? I would like this to be done like this so the maintenance plans are not being run based on a centralized package, but on local jobs. If anybody knows how to do this or has any ideas they would be greatly appreciated.
-Kyle
View 3 Replies
View Related
Apr 2, 2006
I've been searching everywhere for a solution to this problem and no answers exist anywhere. When I try to create a new maintenance plan I get the following error. I've been told it may be related to SSIS but nobody has a solution. How do I fix this issue so I can create a maintenance plan.
Exception has been thrown by the target of an invocation. (mscorlib)
ADDITIONAL INFORMATION:
An OLE DB error 0x80004005 (Client unable to establish connection) occurred while enumerating packages. A SQL statement was issued
and failed.
An OLE DB error 0x80004005 (Client unable to establish connection) occurred while enumerating packages. A SQL statement was issued
and failed.
View 1 Replies
View Related
Jan 23, 2006
Hi all,
I need to find out on runtime, a list of Maintenance plans and Jobs.
i dont know how to acquire the database name on which the plan/job are operating.
i also dont know from which table can i get the tasks ( backup,integrity,rebuild index etc.) that belongs to the plan.
its quite urgent,
10x,
Gil.
View 5 Replies
View Related
Jan 17, 2006
I'm trying to create a maintenance plan to backup a database, and when I go to select the database in the dropdown, I don't have any of the user databases any more. They were there several weeks ago when I first created the 'plan'.
Does anyone know how to get the user db's to show up in the dropdown?
View 7 Replies
View Related
Jul 9, 2007
Hi there
I feel like a complete nitwit. I've been using SQL 2000 for about 5 years now and regularly set up automatic backups using the wizard for many people. I'm in no way an advanced user and have enjoyed some of the more user-friendly features of SQL 2000 such as the backup wizard.
Well now we have SQL 2005 and I can't find the "wizard". I did a lot of googling and found very little on this. The frustration is mounting - let alone the fact that I had to edit the registry just to be able to perform a manual backup in the first place... :-(
Apparently their is a "maintenance plans" folder but the only place I can find this is under "Legacy" and you can't create jobs there.
Has anybody else had this issue? Is there a bug in SQL 2005? I reinstalled the full version with all options selected and still can't find this folder. Am I just being completely "blonde"?
View 4 Replies
View Related
Oct 4, 2007
Hello, everyone:
I have a strange trouble on SQL Server 2005 on XP/local and Win 2003/server. When I connect local database and create a backup plan named by "backup plan", I can see the name under "maintenance plan" and "Jobs" under SQL Server Agent. But if I connect to server database and do the samething, I cannot see the name under "maintenance plan". I can see the name under "Jobs" under SQL Server Agent. But cannot delete this job, and message:
Drop failed for job "Backup Plan".(Microsoft.SqlServer.Smo).
Did someone meet this bug and have suggestions?
Thanks
ZYT
View 4 Replies
View Related
Nov 16, 2007
I need to create sql 2005 maintenance plan for system and user databases which includes Database integrity check and Rebuild index tasks but these two things should happen at different times. Is it possible to schedule these two tasks at different times under single database maintenance plan, if so please let me know...
View 3 Replies
View Related
Aug 4, 2006
I successfully created a simple Backup Maintenance Plan under SSMS. But now that the plan is created I need to change the number of days the backups are kept. When I pull up the plan udner SSIS and lok at the tasks I can't locate that option. I couldn't find it under the sql agent either. Anyone know where it is? (Or do I need to recreate the plan from scratch?)
TIA,
barkingdog
View 24 Replies
View Related
Feb 16, 2007
In SQL 2005...
How do I specifty in the maintenance plan to only keep 3 days worth of backup.
Andre
View 4 Replies
View Related
Apr 16, 2008
I need to build a query that shows which databases on a sql 2005 server are NOT included in a maintenance plan.
So far I have been unable to find a matching column from msdb sys.databases with any column in the following msdb tables:
sysmaintplan_plans
sysmaintplan_subplans
sysmaintplan_log
sysmaintplan_logdetail
Has anyone else been able to solve this challenge?
Thanks!
.40
View 6 Replies
View Related
Nov 16, 2007
I want to create sql 2005 maintenance plan which includes Database integrity check and Rebuild index tasks but these two things should happen at different times. Is it possible to schedule these two tasks at different times under single database maintenance plan, if so please let me know...ASAP
View 4 Replies
View Related
Sep 19, 2007
I created a database maintenance plan on sql server 2005 (standard SP2, cluster environment). The plan created successfully on scheduled successfully. But when execute the plan, it fails. Here is the information in the log:
The last step to run was step 1 (TranLog backup).,00:00:01,0,0,,,,0
09/19/2007 12:06:38,Tranlog backups.TranLog backup,Error,1,LAIWWORKSITE1LA,Tranlog backups.TranLog backup,TranLog backup,,Executed as user: STROOCKSQLSRV. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:06:38 PM Warning: 2007-09-19 12:06:39.27 Code: 0x80012017 Source: Tranlog backups Description: The package path referenced an object that cannot be found: "PackageTranLog backup.Disable". This occurs when an attempt is made to resolve a package path to an object that cannot be found. End Warning DTExec: Could not set PackageTranLog backup.Disable value to false. Started: 12:06:38 PM Finished: 12:06:39 PM Elapsed: 0.688 seconds. The package execution failed. The step failed.,00:00:01,0,0,,,,0
Any ideas? Thanks.
View 1 Replies
View Related
Mar 25, 2007
Hi,We have a SQL-cluster with over 6300 databases, most of them prettysmall in size and usage.For this reason all these databases are on auto-close ( Otherwise SQLwould need many many gigabytes ).We've recently upgraded to SQL 2005 because of the Management Studio,because the EM is unusable on a SQL-Server with lots of databases.But whenever you choose 'all user databases' or 'all databases' in anyof the DB Maintenance task, MSSQL begins starting up every databasewhich takes about 30 minutes. Until then, you cannot use your Studioanymore.My question : is there a possibility to avoid this behavior, it shouldnot go and check all those db's, just add my task.PS : Detaching these databases is not an option ...Thanks in advance,Sven Peeters
View 5 Replies
View Related
Apr 21, 2008
Hello,
I use SQL Server Express 2005 SP2.(Microsoft SQL Server 2005 - 9.00.3042.00)
I want to make a maintenance plan, but I don't have a task called "maintenance plan"
I am user sa with "sysadmin"
How can I make a maintenance plan.
Thanks for your help
Thomas
View 3 Replies
View Related
Nov 8, 2006
I had created a maintenance plan and configure and scheduled my maintenace plan to run. I would like to save this package as a file into a source control. SO I use SSIS to export the package under stored packages->MSDB->Maintenace Plan. After that, I wanted to test my import process. So I deleted the packages under SSIS ->stored packages->MSDB->Maintenance plan and I use the import to add the package from my previously exported package stored in a file .dtxs extention.
So the problem is, after I imported my package. I lost the configured schedule and the job runs without doing anything. When I try to go in and make changes to the package, by adding a new schedule under SQL server -> management->maintenance plan. I receive a odd error message and it doesn't allow me to save the package.. The error message I got is...."GUID should contain 32 digits with 4 dashes (xxxxx-xxx-xx...)"
So my questions are: 1. why did the re-import loses the originally configure job run schedule. 2. why doesn't the re-import package works by backing up database as it was first setup. 3. why I cannot re-edit this package and saving the package error out?
Thank you for reading and for your helps! --Jon
View 3 Replies
View Related
Sep 22, 2006
Hello,
I have created a simple maintenance plan in SQL Studio. When I double-click on the task boxes (ie 'Rebuild Index', 'Backup') to just make changes or to look, I get the following message:
Cannot show editor for this task
Additional information:
Exception from HRESULT: 0xC0010014 (Microsoft.SqlServer.DTSRuntimeWrap)
I also cannot save any change that I make in the schedule of the plan.
I HAVE installed Integration Services, which at least enabled me to initially SAVE the plan. I just can't edit the tasks or change the schedule.
Any help is most appreciated!
Eunice Griffin
View 1 Replies
View Related
Jan 30, 2008
Hi,
I've small issue with SQL 2005 SP2. We have migrated database from another server with backup/restore procedure. Seems that db is properly set with cmp level 7.0. We are using standard maintenance plan for all SQL 2005 servers with option "backup all user databases", to have less work. Now it seems that dbs in SQL COMPATIBILITY LEVEL 7.0 cannot be backuped with Maintenance plan. They are not listed in the list of the databases and Maintenance plan is always reporting failure :
"Database '<DBNAME>' is not valid to be included in the maintenance plan."
Is there any way to fix this? Or can I safely switch db to CMP Level 8.0 at least?
Thanks for help
View 4 Replies
View Related
Nov 26, 2007
I need to programmatically create a SQL 2005 maintenance plan at hundreds of customer sites. Each plan will be a little different because of path names, database names, etc., so I can't use one canned plan without modifications. I know that SQL 2005 maintenance plans are XML packages and SQL jobs, so scripting isn't an option. I've seen mention of using the .Net API to create the plans, but I can't find any examples. SMO doesn't seem to have any objects for managing maintenance plans. Can someone point me to an example of using the API to perform this task?
Thanks,
Tom
View 1 Replies
View Related
May 5, 2008
Hi,
I have recently upgrade from MS SQL 2000 to MS SQL 2005. The maintenance plan disappeared. Although the jobs are still around and running. What issues might arise from this? Thanks..
View 1 Replies
View Related
Jul 5, 2007
Hi all,
We have a SQL Server 2005 64-bit, and recently I upgrade from build 3042 to 3054 and I try to do a maintenance plan for transaction logs(TL) backup, including cleanup for two days (have full backup every night).
Problem I have is that I want the TL files to dump in a different location(due to disk space), so I put in the UNC path in the "Create a backup file for every database - >Folder:\FileServerTLDBLogs"
NB: if using the local drives, it work
Check List
Security:
- The account that I used to create the plan is an sa account
- The location that I dump the TL files, I have full access to the folder
SQL Statement:
exec xp_cmdshell 'dir FileServerTLDBLogs' (it list all files)
Is this a bug for 64-bit? because I can do this on SQL Server 2005 32-bit and it's work perfectly
View 1 Replies
View Related
May 15, 2006
We are using SQL Server 2005 (SP1). I have created a maintenance plan that backs up up the datebase every night. The problem is that the transaction log is continuing to grow. I have been told that a full backup will automatically truncate and shrink the transaction log. However, this is not happening. How can I truncate and shrink the transaction log after a full backup as part of our maintenance plan. Thank you.
View 29 Replies
View Related
Apr 13, 2007
I installed SQL Server 2005 Enterprise, then SP1 and then SP2, Maintenance Plan worked. But if I installed SQL Server 2005 Enterprise and then SP2 directly (skipping SP1), the Check Database Integrity in Maintenance Plan was not working. The error message is as follows:
Executed as user: DomainSqlServiceAccount. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:56:21 AM Could not load package "Maintenance PlansTest Plan" because of error 0xC0014062. Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Login failed for user ''. The user is not associated with a trusted SQL Server connection.). The SQL statement that was issued has failed. Source: Started: 10:56:21 AM Finished: 10:56:21 AM Elapsed: 0.047 seconds. The package could not be loaded. The step failed.
Other tasks like Rebuild Index seem to be fine. SP2 is supposed to be inclusive. Does anyone have any ideas on why this is happening.
Thanks
E.G.
View 10 Replies
View Related
Nov 2, 2015
Unable to save a maintenance plan in sql server 2005, SP3. Error is class not registered.
View 2 Replies
View Related
Oct 1, 2007
I get the following message when I execute a mantenance plan to delete files older than 1 day.
Error # -1073548784
Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'',N'',N'2007-09-30T07:56:09'
" failed with the following error: "Error executing extended stored procedure: Invalid Parameter". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Any help would be appreciated!!
View 3 Replies
View Related
Aug 17, 2007
Hello,
I have a question that I hope someone can clear up for me. I have come across a number of different suggestions on DB maintenance, for example reindexing with the following script:
USE DatabaseName --Enter the name of the database you want to reindex
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
My question is, doesn't the maintenance plan have this functionality inherent in it when you create the maintenance jobs to reindex? Is there a benefit to scripting things out vs just using the maintenance plan wizard for this sort of thing and any of the items it covers? I came from an Oracle background where this was a no-brainer but I am a bit confused on the choices with SQL Server.
Thanks.
View 1 Replies
View Related
Apr 16, 2007
I want to get the start time of data load and end time after data load and store it in a table which has mapping_id , mapping_name,start_time,end_time.
i use ActiveXScript task to get the start time before data load and store the mapping_id in a global variable,then data flow transformation occurs.
i want to use a global variable to store the mapping id ,so that i can update the end time after data load with that variable.how to do this?
is there any other way, i can get the start and end time of data load (other than the logging information)?
View 3 Replies
View Related
Oct 9, 2007
Hi There
I have just done my first clean install of SQL 2005. I have previously upgraded several boxes from SQL 2000 without incident.
The clean install seemed to go fine but the Maintenance Tasks didn't install? I have looked on the web but have been unable to unearth any information. Has anyone encountered this before and if so I how do I add Maintenance Tasks to the Management folder?
View 6 Replies
View Related
Nov 2, 2007
I've had problems trying to get a new maintenance plan on a new server to work.
I'm running a plan that does an integrity check, reindex, shrink, backup, the a maint cleanup to get rid of backups older than 2 weeks.
I'm getting failures that say:
Error Number
-1073548784
Error Message:
Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'',N'BAK',N'2007-10-18T18:43:41',1
" failed with the following error: "Error executing extended stored procedure: Invalid Parameter". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Yes, this is a post SP2 server.
Current version, with the patches I've applied looking to fix this is: 9.0.3054
Additionally, if you go in to edit a maintenance cleanup task, you cannot click on OK, or the T-SQL. Changing values within it does not help.
Any clues?
charles@at@borner.us
View 1 Replies
View Related
Mar 5, 2001
I have deleted a database from SQL Enterprise Manager. Anyone know a way to clear that database from my maintenance plan? I do not wish to just uncheck the deleted database or create a new database plan.
Thanks!
View 1 Replies
View Related
Oct 23, 2001
Hello All
I have been given a SQL Server 2000 database to look after which has been set up with a Database maintenance plan. The plan is set to backup the complete database and the transaction log. The backups are written to the local disk correctly but the plan is also set to remove any backup files (both database .BAK and transaction log .TRN) that are over one week old. Complete database .BAK files are written daily and the .TRN are written every hour daily. The .BAK files are removed ok automatically but the .TRN files are not - they are just slowly filling the disk. There does not seen to be anything different between the way the main database and the transaction log is set up in the maintenance plan.
I would be very grateful for any ideas
View 1 Replies
View Related
