To Find Larger Size Database

http://sql-bi-dev.blogspot.com/2010/07/find-largest-size-tables-in-database.html

The above link is the source of postings. and can find many more in that link.

Function to Split Multi-valued Parameters

How to split a comma seperated value?
Many times we need to write T-SQL statements to split a comma seperated value, however string is not necessarily to be comma seperated, it can be seperated by any delimiter e.g. comma (,), @, &, ; etc.

How to use Multi-valued Parameters of SSRS report in a Stored procedures?
One more question comes around, how to use a multi valued parameter of SSRS report in a Stored Procedure to filter report data? I am sure you can't use a multi valued parameter directly in T-SQL code without spliting multiple values, if you do so without spliting values, SPROC will throw an error.

To find the answer of above questions, you create a user defined function using below T-SQL code:

/**********************************************
CREATED BY HARI
PURPOSE : To split any multivalued string
seperated by any delimiter into multiple rows
***********************************************/
CREATE FUNCTION [dbo].[SplitMultivaluedString]
(
   @DelimittedString [varchar](max),
   @Delimiter [varchar](1)
)
RETURNS @Table Table (Value [varchar](100))
BEGIN
   DECLARE @sTemp [varchar](max)
   SET @sTemp = ISNULL(@DelimittedString,'')
                + @Delimiter
   WHILE LEN(@sTemp) > 0
   BEGIN
      INSERT INTO @Table
      SELECT SubString(@sTemp,1,
             CharIndex(@Delimiter,@sTemp)-1)
     
      SET @sTemp = RIGHT(@sTemp,
        LEN(@sTemp)-CharIndex(@Delimiter,@sTemp))
   END
   RETURN
END
GO


/* How to use this function:
-----------------------------------------
SELECT * FROM [dbo].[SplitMultivaluedString] ('1,2,3,4', ',')
SELECT * FROM [dbo].[SplitMultivaluedString] ('1;2;3;4', ';')
SELECT * FROM [dbo].[SplitMultivaluedString] ('Hari#Thomas','#')
*/

Sequence Container in SSIS - Different ways of using Sequence Containers

Integration Services includes the Sequence container, which makes it simple to divide the control flow in a package into groups of tasks and containers that you can manage as a unit.
Using Sequence containers lets you handle the control flow in a package in more detail, without having to manage individual tasks and containers.
If a package has many tasks, it can be helpful to group the tasks in Sequence containers.

I'll explain different scenarios where Sequence Container paly an important role to implement business rules.

Scenario 1:

• Sequence Task SC1 must execute first. SC1 may have many task with or without precedence constraints. The group of these task must execute prior to any other tasks in the package.
• Sequence Task SC2 must start after completion of SC1. SC2 must execute irrespective of Failure/Success of SC1.
• Sequence Task SC3 contains Maintenence Plan Tasks and should be followed by SC2. SC3 must execute only after successfully execution of SC2. 

Scenario 2:

• Sequence Task SC1 must execute first.
• Sequence Task SC2 must start based on logical condition. This condition can be based on package variables. 
• Sequence Task SC3 contains Maintenence Plan Tasks and should be followed by both SC1 and SC2. SC3 must execute after successfully execution of SC1. SC3 should execute regardless of SC2 but if SC2 executes, SC3 must execute after SC2.

  Scenario 3: This is real scenario in many BI applications.

• SC1 must execute prior to other task. This Sequence Container may contain many tasks to pull data from hetrogenious data sources.
• SC2 should be followed by SC1. This can be used to Insert/Update dimension tables and fact tables along with intermediate tables (if any). This container may further contain Sub - Sequence Containers e.g. SC2(a) to handle dimension Insert/Update and SC2(b) to update Intermediate and Fact tables.
• SC3 should be followed by SC2. This may contains SSAS Tasks to Build/Prcoess Cubes. This container may also have Sub - Sequence Containers e.g. SC3(a) to check whether Current Month partition exists or not. If Yes than Do Nothing Else Create Current Month Partitions using Script task. SC3(b) can be used to Process Dimensions and Current Month Partitions.
• SC4 can be used for Maintenence Plan tasks. If SC3 successfully completed (Evolution operation - Expression and Constraint) than take Backup of Cube, Shrink Datamart and Take Backup of Datamart.
• SC5 can be used for Recovery Paln. If SC3 fails, Restore Cube from previos & Latest Backup available..
  
  

However, there are many more ways of utilizing Sequence Containers. Further, things depends on complexity of business requirement rules defined.

Dynamic Database Connection using SSIS ForEach Loop Container

Did you ever come across a requirement where you have to run the same SQL statement(s) in multiple database server/instances using SSIS?

Many of my friends have asked about how to connect through multiple Databases from different Server using single Dynamic Connection. I want to explain this feature in this article. Basically, I want to execute one query (to calculate Record Counts for a given table) on a set of servers (which can be Dev, Test,UAT, PreProduction and Production servers). In my example, I am using ForEach Loop to connect to the servers one by one--> Execute the Query --> Fetch and store the data.

So here is the approach:

• Create a Table in your local database (whatever DB you want) and load all the connection strings. Within SSIS package, use Execute SQL Task to query all the connection strings and store the result-set in a variable of object type.
• Use ForEach Loop container to shred the content of the object variable and iterate through each of the connection strings.
• Place an Execute SQL task inside ForEach Loop container with the SQL statements you have to run in all the DB instances. You can use Script Task to modify your query as per your need.

Below is the details with an example:

STEP1:
To begin, Create two tables as shown below in on of the environment:

-- Table to store list of Sources
CREATE TABLE SourceList (
   ID [smallint],
   ServerName [varchar](128),
   DatabaseName [varchar](128),
   TableName [varchar](128),
   ConnString [nvarchar](255)
)

GO

-- Local Table to store Results
CREATE TABLE Results(
   TableName  [varchar](128),
   ConnString [nvarchar](255),
   RecordCount[int],
   ActionTime [datetime]
)
GO

STEP 2:
Insert all connection strings in SourceList table using below script:
INSERT INTO SourceList

SELECT 1 ID,
'(local)' ServerName, --Define required Server
'TestHN' DatabaseName,--Define DB Name
'TestTable' TableName,
'Data Source=(local);Initial Catalog=TestHN;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;' ConnString
 
Insert as many connections as you want.
 
STEP 3:
Add new package in your project and rename it with ForEachLoopMultipleServers.dtsx. Add following variable:
 

Variable	Type	Value	Purpose
ConnString	String	Data Source=(local); Initial Catalog=TestHN; Provider=SQLNCLI10.1; Integrated Security=SSPI; Auto Translate=False;	To store default connection string
Query	String	SELECT '' TableName, N'' ConnString, 0 RecordCount, GETDATE() ActionTime	Default SQL Query string. This can be modified at runtime based on other variables
SourceList	Object	System.Object	To store the list of connection strings
SourceTable	String	Any Table Name. It can be blank.	To store the table name of current connection string. This table will be queried at run time

STEP 4:
Create two connection managers as shown below:


Local.TestHN: For local database which has table SourceList. Also this will be used to store the result in Results table.
DynamicConnection: This connection will be used for setting up dynamic connection with multiple servers.
Now click on DynamicConnection in connection manager and click on ellipse to set up dynamic connection string. Map connection String with variable User::ConnString.

STEP 5:
Drag and drop Execute SQL Task and rename with "Execute SQL Task - Get List of Connection Strings". Now click on properties and set following values as shown in snapshot:
Result Set: Full Result Set
Connection: Local.TestHN
ConnectionType: Direct Input
SQL Statement: SELECT ConnString,TableName FROM SourceList

Now click on Result Set to store the result of SQL Task in variable User::SourceList.

STEP 6:
Drag and drop ForEach Loop container from toolbox and rename with "Foreach Loop Container - DB Tables". Double click on ForEach Loop container to open Foreach Loop Editor. Click on Collection  and select Foreach ADO Enumerator as Enumerator. In Enumerator configuration, select User::SourceList as ADO object source variable as shown below:

STEP 7: Drag and drop Script Task inside ForEach Loop container and double click on it to open Script Task Editor. Select User::ConnString,User::SourceTable as ReadOnlyVariables and User::Query as ReadWriteVariables. Now click on Edit Script button and write following code in Main function:

public void Main()

{
   try
   {
      String Table = Dts.Variables["User::SourceTable"].Value.ToString();
      String ConnString = Dts.Variables["User::ConnString"].Value.ToString();
      MessageBox.Show("SourceTable = " + Table + "\nCurrentConnString = " + ConnString);
      //SELECT '' TableName,N'' ConnString,0 RecordCount,GETDATE() ActionTime
      string SQL = "SELECT '" + Table + "' AS TableName, N'" + ConnString + "' AS ConnString, COUNT (*) AS RecordCount, GETDATE() AS ActionTime FROM " + Dts.Variables["User::SourceTable"].Value.ToString() + " (NOLOCK)";

      Dts.Variables["User::Query"].Value = SQL;
      Dts.TaskResult = (int)ScriptResults.Success;
   }
   catch (Exception e)
   {
      Dts.Log(e.Message, 0, null);
   }
}
 
STEP 8:
Drag and drop Data Flow Task and double click on it to open Data Flow tab. Add OLE DB Source and Destination. Double click on OLE DB Source to configure the properties. Select DynamicConnection as OLE DB connection manager and SQL command from variable as Data access mode. Select variable name as User::Query. Now click on columns to genertae meta data.

