My MCITP Transcripts

           02/10/10      70-431     Score: 1000 (Non-performance), 940 (Performance)
           02/17/10      70-444     Score: 968
           02/22/10      70-443     Score: 955
           02/27/10      70-441     Score: 983
           02/27/10      70-442     Score: 940
            03/01/10     70-453     Score: 1000 (design/optimize/maintain), 972 (implement)
            03/01/10     70-454     Score: 966 (DB Development), 1000 (Design and Data Access)
           06/10/10      70-445     Score: 1000
           06/10/10      70-455     Score: 976
           06/11/10      70-446     Score: 984

Wow!!! I am now a Microsoft Certified IT Professional in SQL Server  Administration and Development (2005/2008), and Microsoft Business Intelligence Development (2005/2008).

How to change the SQL Server stored package?

Cannot do it directly in the MSDB folder or the package store(the File System Folder) in SSMS/SSIS. Have to export it first. Change it in BIDS, then re-import it back to SQL Server/SSIS/MSDB.

Note: If the exported package file is double-clicked, the Execute Package Utility is opened. But it's hard to fix/change the connection managers, control flow/data flow tasks on this interface. It's better to edit it in BIDS.

Many table changes from AdventureWorks to AdventureWorks2008

e.g., person.contact is replaced with person.person. click the link below for more details.

http://msftdbprodsamples.codeplex.com/wikipage?title=AW2008Details&referringTitle=Home

Access is denied when executing a package with DTExecUI

Package execution is fine in BIDS as an admin. But I encountered an error by using the DTExecUI interface (e.g., access is denied to update the checkpoint file). In this case, I need to run the command prompt with the admin authority.

How to remove recent projects from Visual Studio Start Page

(source: http://aspnetcoe.wordpress.com/2007/02/06/how-to-remove-recent-projects-from-visual-studio-start-page/)

To remove the projects from the list, follow these steps:

1.Close Visual Studio if it is running.
2.Start the Registry Editor (run regedit).
3.Navigate to this registry key:
HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\8.0\ProjectMRUList
4.Then delete the key that has the project you do not want to keep in the list.

Caveat: The list only lists consecutive items starting from File1. So if in the above list I deleted File6, then only projects from File1 to File5 will be displayed in the recent project list. File7 and above will not be displayed. If you like File7 and above to be displayed in this case, you will need to rename one of the keys so that they form a consecutive numbered list.

How to make the ForEach Loop Container work - the project on pp 116-117

First, you need to change the path for the 3 excel files from c:\users\Administrator\... to wherever you actually store the files (e.g., c:\user\yourname\...) in both the ForEach container and the Excel Connection Manager properties (including Excel Source if necessary). Then, for the archive Connection Manager, you can create a folder. Finally, for the File System Task, you should use 'Copy File' for Operation and 'Archive' for 'DestinationConnection'. In this way, all of the 3 files are looped.

Update:

The previous approach does not use the property expression to update the Excel Connection Manager dynamically. If using the property expression as that on p. 117, the Excel Source component in the Data Flow tab will have an error icon for invalid connection (OLE DB Error), just ignore it as the DelayValidation property for the Foreach Loop container is set to True.

More updates on the project (5/15/2011):

1. To run 32-bit in 64-bit SSIS: Project-->xxx Project Properties-->Debugging-->run 64-bit FALSE
2. many warning and error messages related to SSIS Logging and Package Configurations (as I use 2008R2 not 2008). Need to fix them as well.
3. The project basically moves 3 excel files from the location specified in the LoopEach container to the location specified in the Archive connection manager (using copy not move)
4. The definition of the User Variable [User:FileName] may need to be changed well for a valid path.

Some Interview Questions - how to handle the large amount of deletion and how to handle the suspect mode of a database

Question 1: How to delete 10 million rows in the full mode?

Step 1 - deleting in chunks - maybe 10,000 records per time (divide and conquer)
Step 2 - keeping the row counts
Step 3 - using dbcc Shrinkfile as below (see http://support.microsoft.com/kb/907511)

To shrink a transaction log file that has little free space in SQL Server 2005, follow these steps:

1)    Back up the transaction log file to make most of the active virtual log files inactive. Therefore, the inactive virtual log files can be removed in a later step. To do this, run a Transact-SQL statement that is similar to the following Transact-SQL statement.

BACKUP LOG TestDB TO DISK='C:\TestDB1.bak'

2)    Shrink the transaction log file. To do this, run a Transact-SQL statement that is similar to the following Transact-SQL statement.

BCC SHRINKFILE (<FileName>, <TargetSize>) WITH NO_INFOMSGS

Note: In this statement, is a placeholder for the name of the transaction log file. In this statement, is a placeholder for the target size that you want the transaction log file to be. The target size must be reasonable. For example, you cannot shrink the transaction log file to a size that is less than 2 virtual log files.

3)    If the DBCC SHRINKFILE statement does not shrink the transaction log file to the target size, run the BACKUP LOG statement that is mentioned in step 1 to make more of the virtual log files inactive.

4)    Run the DBCC SHRINKFILE statement that is mentioned in step 2. After this operation, the transaction log file should be similar to the target size.

 Question 2: How to handle the suspect mode?

EXEC sp_resetstatus 'yourDBname';
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb('yourDBname')
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('yourDBname'REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER