Customize Database Maintenance Using SSIS (Select Databases Based On Status)?

Mar 7, 2008

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.

Does anyone know how to do this?

Thanks much.

David

View 9 Replies


ADVERTISEMENT

Transact SQL :: How To Customize Where Statement Based On UDT

May 20, 2015

Display customized data based on customized where statement from UDT.

The UDT is a parameter inside of a stored procedure.

Problem:

A parameter from a stored procedure is
@communication communications readonly

This parameter is a User-Defined Table Types (UDT) It contains criteria based on end-user's selection from a filtration functionality from a webpage.

Four example of filtration critera based on four end-users' selection that is located inside of a table below.

Each UDT table contains different criteria:

Number Criteria
------ --------
1 Phone
3 Email

Number Criteria
------ --------
1 Phone
2 Cellphone
3 email

Number Criteria
------ --------
4 None

Number Criteria
------ --------
2 Cellphone
1 Phone

Is it somehow possible to use the criteria's value as a column name in the where statement? I want to filtrate the data of the table datatable based on id, name and the UDT's criteria.

I was enable to apply the criteria inside of a variable by looping the UDT's table but the next thing is to paste it in the where statement after "id=1 and name" below

select *
from datatable
where id = 1 and name = 'Cost'

How should I do it?

[URL] .....

create table datatable (id int,
name varchar(100),
email varchar(10),
phone varchar(10),
cellphone varchar(10),
none varchar(10)
);

[Code] .....

View 4 Replies View Related

Does Anyone Use SSIS For Database Schema Maintenance?

Mar 1, 2007

We currently use SSIS to build DTS packages in which we store changesto our database schema, as well as scripts that need to be run uponeach release. This works well for small sets of changes that neverneed to be updated or for architectures with only one database.We store each of the changes included in the package in separatefiles, which are tracked using version control. It is growing timeconsuming to maintain parity between those files and what is in theSSIS.Furthermore, we have been unable to discover an easy way to load afile's contents into a package SQL Task without opening the file andcopy-pasting the contents into a new SQL task.ANY information at all would be extremely appreciated!

View 1 Replies View Related

Status On Set Based Operations?

Aug 26, 2004

Every time I mention cursors, I hear a lot of people pointing out how you shouldn't use cursors and how set based operations are much faster.

However, procedural code offers the big advantage that I can embed status/progress type commands into a cursor loop and get accurate data on progress and ETA.

If I execute a single huge UPDATE/INSERT, I am at the whim and mercy of SQL Server. Seemingly simple statements often run for 50 hours+ and I have no clue how far along it is or anything remotely close to an ETA. Often, that is absolutely inacceptable to have mission criticial hardware to be churning through a process for DAYS without the slightest idea if it is actually making progress or not.

Is there any way to get progress/status on such an UPDATE/INSERT command?

View 11 Replies View Related

Breaking Down Status Based On Hierarchy?

Jan 23, 2012

I want to show a third-level overview of how many to-do items are in various statuses (pending declined and completed) for the third level individuals.

In this example there's 2 regional leaders (level3), each with local leaders (level2) and individual team members (level1)

Status "1" is a new or pending to-do item
Status "2" is a declined item
Status "3" is a completed item

Here's what I want the output to look like (I may need to add columns for the final report but it should look as follows):

LEVEL3 | Status 1 | Status 1 | Status 2 | Status 3 |
LEADER | TOTAL | 2012 | TOTAL | TOTAL |
-------|----------|----------|----------|----------|
paul | 3 | 1 | 1 | 0 |
roger | 1 | 1 | 1 | 0 |

Here's some sample data similar to my live DB:

CREATE TABLE #items (id int, datestamp datetime, userid int, status int)
INSERT INTO #items (id,datestamp,userid,status) VALUES (1,'12/26/2011',1,1)
INSERT INTO #items (id,datestamp,userid,status) VALUES (2,'12/26/2011',2,1)
INSERT INTO #items (id,datestamp,userid,status) VALUES (3,'1/2/2012',4,1)
INSERT INTO #items (id,datestamp,userid,status) VALUES (4,'1/3/2012',1,2)

[Code] ....

I have a view called hierarchy that shows me the hierarchy for each level 1 user. Here's the source for the view:

