Feedback from a SSIS Check Database Integrity Task

SQL Server Integration Services

Index ‹ SQL Server

- Previous
  - 1
    - XML Source Produces DT_UI8 Key Columns - Which SQL Server Data Type to Store? Thanks for the link. Unfortunately, it makes my point for me, quite forcefully. There is no datatype in any supported database, that can accept DT_UI8 without conversion, so, what's the purpose of using that datatype in the XML Source Surely they didn't need the extra bit! Tag: Feedback from a SSIS Check Database Integrity Task
  - 2
    - Unable to delete duplicate records Bill, Since you have so many duplicate rows, the easiest method would most likely be the following: SELECT a single copy of each row into a new table. EITHER DELETE the contents of the current table (or TRUNCATE the table AND add the contents of the new table back to the original table OR DROP the original table RENAME the new table to the original table Here is how to write a query to extract a single row in the place of two duplicates. Code Snippet SELECT Column1, Column2, {list all columns} INTO Your NewTable FROM Your OldTable GROUP BY Column1, Column2, {list all columns exactly like above} Tag: Feedback from a SSIS Check Database Integrity Task
  - 3
    - How to find out the dts package is running If you connect to the Integration Services service through SSMS, you can select the Running Packages folder. You can also use the Application.GetRunningPackages method in the SSIS API to get all currently running packages. Check out this topic in Books Online " Managing Running Packages Programmatically". Tag: Feedback from a SSIS Check Database Integrity Task
  - 4
    - Local system account and Mirroring. Hi Bob, Thanks for your reply. I configured the endpoints using windows authentication but no luck. It still failed. we will use domain accounts. Thanks AK Tag: Feedback from a SSIS Check Database Integrity Task
  - 5
    - Logging Inserts From Flat File Your response spawns another question.. Not being familiar with SSIS I would like to know that we are building this package correctly. I have been working on a solution with another developer for processing flat files to a SQL server. We are getting 45 flat files from a known directory and processing each file into its own table in the SQL server. Field names match in both the flat file and the SQL table. the data types have been taken care of too. Basically, we have a control flow with a foreach loop and within the loop we have one Data Flow task. When you open this one Data Flow task, we have set up 45 different dataflow tasks that all have the following sequences: Flat file source (each have their own connection manager that points to a different text file) So we have 45 different flat file connection mangers. 1 is connected to a script task that creates a row number to be used if an error occurs so we can get the row number of the error - since we cannot get the column name in error (as you can see for my previous postings) Number 2 is connected to an OLE DB Destination (the SQL table the flat file will be inserted into) and this is where we have mapped the flat file columns to the SQL table columns Number 3 is connected to another script task via the Error Output where we set the variables for the table name (or flat file name) and row number along with the error info. Number 4 is then connected to an OLE DB Command where we are calling a stored proc to log the error. The .dtsx file is 9500k and takes a while just to open the package (oh yeah, when I do open the package it keeps telling my a there are long lines of text and asks me if I would like to continue loading the package ). When this package runs, it takes a little while, but it is successful. I just want to hear from someone who is experienced with SSIS to let me know if our approach is headed in the right direction. Any and all suggestions would be greatly appreciated! Tag: Feedback from a SSIS Check Database Integrity Task
  - 6
    - validating sequence container freeze I am attempting to open up my ssis integration project but when I try to open up packages it says @ the bottom "Validating Sequence Container" and it just stays there. I cannot click on anything and nothing is responsive. Does anyone know why this is happening Tag: Feedback from a SSIS Check Database Integrity Task
  - 7
    - how to use input column inside script component Hi, I have a script component and under input columns, I checked one of the fields I want to use inside the script. It's under the Input 0 in the hierarchical tree inside the inputs and outputs. How do i use this inside the script cherriesh Tag: Feedback from a SSIS Check Database Integrity Task
  - 8
    - error handling in OLEDB source in data flow I agree with Phil 1) If you need to Insert with same datbase, use Execute SQl task 2) If in different database, use data flow task in which you can specify the source and destination. secondly if you have some queries running bnefore running and this all is happening in a sproc then you can run that Sproc using Execute SQL task- Simple!!! Tag: Feedback from a SSIS Check Database Integrity Task
  - 9
    - SQL 2005 SP2 running on Vista 64 Extremely Slow Performance running scripts Okay, after reviewing in further detail I have deduced that: Overall, adding fulltext indexes and creating objects takes twice as long and population of default data into tables is six times longer. It appears that the following method of inserting data into a table in quantities such as 100's at a time is very inefficient. INSERT INTO [dbo] . [table_name] ( [column1] , [column2] , [column3] , [column4] ) VALUES ( 1 , 'name' , 1024 , 'name' ) INSERT INTO [dbo] . [table_name] ( [column1] , [column2] , [column3] , [column4] ) VALUES (2 , 'name' , 1024 , 'name') etc....... Tag: Feedback from a SSIS Check Database Integrity Task
  - 10
    - PLEASE HELP!!!! MDX-EXPERTS!!! LOCAL CUBE SECURITY thanks for reply. If i'm slicing the cube on the Sales Reps I will only see the cells for this Sales Reps and not for its customers since Sales reps is a separate Dimension and not an attribute of the Customer dimension. Am I wrong Tag: Feedback from a SSIS Check Database Integrity Task
  - 11
    - filtering calendar control? Hello, Please, help with following issues. I have 3 parameters in the report. When user selects 1st one from dataset, one of the fields gives me object creation date. Calendar control is supposed to be filtered to allow users select the dates greater than selected one. How can I filter (disable dates in) calendar control Any suggestion/comment highly appreciated Tag: Feedback from a SSIS Check Database Integrity Task
  - 12
    - Cannot create Database Mail Account Thank you Dale. I'll try regenerating the SMK and give you feedback on how it goes. Jerome Tag: Feedback from a SSIS Check Database Integrity Task
  - 13
    - Report Header Hi, I'm so sorry, I forget to link it. Here you are: http://technet.microsoft.com/en-us/library/ms159836.aspx or in BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsrvr9/html/d2cfa39f-7092-4c55-aab2-6d28ab5731e2.htm Regards, Janos Tag: Feedback from a SSIS Check Database Integrity Task
  - 14
    - ForeignKey Constraint from a Lookup Table Hi, I'm trying to figure out how to setup a Constraint (Foreign Key ) from a lookup table. For the following example, I want to constrain to constrain so that a Car.ParkingSpotNumber is unique for a given ParkingFacility. Any thoughts Thanks! Andy Here's the scenario: CAR table: Id (PrimaryKey) LicensePlateNumber ParkingSpotNumber PARKINGFACILITY__CAR__LOOKUP table: Id (PrimaryKey) CarId (Foreign Key to Car.Id) ParkingFacilityId (Foreign Key to ParkingFacility) PARKINGFACILITY table: Id (PrimaryKey) PARKINGLOT table: Id (Foreign Key to ParkingFacility.Id) (PrimaryKey) Address PARKINGGARAGE table: Id (Foreign Key to ParkingFacility.Id) (PrimaryKey) Address Here's the T-SQL code to make a SQLServer2005 database for this scenario: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID ( N '[dbo].[Car]' ) AND type in ( N 'U' )) BEGIN CREATE TABLE [dbo] . [Car] ( [Id] [bigint] NOT NULL, [CustomerName] [nvarchar] ( 50 ) NOT NULL, [ParkingSpotNumber] [int] NOT NULL, CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED ( [Id] ASC ) WITH ( PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] ) ON [PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID ( N '[dbo].[ParkingFacility]' ) AND type in ( N 'U' )) BEGIN CREATE TABLE [dbo] . [ParkingFacility] ( [Id] [bigint] NOT NULL, CONSTRAINT [PK_ParkingFacility] PRIMARY KEY CLUSTERED ( [Id] ASC ) WITH ( PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] ) ON [PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID ( N '[dbo].[ParkingFacility__To__Car_Lookup]' ) AND type in ( N 'U' )) BEGIN CREATE TABLE [dbo] . [ParkingFacility__To__Car_Lookup] ( [Id] [bigint] NOT NULL, [CarId] [bigint] NOT NULL, [ParkingFacilityId] [bigint] NOT NULL, CONSTRAINT [PK_ParkingFacility__To__Car_Lookup] PRIMARY KEY CLUSTERED ( [Id] ASC ) WITH ( PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] ) ON [PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID ( N '[dbo].[ParkingLot]' ) AND type in ( N 'U' )) BEGIN CREATE TABLE [dbo] . [ParkingLot] ( [Id] [bigint] NOT NULL, [Address] [nvarchar] ( 50 ) NOT NULL, CONSTRAINT [PK_ParkingLot] PRIMARY KEY CLUSTERED ( [Id] ASC ) WITH ( PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] ) ON [PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID ( N '[dbo].[ParkingGarage]' ) AND type in ( N 'U' )) BEGIN CREATE TABLE [dbo] . [ParkingGarage] ( [Id] [bigint] NOT NULL, [Address] [nvarchar] ( 50 ) NOT NULL, CONSTRAINT [PK_ParkingGarage] PRIMARY KEY CLUSTERED ( [Id] ASC ) WITH ( PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] ) ON [PRIMARY] END GO IF NOT EXISTS ( SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID ( N '[dbo].[FK_ParkingFacility__To__Car_Lookup_Car]' ) AND parent_object_id = OBJECT_ID ( N '[dbo].[ParkingFacility__To__Car_Lookup]' )) ALTER TABLE [dbo] . [ParkingFacility__To__Car_Lookup] WITH CHECK ADD CONSTRAINT [FK_ParkingFacility__To__Car_Lookup_Car] FOREIGN KEY ( [CarId] ) REFERENCES [dbo] . [Car] ( [Id] ) GO ALTER TABLE [dbo] . [ParkingFacility__To__Car_Lookup] CHECK CONSTRAINT [FK_ParkingFacility__To__Car_Lookup_Car] GO IF NOT EXISTS ( SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID ( N '[dbo].[FK_ParkingFacility__To__Car_Lookup_ParkingFacility]' ) AND parent_object_id = OBJECT_ID ( N '[dbo].[ParkingFacility__To__Car_Lookup]' )) ALTER TABLE [dbo] . [ParkingFacility__To__Car_Lookup] WITH CHECK ADD CONSTRAINT [FK_ParkingFacility__To__Car_Lookup_ParkingFacility] FOREIGN KEY ( [ParkingFacilityId] ) REFERENCES [dbo] . [ParkingFacility] ( [Id] ) GO ALTER TABLE [dbo] . [ParkingFacility__To__Car_Lookup] CHECK CONSTRAINT [FK_ParkingFacility__To__Car_Lookup_ParkingFacility] GO IF NOT EXISTS ( SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID ( N '[dbo].[FK_ParkingLot_ParkingFacility]' ) AND parent_object_id = OBJECT_ID ( N '[dbo].[ParkingLot]' )) ALTER TABLE [dbo] . [ParkingLot] WITH CHECK ADD CONSTRAINT [FK_ParkingLot_ParkingFacility] FOREIGN KEY ( [Id] ) REFERENCES [dbo] . [ParkingFacility] ( [Id] ) GO ALTER TABLE [dbo] . [ParkingLot] CHECK CONSTRAINT [FK_ParkingLot_ParkingFacility] GO IF NOT EXISTS ( SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID ( N '[dbo].[FK_ParkingGarage_ParkingFacility]' ) AND parent_object_id = OBJECT_ID ( N '[dbo].[ParkingGarage]' )) ALTER TABLE [dbo] . [ParkingGarage] WITH CHECK ADD CONSTRAINT [FK_ParkingGarage_ParkingFacility] FOREIGN KEY ( [Id] ) REFERENCES [dbo] . [ParkingFacility] ( [Id] ) GO ALTER TABLE [dbo] . [ParkingGarage] CHECK CONSTRAINT [FK_ParkingGarage_ParkingFacility] Tag: Feedback from a SSIS Check Database Integrity Task
  - 15
    - unable to start Analysis services - sql server 2005 I killed ssas whne it was not responding. Bad Idea, I guess. Now, it starts and then stops. --------------------------- Services --------------------------- The SQL Server Analysis Services (MSSQLSERVER) service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service. --------------------------- OK --------------------------- I ran it from command prompt, and got: Message: Errors in the metadata manager. An error occurred when instantiating a metadata object from the file, '\\ \C:\Program Files\Microsoft SQL Server\MSSQL. 3\OLAP\Data\MonthByMonthDT.0.db\bla bla bla.1.dms.xml'. (Source : MSSQLServerOLAPService, Type: 3, Category: 289, Event ID: 0xC1210012) So following some other suggestions in this forum, I deleted that data, then cleared out the whole data folder and still getting same problem. Also, I was messing around with configuration around when this hapend, allowing openrowset commads, not sure if that is possibly the cause or how I can reset to default config in order to try. Right now, runnning gives this: C:\Program Files\Microsoft SQL Server\MSSQL.3\OLAP\bin>msmdsrv.exe -s "C:\Progra m Files\Microsoft SQL Server\MSSQL.3\OLAP\Config" -s C:\Program Files\Microsoft SQL Server\MSSQL.3\OLAP\Config Message: The flight recorder was started. (Source: MSSQLServerOLAPService, Type: 1, Category: 289, Event ID: 0x41210005) Message: Service stopped. (Source: MSSQLServerOLAPService, Type: 1, Category: 28 9, Event ID: 0x41210001) Any Ideas on how to get this puppy started Adam Tag: Feedback from a SSIS Check Database Integrity Task
  - 16
    - Find the nonexisting Event Here I give both fast & slow one. According to your data-volume which ever fits use it, Code Snippet Create Table #event_codes ( [EventCodeIdNo] int primary key , [EvenCode] Varchar ( 100 ) ); Insert Into #event_codes Values ( '13' , 'Criminal-History' ); Insert Into #event_codes Values ( '31' , 'DL' ); Insert Into #event_codes Values ( '30' , 'CDL' ); Insert Into #event_codes Values ( '75' , 'EMPLOYMENT-REF' ); Insert Into #event_codes Values ( '74' , 'Texas-Lic' ); Insert Into #event_codes Values ( '2' , 'CDA' ); Insert Into #event_codes Values ( '4' , 'Emp-Suggestions' ); Create Table #events ( [PersonIdNo] int , [EventCodeIdNo] int primary key ( [PersonIdNo] , [EventCodeIdNo] ) ); Insert Into #events Values ( '6349' , '13' ); Insert Into #events Values ( '6349' , '31' ); Insert Into #events Values ( '6349' , '30' ); Insert Into #events Values ( '6345' , '75' ); Insert Into #events Values ( '6345' , '13' ); Code Snippet --complex & slow one select pes . [PersonIdNo] , pes . [EventCodeIdNo] , pes . [EvenCode] from #events es full outer join ( select Distinct es . [PersonIdNo] , ec . [EventCodeIdNo] , ec . [EvenCode] from #events es cross join #event_codes ec ) as pes On pes . [PersonIdNo] = es . [PersonIdNo] and pes . [EventCodeIdNo] = es . [eventcodeidno] Where es . [EventCodeIdNo] is null Code Snippet --fast & good one select * from #event_codes ec cross join ( select distinct [personidno] from #events ) p where NOT exists ( select 1 from #events es where es . [eventcodeidno] = ec . [eventcodeidno] and es . [personidno] = p . [personidno] ) Tag: Feedback from a SSIS Check Database Integrity Task
