SQL 2005 SSIS and Excel 2007

Most of us know how to use an Office Excel file in an SSIS package. As long as we use an Excel file with the .xls extension it would be fine. But, when it comes to Office Excel 2007 it would be a bit different. The Connection manager for Excel files option that we usually choose will not work.

Since Office Excel 2007 and its .xlsx extension came after a long time after SQL Server 2005 release and since this new format is entirely different from the previous formats we have a little problem in using Connection manager for Excel files option with Excel 2007.This was sorted out with the Service Pack 2. This Service Pack gave us a new driver which could be used for Office Excel 2007 files.

Here are the brief steps on how to create a connection manager for Excel 2007.

In a new or existing package,

1. Add a New Connection and choose the connection manager type either ADO.NET or OLEDB by right-clicking on the Connection Managers tab.

2. Click on New and under the Provider drop-down list, select Microsoft Office 12.0 Access Database Engine OLE DB Provider

3. Click OK

4. Click on “All” which is located on the left side of the connection manager window, and type “Excel 12.0” against the Extended Properties.

5. Now go back to the Connections tab and type in the file path of the Excel 2007 file along with the file name there.

6. Click OK and you are done.
7. The same can be used for Access database 2007.

SQL Server 2005's 5th system database - MSSQLSystemResource (The Invisible Database)

MSSQLSystemResource is a database that complements the master db. It is like the name smartly impels a resource database.
All system stored procedures, views and functions are stored here.

This database is hidden from the user. We can't view it in Object Explorer or with the use of sp_helpDB or by selecting from a sys.databases view. Resource database does not contain any of user data. This database has to be backed up using file-based backup or by using Drive Backups.

So how do we know its presence?
-Go to the Data directory of your SQL installation [Install Drive]:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data and there you will find the MSSQLSystemResource.mdf and MSSQLSystemResource.ldf.

If you want to see what is there in this database:

1. Stop the SQL Server service.
2. Copy both files and rename the copies to MSSQLSystemResource_Test.mdf and MSSQLSystemResource_Test.ldf.
3. Start the SQL server service
4. Attach the new files.
5. Query the database.

Search the Database

Searching for an object in a SQL 2000 Database is easier by using this undocumented stored procedure sp_MSobjsearch. This can be used to search any SQL objects such as User Table, System Table, View, SP, triggers, columns, etc...


EXEC sp_MSobjsearch
=============================================
--PARAMETERS
=============================================
@searchkey default NULL
@dbname default current db = db_name(), valid DB name or * (ALL)
@objecttype default 1 (user table), can be valid objtype or 4096 (ALL), see remarks @hitlimit default 100 rows, 0 is all results
@casesensitive default 0, only valid when server is case sensitive
@status default 0 = no status, 1 = send percentage progress status back based
database/step
@extpropname default NULL
@extpropvalue default NULL

=============================================
-- REMARKS
=============================================
@objecttype
user table = 1 from @dbname..sysobjects
system table = 2 from @dbname..sysobjects
view = 4 from @dbname..sysobjects
sp = 8 from @dbname..sysobjects
rf(repl sp) = 16 from @dbname..sysobjects
xp = 32 from @dbname..sysobjects
trigger = 64 from @dbname..sysobjects
UDF = 128 from @dbname..sysobjects
DRI Constraints = 256 from @dbname..sysobjects
log = 512 from @dbname..sysobjects
column = 1024 from @dbname..syscolumns
index = 2048 from @dbname..sysindexes
all = 4096
=============================================

Delete from Registry using SQL

xp_regdeletekey and xp_regdeletevalue are the two undocumented stored procedures that helps in deleting values and keys from registry. These stored procedures should be used very vary carefully as there are chances of harming the system and system may crash.

xp_regdeletekey
This is an extended stored procedure that will delete an entire key from the registry.
EXEC xp_regdeletekey @rootkey,@key
Example:-
EXEC master..xp_regdeletekey @rootkey='HKEY_LOCAL_MACHINE',
@key='SOFTWARE\Test'

xp_regdeletevalue

This is an extended stored procedure that will delete a particular value for a key in the registry.

EXEC xp_regdeletevalue @rootkey,@key,@value_name

Example:-

EXEC master..xp_regdeletevalue @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Test', @value_name='TestValue'

Registry writing and regisrty reading through SQL

In SQL server we have 2 undocumented stored procedures for reading from registry and for writing into registry. For reading from registry we use the xp_regread and for writing into registry we use xp_regwrite undocumneted extended stored procedures. These two SP`s can be found in master database of a particular server.

Usage :-
EXEC xp_regread @rootkey, @key,[@value_name],[@Value]
Example:-
EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key= 'SOFTWARE\Microsoft\Microsoft SQLServer\80\Replication\Subscriptions\',
@value_name= 'SubscriberEncryptedPasswordBinary'

EXEC xp_regwrite @rootkey,@key,@value_name,@type,@value
Example:-
EXEC master..xp_regwrite @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Test',
@value_name='TestValue', @type='REG_SZ', @value='Test'