SELECT
a.id 'level1',
b.id 'level2',
c.id 'level3'
FROM #users a
INNER JOIN #users b ON a.reportsto = b.id
INNER JOIN #users c ON b.reportsto = c.id

I've run into table/view limits when using a straight query and timeouts when trying to create a cursor that looks through all level3 users and populates a temporary table with an INSERT and multiple UPDATE statements to get the individual counts for each column for each user.

(Just a note on the actual database in case it's relevant: It has about 1000 LEVEL1 users, 50 Level2, and 15 Level 3 users. It will generate about 1400 to-do items per week.)

View 4 Replies View Related

Taking Data From A Different Databases/Data Warehouses Using A Sequel Server 2005 Based Database.

Apr 1, 2008



I'm in the middle of developing a Database for a hospital that measures its audits, inhouse operations, and finance. What we currently have and do everyday is collect data from a large database that is real time with patient data, progess, infomation, etc and dump it into a data warehouse that runs on TSI/Eclipsys. We run reports using a number of programs and dump it into Excel sheets that have charts, reports, etc. This Database for which I'm developing won't come soley from the TSI/Eclypsys source, but this is the only source thats updated regularly. I don't want to have in sync with TSI/Eclipysys in fear that every day when it updates data may be lost, not read, or worse won't be up date if there is a problem. My question is is it possible to run a query from Sequel Server 2005 that will take that data upon request using the reporing features on Sequel Server 2005. i.e. What if I need to run a report on measure B in department 12 from Jan 1-Feb 1, instead of being in sync, can I just write queries to take that information rather than double the data and take up twice the space and trouble. FYI, these datatypes rarely change in the TSI/Eclipsys data warehouse. This sure was long question and didn't intended it to be . Thanks for listening and hope to hear back.


Stephen

View 1 Replies View Related

Customize Database Admin Activities By Creating New User Group?

Aug 17, 2012

The requirement is to customize database admin activities by creating new user group.

Need to create a group of user / dbauser1 which will have restriction in seeing the data but they should be able to alter database - add / remove the data file , increase or decrease the data file space when required.

This requirement came we wanted to create a new dba group they should not be able to any user data / any table but increase / decrease / add / modify space etc.

View 1 Replies View Related

Not All Databases Showing Up In Maintenance Plan

Jun 11, 2007

I'm running SQL Server 2005 version 9.0.3042. I'm trying to create a maintenace plan using the wizard, but when I get to where you can select the databases, it shows all the system databases, but only 2 of the 14 user databases.

If I connect to a different 2005 server that's only running SP1 (2047), I can see all of it's databases. Is this is SP2 issue?

Thanks in advance!

Kevin

View 2 Replies View Related

Sql 2005 Databases That Are Not In A Maintenance Plan

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

How To Calculatesave A Parent Status Based On Related Child Records

Sep 24, 2007

Thanks for your time,
How to calculate & save a Parent status [qcStatus varchar(30)] and Alert [alertFlag bit] in dbo.a1_qcParent
based on comparison of its Child records in dbo.a3_qcItems2Fix columns [itemComplete bit] and [alertFlag bit]
Where a1_qcParent[a1_id] = a3_qcItems2Fix[a1_ID]

- Parent CLOSED: if all children [itemComplete] are True
- Parent OPEN: if any child [itemComplete] is False

- Parent ALERT: True if any child row [alertFlag bit] is True

Using sql_Datasource in webpage, but more comfortable in sql... After-Trigger?
Can Parent columns have calculated formula referencing the child table? Please help.

View 1 Replies View Related

Transact SQL :: Fetching Records Based On Maximum Date And Status

Jul 2, 2015

I have a scenario to fetch records for each ID on 2 conditions. There are 2 types of Product type for each ID. PFE and PRI. I need only latest active PFE and at the same time all the latest PRI should be closed. (meaning, only PFE should be in Active status currently)

1.) Latest Product type PFE should be A (active) status for the particular ID
2.) At the same time ALL the latest PRI should be C(closed) status for the same ID

I have give example with 3 scenarios and desired output

1.) For ID 101, Latest PFE is active and all latest PRI is closed ----> Should come in result
2.) For ID 102, Latest PFE is Closed and all latest PRI is closed ---->Should NOT come in result

View 5 Replies View Related

SQL 2005 E-mail Client Failing To Send Email Based On Job Status....

Mar 4, 2008

Kind of a newby sql question, but here goes:I have a sql 2005 database that I have a job that runs Sunday morning at 12:30 am.  I set it up using SQL Svr Mgt Studio 2005. Under  the Management directory I set up Database Mail to work with my local SMTP server. I can send a test email just fine.I then set myself up as an operator with my email address. (Under operators directory) I then went back to the properties of the job I set up, and under 'notification', chose e-mail operator (me) when Job Succeeds. The job runs, itt suceeds, but NO email!It flat out won't work. there are NO entries in teh( email) log for errors either.  Anyone? TIA  Dan  OR is it better to script these jobs using xml? I don't have time to learn a new thing right now, just need it to work!  

View 1 Replies View Related

SQL 2012 :: Report GUEST User Access Status In All Databases On Instance Level

Apr 17, 2015

Below query tells us if guest user is enabled or disabled in a particular database

SELECT dp.name, CASE perms.class WHEN 0 THEN 'Yes' ELSE 'No' END AS 'Enabled'
FROM sys.database_principals dp
LEFT JOIN (SELECT grantee_principal_id, class FROM sys.database_permissions
WHERE class = 0 AND type = 'CO' AND state = 'G') AS perms
ON dp.principal_id = perms.grantee_principal_id
WHERE dp.name = 'guest';

Do we have a query which can also add the database name to above query output? The output must have columns with data against Name,Enabled,Database name

View 1 Replies View Related

Trouble Backing-up All Databases To Network Folder Using A Maintenance Plan

Oct 10, 2006

I have set up a Database Maintenance Plan that does a nightly backup of all of my databases (about 12 of them) to a network folder. The plan works for about 95% of the job, but most nights there will be at least 1 database which will fail with the following error...
BackupDiskFile::RequestDurableMedia: failure on backup device '\myfileserverBackupSQLDatabaseDatabaseNameD atabaseName_db_200610081749.BAK'. Operating system error 64(The specified network name is no longer available.).
I know that this is not a permission or storage problem, because it works for most of the job. And a database that fails one night, may work fine the next night only to have a DIFFERENT database fail that night, and sometimes all databases work 100%.

Is there a way to fix this problem? And if not, is there a way to be notified of which specific database in the maintenance plan is failing. The message on the job itsself is very non-descriptive and I have to manually search the logs to find out which databases were successful & which where not. It is very time consuming. Any help with this would be greatly appreciated.

View 5 Replies View Related

Integration Services :: Fetch Data From Netezza Database Based On Values Present In Excel Using SSIS

Aug 7, 2015

I have an excel sheet containing one column (ID_NO) with 400K rows. I have a database from where I have to fetch some other columns from a Netezza database. Initially I tried hardcoding all the 400K rows in the query that I wrote using filter WHERE ID IN ('1212','2334'). But after pasting all the 400K rows the query is running indefinitely.

I have imported all the ID in a SQL table (MY_LIST table). I used a DFT, and selected ODBC source, and selected my netezza server. Then in the 'Data access mode' I selected the SQL command from the dropdown.I pasted the same query that I wrote in Netezza. Is there any way to pull only for those records that I have pulled in my SQL table (MY_LIST) ?

View 4 Replies View Related

T-SQL (SS2K8) :: How To Select Records Only When Their Status Has Been Changed

Jun 9, 2015

I have a product table and my task is to select only the products that had been paid for, but later their status has been changed.

I need to report the original paid date, the most recent status, and most recent updated date. Here is the sample table:

CREATE TABLE #Products (primKey int, productId int, productName varchar(100), productStatus varchar(50), logDate datetime )

Insert into #Products(primKey, productId, productName, productStatus, logDate)

Values
(1, 201, 'pen', 'received', '01/01/2011'),
(2, 201, 'pen', 'sold', '01/02/2011'),
(3, 201, 'pen', 'paid', '01/03/2011'),
(4, 201, 'pen', 'returned', '01/04/2011'),
(5, 201, 'pen', 'refurbished', '01/05/2011'),
(6, 202, 'pencil', 'received', '01/06/2011'),
(7, 202, 'pencil', 'sold', '01/07/2011'),
(8, 202, 'pencil', 'paid', '01/08/2011'),
(9, 201, 'pen', 'sold', '01/09/2011'),
(10, 201, 'pen', 'paid', '01/10/2011')