Double click on OLE DB Destination to configure the properties. Select Local.TestHN as OLE DB connection manager and Table or view - fast load as Data access mode. Select [dbo].[Results] as Name of the table or the view. now click on Mappings to map the columns from source. Click OK and save changes.
Finally DFT will look like below snapshot:

STEP 9: We are done with package development and its time to test the package.
Right click on the package in Solution Explorer and select execute. The message box will display you the current connection string.

 Once you click OK, it will execute Data Flow Task and load the record count in Results table. This will be iterative process untill all the connection are done. Finally package will execute successfully.

You can check the data in results table:
Here is the result:

SELECT * FROM SourceList

SELECT * FROM Results

Moving Database Files From One Drive to Another

In practice, database files grows everyday. Sometimes it occupy the complete disk and we may end up in unsufficeint mamroy - leading to unexpected results.

In this blog, I'll explain how to transfer database files from one drive to another. To explain, I'll create a test database TestDB. Here is the command:

USE [master]
GO

IF DB_ID(N'TestDB') IS NULL
BEGIN
CREATE DATABASE [TestDB] ON PRIMARY
(
   NAME = N'TestDB'
   ,FILENAME = N'C:\MSSQL\Data\TestDB.mdf'
   ,SIZE = 3MB
   ,MAXSIZE = 2048GB
   ,FILEGROWTH = 1024KB
)
LOG ON
(
   NAME = N'TestDB_log'
   ,FILENAME = N'C:\MSSQL\Data\TestDB.ldf'
   ,SIZE = 3MB
   ,MAXSIZE = 2048GB
   ,FILEGROWTH = 10%
)
END
GO

Now check existing files location:

USE TestDB
GO
SELECT name, physical_name, state_desc
FROM TestDB.sys.master_files
WHERE database_id = DB_ID(N'TestDB')
 

 
 
 
 
 
 
 
 
Now the database is created, you can create some tables and enter some data into the database, if you wish, Otherwise proceed like this:
 
Step1: Make the database OFFLINE.

USE [master]
GO
ALTER DATABASE TestDB SET OFFLINE

Step2: Move file Physically
Now you need to move that file physically from existing folder to the new the location. Open the parent folder (Here 'C:\MSSQL\DATA') , You can see both mdf and ldf files', make sure that you cut the appropriate file, in this example it is the Log file. Cut that "TestDB_log.LDF" file and paste it on "D:\MSSQL\Data"
 
Step3: Update the system reference
Once you move the file physically , you need to update the system reference using the ALTER DATABASE .. MODIFY FILE Command as shown below:
 
ALTER DATABASE TestDB
MODIFY FILE (
   NAME ='TestDB_log'
   ,FILENAME = 'D:\MSSQL\Data\TestDB.LDF'
)

Step5 : Make database ONLINE
Last step is to make database online as shown below:

ALTER DATABASE TestDB SET ONLINE
GO

Date Calulations

WEEK START DATE and WEEK END DATE

declare @dtDate as datetime

set @dtDate= getDate()

SET DATEFIRST 1

--WEEK START DATE

select convert(char(10),dateadd(week,-1,dateadd(day,-1*(datepart(weekday,@dtDate)-1),@dtDate)),101)

--WEEK END DATE

select convert(char(10),dateadd(day,-1*(datepart(weekday,@dtDate)-1),@dtDate),101)

MONTH START and END DATE

declare @dtDate as datetime

set @dtDate= getDate()

--MONTH START DATE

select convert(char(10),dateadd(month,-1,dateadd(day,-1*datepart(day,@dtDate)+1,@dtDate)),101)

--MONTH END DATE

select convert(char(10),dateadd(day,-1*datepart(day,@dtDate),@dtDate),101)

or
select DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, 0)-1 AS MonthEndDate

Date Dimension

Below is the script to create Date Dimension.

USE [DatabaseName]
GO

IF OBJECT_ID('Date','U') IS NOT NULL
DROP TABLE Date
GO


CREATE TABLE [dbo].[Date](
   [DateSK] [int] NOT NULL,
   [FullDate] [datetime] NOT NULL,
   [DateName] [char](11) NOT NULL,
   [DayOfWeek] [tinyint] NOT NULL,
   [DayNameOfWeek] [char](10) NOT NULL,
   [DayOfMonth] [tinyint] NOT NULL,
   [DayOfYear] [smallint] NOT NULL,
   [WeekdayWeekend] [char](7) NOT NULL,
   [WeekOfYear] [tinyint] NOT NULL,
   [MonthName] [char](10) NOT NULL,
   [MonthOfYear] [tinyint] NOT NULL,
   [CalendarQuarter] [tinyint] NOT NULL,
   [CalendarYear] [smallint] NOT NULL,
   [CalendarYearMonth] [char](7) NOT NULL,
   [CalendarYearQtr] [char](15) NOT NULL,
CONSTRAINT PK_Date_DateID PRIMARY KEY (DateSK)
) ON [PRIMARY]
GO
RAISERROR('Table Date created successfully!',0,1)