- Next
  - 1
    - Master and SubReports I will take a look. Many Thanks. Its up and running now, just formatting and sizing now Tag: Feedback from a SSIS Check Database Integrity Task
  - 2
    - How to not display the "no relationship" attributes and measurements? I was able to hide the "no relationship" attributes and measurements by setting IgnoreUnrelatedDimension to 'False' on the measure groups. Tag: Feedback from a SSIS Check Database Integrity Task
  - 3
    - "could not validate service accounts" error when doing unattended install - 2005 sql server Hi, I am working on making life a little easier when building customer machines and part of this undertaking has been to automate/configure some unattended sql server installs. This was the best walkthrough guide I could locate and I have followed it almost perfectly (except for renaming instance, accts, pwds): http://www.databasejournal.com/features/mssql/article.php/3616901 Thus far I have not been able to get the install to fully take. It currently is hanging on the "could not validate service accounts" part when installing database services (which comes right after PID verification and verify IIS install, I think). I am calling my setup via a batch. This batch has "D:\setup.exe /settings c:\install\install.ini /qb" within it. Here is my .ini file: *note the ADDLOCAL line has been broken into two lines for readability.* *once I get this .ini to work I need to add in reporting services stuff* [Options] ADDLOCAL=SQL_Engine,Analysis_Server, AnalysisSharedTools,Client_Components, Connectivity,SQL_Documentation,SQL_Tools90 INSTALLSQLDIR=C:\Binaries INSTALLSQLSHAREDDIR=C:\Binaries INSTALLSQLDATADIR=C:\DSDEVICE INSTALLASDATADIR=C:\ASDEVICE INSTANCENAME=MSSQLSERVER SECURITYMODE=SQL SAPWD=Mantissa SQLACCOUNT=System\Mantissa SQLPASSWORD= Mantissa AGTACCOUNT= System\Mantissa AGTPASSWORD= Mantissa ASACCOUNT= System\Mantissa ASPASSWORD= Mantissa SQLBROWSERACCOUNT= System\Mantissa SQLBROWSERPASSWORD= Mantissa SQLCOLLATION=Latin1_General_CI_AS ASCOLLATION=Latin1_General_CI_AS DISABLENETWORKPROTOCOLS=0 _________________________________________________ My server name is merlin1, it is not currently on a domain. A local windows account exists with the username "mantissa". Some of the different Account combinations I have tried are as follows: System\Mantissa NT Authority\System NT Authority\Mantissa Merlin1\Mantissa Mantissa\Mantissa Mantissa All of these reach the same point and stop the install with the error "could not validate service accounts". I assume I am just entering the wrong info here. Maybe I am mistaken as to the composition of these account paired values Could someone help take the time to clarify this for me If I can just get one install to go off without any problems then I am sure cutting/pasting of different install options shouldn't slow me down anymore. This appears that it will install everything I need, except reporting services. Or, if someone has a better article for me to try and follow along with then let me know. Thank You in advance for any assistance provided. Tag: Feedback from a SSIS Check Database Integrity Task
  - 4
    - Unable to deploy a report well turns our roguputa was right about why i couldn't deploy, had the wrong path specified. so now, thank's to your guys help the only question remaining is : Can windows credentials be specified for a report and not require users to type in user/pass if they have rights to a report Tag: Feedback from a SSIS Check Database Integrity Task
  - 5
    - Programatically created DataFLow task fails Jamie Thomson wrote: MohitGupta wrote: Hi Jamie, There is no change in code because it was deployed in production. Is there possibility of this error being caused by any SQL server service pack or hotfix on the server. Thanks Mohit yes, its certainly a possibility. That's what I meant by identify what has changed. Code changes Service pack changes Windows Update changes They're all changes that could *potentially* cause problems. -Jamie Any pointers to the article resolving this specific error will be very helpful. Mohit Tag: Feedback from a SSIS Check Database Integrity Task
  - 6
    - Fact and Dimension linking Thx everyone... after posting I sat down and read the book again to make sure I hadn't missed anything out. And you are right, the tables are OK in my DSV, but the dimension had not been configured correctly w.r.t. its attributes. As soon as I had that done correctly, of course it all fell into place.. Thx again. Tag: Feedback from a SSIS Check Database Integrity Task
  - 7
    - when using advanced tab in lookup transformation Hi all when using advanced tab in lookup transformation , for what any suggest i will be appreciated , thanks in advance Tag: Feedback from a SSIS Check Database Integrity Task
  - 8
    - How to de-normalize one to many relationships? JayH, Yes, you have done a better job of explaining my issue that I have. I believe I comprehend the concept. I am working on your concept now. Be back later today. With me luck! I am so remedial in SSIS... Tag: Feedback from a SSIS Check Database Integrity Task
  - 9
    - Batch process for sending mail Here's the basic template for the script to be executed. You'll need to adjust the queries to be exactly what you need. I just did a simple query to get the admin who had 5 or more requests present. Code Snippet create table #Admin ( AdminID int , Email varchar ( 50 )) insert into #Admin values ( 1 , 'admin1@mycompany.com' ) insert into #Admin values ( 2 , 'admin2@mycompany.com' ) insert into #Admin values ( 3 , 'admin3@mycompany.com' ) insert into #Admin values ( 4 , 'admin4@mycompany.com' ) create table #AdminArea ( AdminID int , AreaCode int ) insert into #AdminArea values ( 1 , 100 ) insert into #AdminArea values ( 2 , 200 ) insert into #AdminArea values ( 3 , 300 ) insert into #AdminArea values ( 4 , 400 ) create table #Area ( AreaCode int , AreaName varchar ( 50 )) insert into #Area values ( 100 , 'Area 100' ) insert into #Area values ( 200 , 'Area 200' ) insert into #Area values ( 300 , 'Area 300' ) insert into #Area values ( 400 , 'Area 400' ) create table #WebSummit ( SummitId int , RequestorName varchar ( 50 ), DateOfRequest datetime , RequestStatus int , AreaCode int ) insert into #WebSummit values ( 1 , 'George Jetson' , getdate (), 1 , 100 ) insert into #WebSummit values ( 1 , 'Jane Jetson' , getdate (), 1 , 200 ) insert into #WebSummit values ( 1 , 'Fred Flinstone' , getdate (), 1 , 100 ) insert into #WebSummit values ( 1 , 'Wilma Flintsone' , getdate (), 1 , 100 ) insert into #WebSummit values ( 1 , 'Barney Rubble' , getdate (), 1 , 100 ) insert into #WebSummit values ( 1 , 'Betty Rubble' , getdate (), 1 , 100 ) drop table #to select a . AdminID , a . Email into #to from #Admin a where a . AdminID in ( select a . AdminID from #Admin a inner join #AdminArea aa on a . AdminID = aa . AdminID inner join #Area b on aa . AreaCode = b . AreaCode inner join #WebSummit w on b . AreaCode = w . AreaCode group by a . AdminID having count (*)> 4 ) declare elist cursor for select adminid , email from #to declare @id int , @email varchar ( 50 ), @cmd nvarchar ( 4000 ) open elist if @@cursor_rows > 0 begin fetch next from elist into @id , @email while @@fetch_status = 0 begin set @cmd = N 'put your query here WHERE AdminID = ' + cast ( @id as varchar ( 10 )) exec master . dbo . xp_sendmail @recipient = @email , @query = @cmd , @subject = 'Summit Follow-up' fetch next from elist into @id , @email end end close elist deallocate elist Tag: Feedback from a SSIS Check Database Integrity Task
  - 10
    - Query large database time out in Query Analyzer Sandeep.L wrote: Hi, I am also facing a similar problem, but the no. of records is almost 10 million. Is there any way to optimize the database for simple data retrieve query, coz it takes around 10-15 mins to a simple Select * query. Awaiting replies, Thanks in Advance Sandeep Answer: Jack up the ram and add a few more processors. Hardware solution only folks. Your tables/db's past the point of no return. Adamus Tag: Feedback from a SSIS Check Database Integrity Task
  - 11
    - receive top 20 one follow up, when I send the message using the same conversation handle, the receive top (20) statement waits until the previous batch is committed before it will start retrieving the next 20. I guess this is because of the its now part of the same conversation group and service broker need to guarantee process order This is what my proc looks like BEGIN TRANSACTION WAITFOR ( RECEIVE top ( 20 ) -- get batched so that we can process same listid once message_type_name , message_body , -- the message contents conversation_handle -- the identifier of the dialog this message was received on FROM dbo .target into @PayloadData ), TIMEOUT 3000 -- if the queue is empty for three second, give UPDATE and go away -- do some processing of the records in @PayloadData COMMIT TRANSACTION if "-- do some processing of the records in @PayloadData" is taking a long time, its going to block the messages in the queue. If I remove the begin and commit transaction block, it only wait for the 3 seconds I specified. Question: is the transaction block necessary in the activated procedure. thanks Paul Tag: Feedback from a SSIS Check Database Integrity Task
  - 12
    - Where do I start ? Dear all, I never been using XML in SQL Server, and now I want to learn about it. Where do I start to learn this XML is it the same XML in general Thanks in advance, Hery Tag: Feedback from a SSIS Check Database Integrity Task
  - 13
    - RETURN statement I want to end the stored procedure if duplicate record is found. What would be the best way to do it In the calling program VB6, I would then like to capture the value 1 to display duplicate message. Thanks. IF EXISTS ( SELECT e.EmployeeID FROM Employee e WHERE e.EmployeeID = @NewEmployeeID ) DROP dbo.#NewEmployee RETURN 1 ELSE ... ... END Tag: Feedback from a SSIS Check Database Integrity Task
  - 14
    - Interface for Analysis Services 2005 Thanks for your post... But how can I control the access to the cubes or reports, if I have the users and groups inside tables and not in the active directory Thanks Tag: Feedback from a SSIS Check Database Integrity Task
  - 15
    - stop cell from trimming the value Hi, I am adding some empty spaces to the string value but it comes out trimmed. How can I fix that Thanks, Igor Tag: Feedback from a SSIS Check Database Integrity Task
  - 16
    - Unable to open Local Cube in Excel Hi, I have created a local cube (Offline cube) from SQL Server Analysis Services 2005(Using GLOBAL CUBE statement) with a name LocalCube1.cub. When I am trying to open the local cube using MS Excel 2003, it is giving a message "This DataSource has no visible Tables". I am trying to open the Local Cube file (.cub file) as follows : 1. In MS Excel -> Data -> Import External Data -> Import Data. Can any one help me in solving this problem. Any suggestions on why this message is occuring Regards, Gautham. Tag: Feedback from a SSIS Check Database Integrity Task

Mad_Mick

Hi All,

I have built a package that firstly shrinks a database and then backs it up which is scheduled to run each day, but I would like to add a check (Check Database Integrity) in as the first task and if the Database checks out ok then continue on, if not send an email.

Now, I am unsure if the Check Database Integrity Task in SSIS actually returns the success or failure message back to the package that I am after. The SQL behind the task includes the NO_INFOMSGS option.

Can anyone advise if it is possible to have the status/integrity of the database returned to the SSIS task and to proceed based on the result

Regards

Top

Re: Feedback from a SSIS Check Database Integrity Task

DarrenSQLIS

The Check Database integrity task will fail if the DB has a problem. The task fails; the details are raised in the error event and dumped to the log etc. You can use on Failure precedence constraints or an OnError event handler to capture that failure and do something if you wish.

You can test this yourself by hacking a DB at a binary level, or Tony gives a great explanation and method here -

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/03/10/how-to-create-a-corrupt-database-using-bulk-insert-update-and-bcp-sql-server-as-a-hex-editor.aspx

Top