Does Anyone Use SSIS For Database Schema Maintenance?
Mar 1, 2007
We currently use SSIS to build DTS packages in which we store changes
to our database schema, as well as scripts that need to be run upon
each release. This works well for small sets of changes that never
need to be updated or for architectures with only one database.
We store each of the changes included in the package in separate
files, which are tracked using version control. It is growing time
consuming to maintain parity between those files and what is in the
SSIS.
Furthermore, we have been unable to discover an easy way to load a
file's contents into a package SQL Task without opening the file and
copy-pasting the contents into a new SQL task.
ANY information at all would be extremely appreciated!
I want to customize database maintenance to run only on selected databases based on their status in sys.databases, without having to statically select them in the GUI.
I'm trying to write an SSIS package that will do the following:
1. Query for a list of databases that are not read-only or recovering, etc. 2. Perform database maintenance tasks (e.g., rebuild indexes) on those databases.
I'm doing this because the database maintenance tasks do not provide a way to select databases based on criteria or a SQL query filter.
My package includes an Execute SQL task to get the list of databases, a Foreach loop container to loop through the databases, and then...this is where I get stuck. I can't use the existing maintenance tasks (Rebuild Index, Update Statistics, etc.) because I don't know how to provide an input variable for the list of databases. I'd rather not write dynamic SQL to generate commands for each database object.
I would like to use SSIS tool to move the data from one database schema to another database schema.
For example:
Source table has
1. UserName (varchar 20) (no null)
2. Email (varchar 50) (can be null)
Destination table has
1. UserID (uniqueidentifier - GUID)
2. UserName (varchar 50) (no null)
3. EmailAddress (nvarchar 50) (can be null)
4. DateTime
Questions:
1. What controls do I use in my Data Flow to make data move between databases with different data types and include new value in UserID as a new GUID and DateTime as a date (GETDATE)?
OLE DB Source, OLE DB Destination, Data Converson and .....
How do I insert Guid and Date at the same time?
2. I have many tables to do data moving. Any sugestions? How do I architect my project? If I create many data flows for each table - it will look complicated.
Locally I develop in SQL server 2005 enterprise. Recently I recreated my db on the server of my hosting company (in sql server 2005 express).I basically recreated the tables and copied the data in it.I now receive the following error when I hit the DB:The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version.I heard something about running aspnet_regsql.exe, but I dont have that access to the DB. Also I dont know if this command does anything more than creating the membership tables and filling it with some default data...Any other solutions/thought on what this can be?Thanks!
Hello everybody!I'm using ASP.NET 3.5, MSSQL 2005I bought virtual web hosting .On new user registrations i have an error =(The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version. On my virtual machine it work fine but on web hosting i have an error =(What can you propose to me?
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.
During testing a package repetatively that deletes/inserts into several tables, over the course of several days, my package, which took 45 minutes to load 1700 XML files, began to take over 6 hours. Turns out it was an I/O bottleneck, and the Avg Disk Queue Length was around 200 and I was incurring many PAGEIOLATCH_EX. My devl machine uses a single local disk, no raid, so I had no options there, but I ran the maintenance wizard to recreate indexes/statistics and defraged the hard drive, and regained my original 45 minutes time. I guess I'll have to put a maintenance plan together to do this nightly.
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.
I have a MsAccess db containing a table called Employees which i am transforming to a staging table in Sql server 2005. Everything is working fine. I am using Foreach File enumerator and uploading the files one by one.However I now plan to validate the schema of MsAccess before uploading it. For eg: My employee table in msaccess is as follows :
Code Snippet Employees empId int, empName varchar(60), empAge int
Since the files come from different vendor, while looping, i want to perform a check if the empid or empAge are not of type long or string etc. If they are of type smallint,i have no problem.
However if they are larger datatypes than the the ones kept in Sql server, then the file needs to be logged in the db with the reason and moved to the error folder. In short, if the datatypes in access tables are smaller than those in Sqlserver, allow it, otherwise reject it. THe schema of Sqlserver table is same as of that of Employees in msaccess.
I want to compare the schema of the incoming access tbl fields with my desired schema and all mdb's having data types that are higher or incompatible with the desired schema should be moved to the error.
I have a database that has few tables whose table Schema changes oftenly based on a flat file specification and I wanted to automate the process where I drop the table and recreate it with the new schema using an SSIS package. Any Ideas on how I can achieve this?
Environment: SQL Server 2005 Enterprise Edition x64, 3 server cluster. Two active servers with seperate instances and one passive server. SQL Server was installed on the two active servers.
Problem: When I fail over either of my instances to the passive server in the cluster my maintenance jobs fail to run and there are error messages in the application event viewer "SSIS Subsystem failed to load". I am guessing that all of the needed components are not installed on the passive server? Is this a close guess? If so, exactly what components are missing and do you have to have another license to install them?
I am running SQL 2005 Enterprise with SP2. In the past I have been able to successfully create SSIS packages using the BIDS and also Maintenance Plans from within Management Studio.
Today I tried to edit a maintenance plan and got the following error message:-
TITLE: Microsoft SQL Server Management Studio ------------------------------ Retrieving the COM class factory for component with CLSID {0BE35203-8F91-11CE-9DE3-00AA004BB851} failed due to the following error: 80040154. (Microsoft.DataWarehouse) ------------------------------ BUTTONS: OK ------------------------------
This error also occurs if I try to create a new Maintenance Plan.
I then tried opening an existing SSIS package in BIDS and got the following error:-
------------------------------------------------------------------------------------------ Microsoft Visual Studio is unable to load this document Index (zero based) must be greater than or equal to zero and less than the size of the argument list. ------------------------------------------------------------------------------------------
These errors only occur on my PC so I know that the problem is with my PC. I have totally uninstalled SQL 2005 components from my PC and reinstalled them with no luck.
To my knowledge, I haven't installed any other programs since creating these packages, although there's a possiblilty my PC may have had some patches applied through SMS.
I restored a backup from SQL 2000 onto a new SQL 2005 server. The database restored and the matching user who was the owner of that database in SQL 2000 was created as a user in SQL 2005 as well as being designated as the "schema" for that database. I was having trouble logging in with the user that was created after the restore, so I created a new user and assinged the new user as DBO of the new database, set up the new users default database and schema, and everything was working fine, my database driven application worked perfectly under that user.
Then I installed SSIS, as it was recomended as a solution to another problem. After installing SSIS I can not run queries without specifying a schema name. For example this query will not work, it returns table not found:
SELECT * FROM <table_name>
However if I specify the schema name like this, the results return:
SELECT * FROM <schema>.<table_name>
This screws up my database application as it does not include the schema name in its transactions.
I do not understand why installing SSIS would have changed this.
I recently added a new user to my database. Now I want to delete that user, but I keep getting the error above. What do I need to do to delete my recently added user?
An error has occurred during report processing. (rsProcessingAborted) Query execution failed for data set 'TestID'. (rsErrorExecutingCommand) For more information about this error navigate to the report server on the local server machine, or enable remote errors
The log file reads:
---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'TestID'. ---> System.Data.SqlClient.SqlException: SELECT permission denied on object 'TableID', database 'Database', schema 'dbo'.
***Background***
General Users got an error message when trying to access any reports we have created. All admin have no problems with the reports. Users (Domain Users) are given rights (Browser) to the reports and the Data Sources (Browser) and yet cannot view the reports.
An error has occurred during report processing. (rsProcessingAborted) Cannot create a connection to data source 'DS2'. (rsErrorOpeningConnection) For more information about this error navigate to the report server on the local server machine, or enable remote errors
I'll add this from the report logs...
w3wp!processing!1!3/20/2007-11:43:25:: e ERROR: Data source €˜DS2€™: An error has occurred. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source €˜DS2€™. ---> System.Data.SqlClient.SqlException: Cannot open database €œDatabase€? requested by the login. The login failed. Login failed for user €˜DOMAINUsername€™.
The user has rights via a local group to the report and data source (Browser rights) and the local group has been added as a SQL login.
I gave rights to the databases themselves instead of just to SQL and the error changed (Ah-ha...progress, but why!?!?)
I need to export a database, x, of a server, X, to another database, y, of a server, Y and I need export the database schema only, not include the data. Does anyone know how to do that? Many thanks for replying.
Trying to get my hands around all the new security features of SQL Server 2005. In Management Studio did something I don't know how to undo. I added a database role ReadOnlyRole and clicked the box next to db_datareader in the owned schemas box. Then I tried to remove the ReadOnlyRole and could not. How do I undo what I did? Is it possible?
The below is the TSQL that generates the my issue.
Use [master] go create database [test] go
USE [test] GO
CREATE ROLE [ReadOnlyRole] GO
USE [test] GO
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [ReadOnlyRole] GO
I am looking for opinions of setting up a database maintenance plan. I want to know if it is safe to trust the wizard and let it set up all of the jobs, or if it is better to write your own procedures to handle backups and maintenance as in 6.5. All sugestions and opinions are welcome. Thanks.
For SQL server 7.0, is it necessary to schedule database maintenance plan on a regulare basis? I know it is necessary for SQL server 6.5. Thanks. Su Ge
SQLMaint is run once a week for the following a database on SQL 6.5. The following is the information for the database when I see it through the Enterprise Manager:
Data Size 650 MB Data Space Available 0.00 Log Size 360 Log Space Available 359.99
The following is the syntax built by the DATABASE Maintenance Wizard:
SQLMAINT.EXE -D CATS -CkDB -CkAl -CkTxtAl -CkCat -UpdSts -RebldIdx 100 -Rpt E:MSSQLLOGCATS_maint.rpt It runs once a week and takes about 40 mins and runs successfully. Last it run was 4/11/99 at 2:00 AM
The result set I get from sp_spaceused is as follows:
database_name database_size unallocated space CATS 1010.00 MB 273.96 MB
What I don’t understand is how come the data space available shows 0 in Enterprise Manager? Shouldn't SQLMAINT, which is run once a week, allow for correct information to be reported?
Running SQL Server 2000 Enterprise Edition SP3. The database is alsoused by Microsoft Project Server 2002 and also has OLAP views, so thedatabase is being used to view/run cubes in the Analysis Manager.What is the best way of shrinking the database size and its log file too?Is there an automatic way to do this with a maintenance plan or i haveto manually run a SQL statement periodically?What are the best practices?I have a database that the size of it is 260megs and now the log file isover 800megs...Everytime i run the following:BACKUP LOG DBName WITH TRUNCATE ONLYDBCC SHRINKDATABASE (DBName, 10, TRUNCATEONLY)When running it, i get the following 2 records returned:DbId FieldId CurrentSize MinimumSize UsedPages EstimatedPages7 1 32304 128 28152281527 2 160 128 160128I don't know what the above 2 records mean. I am also concerned if i shouldbe running that statement or not.Also concerned as to why the Windows Server 2003 (Enterprise Edition)always creates a CRITICAL error in the Application event viewer with EventIDnumber 17055, source being MSSQLSERVER and the description of the event:18278:Database log truncated: Database: DBName.If it's a critical error message, then what i am doing is bad? Am i damagingmy database?Within like 7-10 days of running the above statement, the log file becomescloseto 1 GB again! How can i find out what causes the log file to grow that big?Is it beingcaused by running some OLAP views or what? The db has about 20 usersconnected to itusing an ASP web aplication as the front-end, and MSP Professional 2002 alsoto connectto the Project Server 2002 (which uses the same database as the aboveDBName).I would appreciate any help.Thank you very much
I have been looking through the data files directory for my databases and noticed that the log (ldf) files of some of the databases are from 2GB to 4.5GB where as the data files are under 500MB. If I shrink the log files what affect does this have on the database? Should I be setting up regular shrink jobs on the log files?
We are using a database maintenance plan to backup and reindex our db's. Up until the end of last month this was working perfectly - however now it has stopped deleting the old backups (even though we have checked 'Delete files over 1 day old').
Does anyone have any ideas as to why they are now being deleted - and how we can remove them automatically - has something been corruped? Would it be a case of creating a new maintenance plan?
SQL Server 7.0 with SP 1 Database maintenance job is failing with error about being unable to delete old backup file. I can manually delete the file with no problem. This occurs randomly, maybe once a week. Any ideas on what's wrong, and how to fix it?
I want to know database maintenance issue when there are many databases in a machine. The number of database will be 50~80. We'll use SQL 7.0. In Sql 7.0, I heard that there is not so much maintenance issue.
After looking through the archives at database maintenance steps used by others, I have decided on the following steps for each of my SQL Server 7.0 databases. I have set up a maintenance plan where they would execute in the following order for each database, if the preceding step is successful. If the preceding step fails, the job quits.
dbcc checkcatalog ('master') dbcc checkdb ('master') backup database master to master_dump with init, name = 'ASP Master dump' dbcc dbreindex ('master', '', 70) dbcc shrinkdb ('master', 10)
Now my questions...
1. The checkcatalog, checkdb, backup database, and shrinkdb commands appear in blue in SQL Server 7.0 to seemingly indicate they are commands. However, the dbreindex command is black. Does this mean that it is not recognized as a valid command? If so, why not? It is shown as a valid command in the Microsoft Knowledge Base.
2. The value of 70 in the dbreindex command was the value shown in sample code in the Microsoft Knowledge Base. However, the Knowledge Base does not give any guidance on whether this is a good value or not. How should I make a decision on what value to use? The value of 10% for the shrinkdb command is also just a sample value from Microsoft. How does one know what value to choose?
If anyone thinks this is a poor selection of maintenance steps, please let me know that also.
Hi, I'm new to SQL 7 (and fairly new to SQL Server), can anyone help with these basic questions on database maintenance plans generated by the wizard: (1) Scheduling - can SQLServer handle say a REORG running at the same time as a backup against the same database? This should never happen, but what will happen if say a backup is due to start before a REORG has finished? To try & simulate this 'problem' I've run REORGs and backups at the same time & have yet to encounter errors I presume SQL locking handles this OK. (2) Database integrity checks - (a) any comments on the wisdom of checking 'repair any minor problems'? Anyone had any problems with this? (b) While integrity checks are running do they take enforce a consistent view of the data? (I think this is probably the case as my reading of books online indicates that DBCC takes shared locks for the duration) (3) Backups - does the 'VERIFY INTEGRITY' option have any impact on the live database? (My reading of RESTORE VERIFYONLY indicates it doesn't) Thanks....
I have several Maintenance jobs that keep failing. They are , Integrity Checks, Index rebuild, and Update statistics. I keep getting a similar error on all three. I have pasted it below. None of my other SQL 2000 servers have any problems. I have tried changing the database options in question. The error I keep getting is as follows:
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'DAPHNE' as 'CDCNorm' (trusted) Starting maintenance plan 'FSPRD84A Integrity Checks' on 4/21/2003 10:20:59 AM [1] Database FSPRD84A: Check Data and Index Linkage... [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. ** Execution Time: 0 hrs, 0 mins, 8 secs **
End of maintenance plan 'FSPRD84A Integrity Checks' on 4/21/2003 10:21:07 AM SQLMAINT.EXE Process Exit Code: 1 (Failed)
Hi, I'm researching on how to maintain RS SQL server database or what is the best way to maintain ReportServer and ReportServerTempDB? Should I go for the automatic shrink facility of SQL Server? Any suggestion? Thanks.
I have a Database Maintenance Plan that back up some databases and delete old backup files that more than two day. Can any one have idea which action is first, backup or delete old files?