DECLARE @StartDate datetime, @EndDate datetime
-- Set StartDate and EndDate as per your requirement
SELECT @StartDate = '2009-01-01', @EndDate = '2010-12-31'

WHILE (@StartDate <= @EndDate )
BEGIN
INSERT INTO Date
SELECT
   CAST(CONVERT(varchar(8),@StartDate,112) AS int) DateSK
   ,@StartDate AS [Date]
   ,CONVERT(varchar(20),@StartDate,106) AS DateName
   ,DATEPART(DW,@StartDate)   [DayOfWeek]
   ,DATENAME(DW,@StartDate) [DayNameOfWeek]
   ,DATENAME(DD,@StartDate)  [DayOfMonth]
   ,DATENAME(DY,@StartDate)  [DayOfYear]
   ,CASE WHEN DATEPART(DW,@StartDate) IN (1,7) THEN 'WeekEnd'
             ELSE 'WeekDay' END     [WeekdayWeekend]
   ,DATEPART(WW,@StartDate) [WeekOfYear]
   ,DATENAME(MM ,@StartDate) [MonthName]
   ,DATEPART(MM ,@StartDate)   [MonthOfYear]
   ,DATEPART(QQ,@StartDate)     [CalendarQuarter]
   ,DATEPART(YY ,@StartDate)    [CalendarYear]
   ,DATENAME(YY,@StartDate)+'-'+RIGHT('0'+CAST(Month(@StartDate) as varchar),2)  [CalendarYearMonth]
   ,DATENAME(YY,@StartDate)+'-Q'+DATENAME(QQ,@StartDate) [CalendarYearQtr]

   SET @StartDate = @StartDate +1
END
GO

Time Dimension

Some times we need to drill down the Cube data by time hierarchy
e.g. AM/PM-->Hrs-->Min. Below script will create Time dimension and solve the purpose.

USE [DatabaseName]
GO

IF OBJECT_ID('DimTime') IS NOT NULL
DROP TABLE DimTime
GO


CREATE TABLE [dbo].[DimTime]
(
   [DimTimeSK] [int] NOT NULL,
   [Time] [varchar](11) NOT NULL,
   [Time24] [varchar](8) NOT NULL,
   [HourName] [varchar](5),
   [MinuteName] [varchar](8),
   [Hour] [tinyint],
   [Hour24] [tinyint],
   [Minute] [tinyint],
   [Second] [int],
   [AM] [char](2)
) ON [PRIMARY]

GO


DECLARE
   @DimTimeSK int,@Date datetime, @AM char(2),
   @hour24 tinyint, @hour tinyint,
   @minute tinyint, @second int

SET @DimTimeSK = 0

WHILE @DimTimeSK < (60*60*24)
BEGIN
   SET @DimTimeSK = @DimTimeSK + 1
   SET @Date = DATEADD(second,@DimTimeSK,convert(datetime, '1/1/2007'))
   SET @AM = right(convert(varchar,@Date,109),2)
   SET @hour24 = DATEPART(hour, @Date)
   SET @hour = CASE WHEN @AM = 'PM' THEN @hour24 - 12 ELSE @hour24 END
   SET @minute = DATEPART(minute, @Date)
   SET @second = DATEPART(second, @Date)

   INSERT INTO dbo.DimTime
   (
      [DimTimeSK]
      ,[Time]
      ,[Time24]
      ,[HourName]
      ,[MinuteName]
      ,[Hour]
      ,[Hour24]
      ,[Minute]
      ,[Second]
      ,[AM]
   )

   SELECT
   @DimTimeSK AS [DimTimeSK]
   ,right('0'+ convert(varchar,@hour),2) + ':' +
   right('0'+ convert(varchar,@minute),2) + ':' +
   right('0'+ convert(varchar,@second),2) + ' ' + @AM AS [Time]
   ,convert(varchar,@Date,108) [Time24]
   ,right('0' + convert(varchar,@hour),2) + ' ' + @AM AS [HourName]
   ,right('0' + convert(varchar,@hour),2) + ':' +
   right('0' + convert(varchar,@minute),2)+ ' ' + @AM AS [MinuteName]
   ,@hour AS [Hour]
   ,@hour24 AS [Hour24]
   ,@minute AS [Minute]
   ,@second AS [Second]
   ,@AM AS [AM]
END
GO