SQL Server and MS BI With a Geek Touch

In this blog, I will walk you through the creation of excel files ( xls / Excel 2003)  dynamically through SSIS.

Scenario: every day one of my process runs to pull data from SQL server table to excel file. I need to use unique file everyday with name as “taxonomy_<date>.xls” , for example on 14^th of January 2011 the file name should be “Taxonomy_01142011.xls” with the excel sheet name “TaxonomyValues”.

It is pretty simple to create this file dynamically. At first we need to set up an excel connection manager pointing to the file. The connection manager needs to be dynamically configured to point to the correct file everyday, in our case “Taxonomy_01142011.xls” on 14^th Jan. To do this,

1. go to properties window of the excel connection manager
2. click on expression and the browse ( … symbol) and choose “excel File Path” property. ( please refer the pictures below)
3. Copy and paste the expression given below or develop similar expression. Click on evaluate expression and it will display the file path as evaluated value.

“C:\\Taxonomy_”+ (MONTH( GETUTCDATE()  ) < 10? “0 “+(DT_WSTR,2) MONTH( GETUTCDATE()  ) DT_WSTR,2)MONTH( GETUTCDATE()  ))

+(DAY( GETUTCDATE()  ) < 10? “0 “+(DT_WSTR,2) DAY( GETUTCDATE()  ) DT_WSTR,2)DAY( GETUTCDATE()  ))

+(DT_WSTR,4)YEAR( GETUTCDATE()  ) +”.xls”

Also you need to use the settings as below for connection manager ( for .xls files)

Read more…

There are instances when we may have to drop all the connections to a database for example to rename a database. The script below can be used to drop all the connections to the database.

DECLARE @dbname nvarchar(128)
SET @dbname = 'DB name here' -- db to drop connections
DECLARE @processid int
SELECT @processid = min(spid)
from master.dbo.sysprocesses
where dbid = db_id(@dbname)
WHILE @processid IS NOT NULL
BEGIN
EXEC ('KILL ' + @processid)
SELECT @processid = min(spid)
from master.dbo.sysprocesses
where dbid = db_id(@dbname)
END