/* temp table records */
Select * From #Products order by productId

/* The desired outcomes would be showing only the Record 3 and 10.

This is a "fake" query to get the results:
*/
Select * From #Products Where primKey in (3, 10) order by productId

View 9 Replies View Related

Reporting Services :: Select Text Field Dataset Based On User Select Option?

Aug 4, 2015

I have a report that uses different datasets based on the year selected by a user.

I have a year_id parameter that sets a report variable named dataset_chosen. I have varified that these are working correctly together.

I have attempted populating table cell data to display from the chosen dataset. As yet to no avail.

How could I display data from the dataset a user selects via the year_id options?

View 4 Replies View Related

How To: Create A SELECT To Select Records From A Table Based On The First Letter.......

Aug 16, 2007

Dear All
I need to cerate a SP that SELECTS all the records from a table WHERE the first letter of each records starts with 'A' or 'B' or 'C' and so on. The letter is passed via a parameter from a aspx web page, I was wondering that someone can help me in the what TSQL to use I am not looking for a solution just a poin in the right direction. Can you help.
 
Thanks Ross

View 3 Replies View Related

T-SQL (SS2K8) :: Select Statement With Derived Order Status

Aug 28, 2015

Here is my requirement

Table 1 Order
order ID, Sales order ID order date, order type

Table 2 Order details
Order Details ID, Order ID, Order Stage

Table 3 Related Order details
Order ID(FK to Order ID), Related Order Details ID(FK to Order Details ID), Related Order ID( FK to Order ID)

Here is example

Table 1 Order
1, 1234, 2015-01-01, Refill
2, 1234, 2015-02-02, Extension

Table 2 Order Details
1, 1, Approved
2, 1, Approved
3, 2, Rejected

Table 3 Related Order Details
2, 1, 1
2, 2, 1

I have to Select Order, Order Details and Order Status

Order Status is determined from Order Stage as follows:

If, at least one order detail line(from Order Details and Related Order details table) is approved, that Order status=Approved.

For the example, Order Status of Order ID=2, is Approved based on order status for order details lines 3(from table 2) and order details ID 1 and 2 (from table 3)

How to combined order stage from table 2 and table 3 and then compute order status.

View 3 Replies View Related

Reports Based On Multiple Databases

Oct 23, 2007

I'm new to developing reports using 2005 SQL Reporting Services. Is it possible to develop reports based on more than one database, (generally MS SQL)? I need to set up a dataset that includes tables from several different databases linked together so that information can be viewed together in one report.
thanks

View 9 Replies View Related

Outputting Status To Job Log From SSIS ActiveX Component

Apr 29, 2008



I have an ActiveX component in my SSIS package, and it is written in VB. Something is going wrong, and I'm not sure what. I works fine when run from Visual Studio, but when I move it to our server and try to run it from a job there, it fails. I'd like to know WHERE in the component it is failing, so I'd like to send output to something as it goes along, so I can see how far it is getting.

How do I send text to an output or log file?

I can't use MsgBox, because, of course, when it runs as a job, it is putting that message box up on the server, where there is no-one to respond to it, so it hangs. I'm in the process of converting it from a DTS to SSIS, and it does have several instances of MsgBox now. And it's locking up -- on one of them.

I have logging turned on for the job step, and it is writing to a dbo.sysdtslog90 table, but all it tells me is that it is starting that ActiveX script task.


What can I replace the MsgBox with, so that it outputs somewhere to a file? Is there a simple command, like WScript.Echo or Console.WriteLine (neither of which I can get to work)?

Thank you for any help you can provide.

View 7 Replies View Related

Creating Report Based On Multiple Databases?

May 6, 2008

My application uses 2 databases from 2 different server. When i create a report model in business intellegence development studio I can only create 1 datasource for each database, and 1 datasource view for each datasource. Then when I create a new report, I can only use one datasource.

Is it possible that my report can be built on more than one databases?

thanks in advance

View 4 Replies View Related

URGENT HELP PLS: Select Substring('NY',status/1024&&1+1,1) From Master..sysdatabases Where Name=DB_NAME()

Jul 20, 2005

Hi Everyone,All the very best for 2004!!i need urgent help with this problem, the users are about to skin mealive!!we have an access front end with linked to sql server 2k tables.when a user tries to insert a record into one of the tables it"randomly" returns a generic ODBC error and fails to save. on otheroccasions the same record will save.a trace was applied and the following select seemed to appear rightafter the insert statement whenever it failed:select substring('NY',status/1024&1+1,1) from master..sysdatabaseswhere name=DB_NAME()i had a look at other articles in the groups re this select statement,but could not find a clear answer.i have tried the insert statements as both SQL pass throughs and justplain docmd.runsql'scan someone help me with the following:* what is the purpose of the select?* what other investigations can i do to get more info on why thisshould be happening?* how can i stop it?the table i am doing the inserts into is showing as have a numericdata type field in sqlserver, but the linked table shows this numericfield as text - could this be the problem?? this field is not used inthe insert statement.i could not find any references in the MS knowledge base.any and all help would very gratefully received.Edwinah63

View 2 Replies View Related

How To Display Status Information Of The Running SSIS Package In ASP.net

Nov 22, 2007



hi all,
i have a package; thats executing another 4 packages, i can able to execute these packages in ASP.net

I want to display the status notification of the executing packages in the webpage( while packages are being executed, i want to display which package is executing, and what is the status of the package! whether it is executing or not? or it is raising any errors?) every thing i want to give in the front end--> its not my requirement, my client ask me to do that!

so please help me! how to display these status information of the running package in asp.net (c#)

(i'll give you one good chocolate if you give me good solution!)

post your mails to vivekanandaraj.natarajan@honeywell.com Thanks you a lot!

View 1 Replies View Related

SSIS Maintenance Plan

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

[SQL2000] Permissions To Use View Based On Tables From Many Databases

Feb 21, 2006

HiI have two databases: Customers and Operations. In Customers database I havemade a view based on a few tables from both Customers and Operations (leftjoin - customers without any operations). In the same database (Customers) Ihave created a stored procedure based on the view. Finally I'd like to giveto some users permission only to exec the stored procedure.Have I to add the users to Customers? If yes, please describe me how tolimit the users privileges only to execution the stored procedure (no rightsto open tables or view from Customers).Regards,GrzegorzPs. I had sent the post on microsoft.public.sqlserver.security, but I had noanswer.

View 5 Replies View Related

Adding A Column Name To A Table In Each Of The Databases Based On A Condition

Oct 3, 2007

i have the folowing databases DB1,DB2,DB3,D4,DB5........

i have to loop through each of the databases and find out if the database has a table with the name 'Documents'( like 'tbdocuments' or 'tbemplyeedocuments' and so on......)

If the tablename having the word 'Documents' is found in that database i have to add a column named 'IsValid varchar(100)' against that table in that database and there can be more than 1 'Documents' table in a database.


can someone show me the script to do it?


Thanks.

View 3 Replies View Related

SQL 2012 :: SSIS - OLEdb Destination Get Success / Failure Status

Feb 19, 2015

I have a SSIS pkg that gets data from SQL and do data conversion and Insert into OLE db AS400 destination, There is a flag column in SQL table , that has to be updated to true, once the records are inserted in AS400 how do i do that in SSIS

SQL oledb ---------> dataConversion ---------------> AS400 OLE db Destination
|
update SQL table Flag column<---------------------------------|

View 9 Replies View Related

Data Conversion Failed From String To Numeric Returned Status Value 6 In Ssis

Sep 6, 2007

Hi i am working on sending data from a dat file to table in sql server Database and i am using the Data conversion transformation in ssis to convert string of fixed length into numeric (11,5) which is the datatype for the price field in the table and its returning an error saying that status vale 6 and error text as Conersion Failed sue to overflow of specific type ... Can anyone let me know how to overcome this error.

View 6 Replies View Related

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 View Related

Slowness In SSIS Package And The Importance Of Disk Maintenance

Sep 22, 2006

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.

-Kory

View 1 Replies View Related

SSIS Erros Relating To Maintenance Plan Creation

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

Maintenance Jobs Failing SSIS Subsystem Failed To Load

May 1, 2008

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?

Thanks in advance for any advice.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved