Create tables schema to another database using SMO

Create tables schema to another database using SMO.

I have created a package which will create table script to your physical path and also executes same thing on another database to create table schema.

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Collector;
using Microsoft.SqlServer.Management.Common;
using System.Collections.Specialized;
using System.Collections.Generic;
#endregion

namespace ST_ab378c7f30204c94a2f49a2b0f7ac500
{
	[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
	public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
	{
		public void Main()
		{
            try
            {
                string TableName;
                TableName = (Dts.Variables["User::TableName"].Value.ToString());
                Server server = new Server();
                Database source_database = new Database();
                Database destination_database = new Database();
                server.ConnectionContext.LoginSecure = false;
                server.ConnectionContext.Login = "sa";
                server.ConnectionContext.Password = "reliancesp";
                server.ConnectionContext.ServerInstance = "User8\\SQL";
                source_database = server.Databases["Adventureworks2012"];
                destination_database = server.Databases["ReceiverDB"];

                Table table = source_database.Tables[TableName];
                string tables = table.Name.ToString();

                //Define properties in scriptingOptions
                ScriptingOptions scriptingOptions = new ScriptingOptions();
                scriptingOptions.ClusteredIndexes = true;
                scriptingOptions.NonClusteredIndexes = true;
                scriptingOptions.Default = true;
                scriptingOptions.DriAll = true;
                scriptingOptions.Indexes = true;
                scriptingOptions.IncludeHeaders = true;
                scriptingOptions.ScriptSchema = true;
                scriptingOptions.ScriptDrops = false;
                scriptingOptions.Triggers = true;

                StringCollection result = table.Script(scriptingOptions);

                var script = "";
                foreach (var line in result)
                {
                    script += line + "\r\n";
                }

                System.IO.StreamWriter fs = System.IO.File.CreateText(@"D:\data\" + TableName + ".sql");
                fs.Write(script);
                fs.Close();
                destination_database.ExecuteNonQuery(script);
            }
            catch(Exception e)
            {
                MessageBox.Show(e.Message.ToString());
            }
            Dts.TaskResult = (int)ScriptResults.Success;
		}

        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        ///
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

	}
}

SSIS Package to Backup Jobs, Operators and Alerts Script using SMO

SSIS Package to Backup Jobs Script using SMO.

This package willl use some SMO reference and generates SQL Server Jobs, Operators and Alerts script backup.

I had written the code for SQL Server Jobs only and commented code for Operators backup and Alerts Backup.

You just need to replcace Operators or Alerts instead of Jobs.

I have created SSIS package that automatically creates backup folder for current date and save your all jobs with jobsname.sql separately as well as all jobs in one file with static name AllJobsInOneFile.sql.

Step1 : First of all you have to drag one script task and create variables..

–> ServerName – DataType String.

–> UserName – DataType String.

–> pwd- DataType String.

–> DateTime – DataType String.

–> FolderPath – DataType String.

BackupJobsScript 1

Step2 : Please proper assign ServerName, UserName Pwd and your local machine’s FolderPath.

For DateTime variable click on Expression button and paste below code.

Note : This will create folder with current date letter in script task.

(DT_STR,4,1252)YEAR(GETDATE()) + RIGHT(“0″ + (DT_STR,2,1252)MONTH(GETDATE()),2) + RIGHT(“0″ + (DT_STR,2,1252)DAY(GETDATE()),2)

Replication Script Backup 2

Step3 : Now Right Click on Script task and select all user variables to ReadOnlyvariables.

BackupJobsScript 2

Step4 : Click on Edit Script.

here you need to add refrences for SMO Objects.

To Add Reference

–> Click on project menu –> Select Add Refrence.

–> On the Add Refrence popup select .Net Tab.

–>Select Microsoft.SQLServer.ConnectionInfo

Microsoft.SQLServer.Management.Utility

Microsoft.SQLServer.SMO

Microsoft.SQLServer.ManagedDTS

Microsoft.SQLServer.ScriptTask.

Paste below code to your script task code.


#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.Smo.Agent;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Sdk.Sfc;
using System.IO;
using System.Collections.Specialized;
#endregion

namespace ST_69d1b336488942988b45f8cdd86a3824
{
 [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
 public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
 {
  public void Main()
  {
            StringCollection sc = new StringCollection();
            ScriptingOptions so = new ScriptingOptions();
            so.IncludeDatabaseContext = true;

            string ServerName;
            string UserName;
            string Password;
            string FolderDate;
            string FolderPath;

            ServerName = (string)Dts.Variables["ServerName"].Value;
            UserName = (string)Dts.Variables["UserName"].Value;
            Password = (string)Dts.Variables["pwd"].Value;
            FolderDate = (string)Dts.Variables["DateTime"].Value;
            FolderPath = (string)Dts.Variables["FolderPath"].Value;

            ServerConnection conn = new ServerConnection();
            conn.LoginSecure = false;
            conn.Login = UserName;
            conn.Password = Password;
            conn.ServerInstance = ServerName;
            Server srv = new Server(conn);

            System.IO.Directory.CreateDirectory(FolderPath + "\\" + FolderDate.ToString());
            try
            {
                string script = "";

                string JobName;
                string AllJobsInOneFile = "";
                //Loop over all the jobs
                //To take a script backup of Operators.

                //foreach (Job J in srv.JobServer.Operators)
                //To take a script backup of Alerts.
                //foreach (Job J in srv.JobServer.Alerts)
                //To take a script backup of Jobs.
                foreach (Job J in srv.JobServer.Jobs)
                {

                    //Output name in the console
                    Console.WriteLine(J.Name.ToString());

                    JobName = J.Name.ToString();
                    sc = J.Script(so);

                    //Get all the text for the job
                    foreach (string s in sc)
                    {
                        script += s;
                    }

                    //Generate the file
                    TextWriter tw = new StreamWriter(FolderPath + "\\" + FolderDate.ToString() + "\\" + JobName.Replace(':', '_').ToString() + ".sql");
                    tw.Write(script);
                    tw.Close();
                    TextWriter WriteAllJobs = new StreamWriter(FolderPath + "\\" + FolderDate.ToString() + "\\" + "AllJobsInOneFile.sql", true);
                    WriteAllJobs.Write(script + "\nGO\n");
                    WriteAllJobs.Close();
                    script = "";
                }
            }
            catch (Exception eh)
            {
                //MessageBox.Show(eh.ToString());
            }
   Dts.TaskResult = (int)ScriptResults.Success;
  }

        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        ///
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

 }
}

Note: This script will support only in SQL Server 2012.

Create a job which calls this package and schedule it daily or weekly.

Regards,

Nirav Gajjar

SSIS Package to Backup Replication Script using SMO

SSIS Package to Backup Replication Script.

Hi,

This package willl use some SMO reference and generates SQL Server replication backup script same as you do it manually like, Right click on Publication –> Generate Scripts….

I have created SSIS package that automatically creates backup folder for current date and save your all replication publication script with name “publication name + .sql”.

Step1 : First of all you have to drag one script task and create variables..

–> ServerName – DataType String.

–> UserName – DataType String.

–> pwd- DataType String.

–> DateTime – DataType String.

–> FolderPath – DataType String.

Step2 : Please proper assign ServerName, UserName Pwd and your local machine’s FolderPath.

For DateTime variable click on Expression button and paste below code.

Note : This will create folder with current date letter in script task.

(DT_STR,4,1252)YEAR(GETDATE()) + RIGHT(“0” + (DT_STR,2,1252)MONTH(GETDATE()),2) + RIGHT(“0” + (DT_STR,2,1252)DAY(GETDATE()),2)

Replication Script Backup 2

Step3 :  Now Right Click on Script task and select all user variables to ReadOnlyvariables.

Replication Script Backup 1

Step4 : Click on Edit Script.

here you need to add refrences for SMO Objects.

To Add Reference

–> Click on project menu –> Select Add Refrence.

–> On the Add Refrence popup select .Net Tab.

–>Select Microsoft.SQLServer.ConnectionInfo

Microsoft.SQLServer.Management.Utility

Microsoft.SQLServer.SMO.

Paste below code to your script task code.

#region Namespaces
using System;

using System.Data;

using Microsoft.SqlServer.Dts.Runtime;

using System.Windows.Forms;

using Microsoft.SqlServer.Management.Smo.Agent;

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Common;

using Microsoft.SqlServer.Management.Sdk.Sfc;

using Microsoft.SqlServer.Replication;

using System.IO;

using System.Collections.Specialized;

#endregion</pre>
namespace ST_69d1b336488942988b45f8cdd86a3824
{
 [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
 public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
 {       
  public void Main()
  {
            string ServerName;
            string UserName;
            string Password;
            string FolderDate;
            string FolderPath;

            ServerName = (string)Dts.Variables["ServerName"].Value;
            UserName = (string)Dts.Variables["UserName"].Value;
            Password = (string)Dts.Variables["pwd"].Value;
            FolderDate = (string)Dts.Variables["DateTime"].Value;
            FolderPath = (string)Dts.Variables["FolderPath"].Value;

            ServerConnection conn = new ServerConnection();
            conn.LoginSecure = false;
            conn.Login = UserName;
            conn.Password = Password;
            conn.ServerInstance = ServerName;
            Server srv = new Server(conn);

            System.IO.Directory.CreateDirectory(FolderPath + "\\" +FolderDate.ToString());
            ReplicationServer RS = new ReplicationServer(conn);
            try
            {
                foreach (ReplicationDatabase RD in RS.ReplicationDatabases)
                {

                    if (RD.HasPublications)
                    {
                        foreach (TransPublication TP in RD.TransPublications)
                        {
                            //TextWriter tw = new StreamWriter("D:\\" + TP.Name.ToString() + ".sql");
                            TextWriter tw = new StreamWriter(FolderPath + "\\" + FolderDate.ToString() + "\\" + TP.Name.ToString() + ".sql");
                            tw.Write(TP.Script(ScriptOptions.Creation | ScriptOptions.IncludeAll ^ ScriptOptions.IncludeReplicationJobs));
                            tw.Close();
                        }
                    }
                }
            }
            catch (Exception eh)
            {
                //MessageBox.Show(eh.ToString());
            }
   Dts.TaskResult = (int)ScriptResults.Success;
  }

        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        ///
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

 }
}

Note: This script will support only in SQL Server 2012.

Create a job which calls this package and schedule it daily or weekly.

Regards,

Nirav Gajjar

SSIS Package to get Table Schema Discrepancy using XML

SSIS Package to get Table Schema Discrepancy using XML.

I have created package which uses XML in Execute SQL Task and compares two table’s schema discrepancy.

You can use this script to compare table in different instances with different database or in same instance with difference database or in same instance with same database.

You can save your result in database table in any text file, here i have messaged the result.

I have done this stuff in different instances with different database.

Please flow the step to do so.

Step1 : Create one sample table with one column difference on two difference instnace.

Table script for source connection

CREATE TABLE [dbo].[DatabaseInfo]

(
    [ServerName] [varchar](20) NULL,
    [DatabaseName] [varchar](20) NULL,
    [LogicalName] [varchar](20) NULL,
    [PhysicalName] [varchar](200) NULL,
    [DBSize] [int] NULL)

Table script for destination connection


CREATE TABLE [dbo].[DatabaseInfo]

(
    [ServerName] [varchar](20) NULL,
    [DatabaseName] [varchar](20) NULL,
    [LogicalName] [varchar](20) NULL,
    [DBSize] [int] NULL
)

here, i have deleted column PhysicalName to get an idea.

Step2: Now drag two Execute SQL Task, One Script Task, two OLEDB Connection and two variables.

TableSchemaDifference 1

Variables

Obj_Result –> DataType Object

XMLData –> String

Step3 : Right click on Source Execute SQL Task –> Edit.

–> Select ResultSet –> XML

–>SQL Statement


SELECT
 CLMList.Table_Name,
 CLMList.COLUMN_NAME
FROM Information_Schema.Columns CLMList
Where CLMList.Table_Name = 'DatabaseInfo'
For XML Path('SyncTables'),root('Root')

TableSchemaDifference 2

Step4 : Click on ResultSet –> Edit.

–> Select XMLData variable.

TableSchemaDifference 3

Click OK

Step5 : Right click on Source Execute SQL Task –> Edit.

–> Select ResultSet –> Full Result Set

–>SQL Statement


Declare @TableName xml
set @TableName = ?

SELECT
    SyncTables.TableName,
    SyncTables.ColumnName
FROM Information_Schema.Columns CLMList
    right join (SELECT p.value('(./Table_Name)[1]', 'VARCHAR(8000)') as TableName,
    p.value('(./COLUMN_NAME)[1]', 'VARCHAR(8000)') as ColumnName
FROM @TableName.nodes('//Root/SyncTables') t(p)) as SyncTables
    on CLMList.Table_Name = SyncTables.TableName
    and CLMList.Column_Name = SyncTables.ColumnName
 Where CLMList.Column_Name is null

TableSchemaDifference 4

Step6 : Click on Parameter Mapping  of Execute SQL Task Editor.

Select User::XMLData as input variable.

TableSchemaDifference 5

Step7 : Click on Result Set of Execute SQL Task Editor.

Select User::Obj_Result in Variable Name.

TableSchemaDifference 6

Step8 : Now Right click on Script Task –> Edit.

Select User::Obj_Result as ReadOnlyVariables.

TableSchemaDifference 7

Click on Edit Script and past below code.

</pre>
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Data.OleDb;
#endregion

namespace ST_e81be99c144345b2bb202cbd5460cb01
{
 [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
 public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
 {
  public void Main()
  {
   // TODO: Add your code here
            try
            {
                OleDbDataAdapter OA = new OleDbDataAdapter();
                DataTable DT = new DataTable();
                DataRow DR;

               
                OA.Fill(DT, Dts.Variables["User::Obj_Result"].Value);
                foreach (DataRow row in DT.Rows)
                {
                    DR = row;
                    string TableName, ColumnName;
                    TableName = DR["TableName"].ToString();
                    ColumnName = DR["ColumnName"].ToString();

                    MessageBox.Show("Table Name : " + TableName.ToString() + " Column Name : " + ColumnName.ToString());
                }

                Dts.TaskResult = (int)ScriptResults.Success;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message.ToString());
            }
   Dts.TaskResult = (int)ScriptResults.Success;
  }

        #region ScriptResults declaration
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

 }
}

Now run the package and it will show you column difference in popup message.

Regards,

Nirav Gajjar

SSIS Package to Call Web Service

SSIS Package to Call Web Service.

You can Call WebService from SSIS package and transfers your data.

First of all you have to create web service with function as  you needed to call.

Step1 : Go To Microsoft Visual Studio –> New –> WebSite –> ASP.Net WebService and provide you web service name.

WebService 1

Step2 : Paste below code to your Service1.asmx.vb file.

I have created new function “MyHelloWorld” with parameter name in which i am passing a string to write in a destination file.


Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.ComponentModel

' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
' <System.Web.Script.Services.ScriptService()> _
<System.Web.Services.WebService(Namespace:="<a href="http://tempuri.org/">http://tempuri.org/</a>")> _
<System.Web.Services.WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<ToolboxItem(False)> _
Public Class Service1
    Inherits System.Web.Services.WebService
    <WebMethod()> _
    Public Function MyHelloWorld(ByVal name) As String
        Dim FILE_NAME As String = "E:\WebServiceTextFromService.txt"
        Dim objWriter As New System.IO.StreamWriter(FILE_NAME, True)

        objWriter.Write(name)
        objWriter.Close()
        Return "Process Completed."
    End Function

End Class

Step3 : Now Create New SSIS Package with your desired name.

Drag Weservice Task to Control Flow.

Create one variable “Result” with datatype string and value “I am writting this content to file by web service task”.

WebService 2

Step4 : Create one http connection manager. i am using 61508 port number for my local web service so my Server URL : http://localhost:61508.

This port number is assigned by default, you can change port number from web service property.

WebService 4

Step5 : Create one file connection to write output result to local path.

WebService 8

Step6 :  Right click on Web Service Task –> Edit.

On General Tab Select HTTP Connection Manger which you have created on Step4.

Select your WSDL file path to WSDLFile field.

WebService 5

Step7 : Select Input path of Web Service Task Editor.

Select your service name in Service and Method “MyHelloWorld” which you have created on Step2.

After selecting Method, Parameter field enables automatically.

Click on Variable checkbox and select User::Result in value .

WebService 6

Step8 : Select OutPut tab on left pane on Web Service Task Editor.

Select Flat File Connection “WebServiceText.txt” in File Field.

WebService 7

Step9 : After completing this build and run the project and you will get file will be created on “D:\WebServiceText.txt”

with the content “I am writting this content to file by web service task”.

 

Regards,

Nirav Gajjar

SSIS Package Foreach Loop with File Connection

SSIS Package Foreach Loop with File Connection.

This package gives you an idea to get file details and variable mapping between Foreach Loop Container and any inner task contains in loop (here inner task is Script Task).

Step1 : Drag Foreach Loop and script task from SSIS Toolbox.

ForEachLoopWithFile1

Step2 : Create one variable with name “FileName” and datatype “string”.
this varialbe maps filename from Foreach Loop Container value to Script task.

Step3 : Right click on Foreach Loop Container –> Edit
On Collection pane –> select Foreach File Enumerator in Enumerator field.
choose your folder by clicking on browse button.
–> Select file extention if you want to filter file extention otherwise put *.* to get all files.
–> Select Name and Extention in Retrieve file name.
ForEachLoopWithFile2

Step4 : Select Variable mapping pane on Foreach Loop Editor.
–> Select User::FileName variable.
–> and Index to 0.
ForEachLoopWithFile3

Step5 : Right Click on Script Task –> Edit.
–> Select Script Pane –> Select User::FileName as ReadOnlyVarialbe.

Step6 : Click on Edit Scrit to write script code.

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
#endregion

namespace ST_f1f3822404bc4e8d9a22fbe2749ff1d9
{
	[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
	public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
	{
		public void Main()
		{
            string FileName;
            FileName = Dts.Variables["User::FileName"].Value.ToString();
            MessageBox.Show(FileName);
			Dts.TaskResult = (int)ScriptResults.Success;
		}

        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

	}
}

Regards,
Nirav gajjar

SSIS Package to get Disk Space Info with WMI and Script Task

SSIS Package to get Disk Space Info with WMI and Script Task.

In Previous blog i have created SSIS Package to get Disk Space Info with WMI.(Link: http://nirav.extreme-advice.com/2013/02/20/ssis-package-to-get-disk-space-info-with-wmi/)
That was a simple way to gather disk space information with importing data in CSV file and exporting data in database.
I have used same table in this task and i have used Script Task in behalf of CSV file.

Table Structure.

Use AdventureWorks2012
Go
Create Table ServerDiskInfo
(
Drive varchar(5),
DriveSize numeric(18,2),
FreeSpace numeric(18,2),
CollectionDate Datetime Not NUll Default GetDate()
)

Lets start to create SSIS Package to get Disk Space Info with WMI and Script Task.

Step1 : Open your SSIS package and drag WMI Data Reader Task from SSIS Toolbox.

DiskSpaceInfoWithWMIScript

Step2 : Create new WMI Connection shown as below.

DiskSpaceInfoWithWMIScript2

Step3 : Create variable Obj_Result with data type object to recieve result data.

DiskSpaceInfoWithWMIScript3

Step4 : Select User::Obj_Result in Destination.

DiskSpaceInfoWithWMIScript4 

Step5 : Right click on Script Task –> Edit.

Select User::Obj_Result as ReadOnlyVariables.

DiskSpaceInfoWithWMIScript5

Step5 : Click on Edit on Script Task Editor and paste below code.


public void Main()
 {
            try
            {
                DataTable DT;
                DataRow DR;
                SqlConnection SQLCon = new SqlConnection("Server=Nirav'sDiary;Database=AdventureWorks2012;User Id=sa;Password=pwd;");

                DT = (DataTable)Dts.Variables["User::Obj_Result"].Value;
                foreach (DataRow row in DT.Rows)
                {
                    DR = row;
                    string Name;
                    int Size, FreeSpace;
                    Name = DR["Name"].ToString();
                    Size = Convert.ToInt32(Convert.ToDouble(DR["Size"].ToString()) / 1024 / 1024 / 1024);
                    FreeSpace = Convert.ToInt32(Convert.ToDouble(DR["FreeSpace"].ToString()) /1024/ 1024 / 1024);
                    SqlCommand cmd = new SqlCommand("Insert Into ServerDiskInfo Values('"+ Name +"',"+ Size +","+ FreeSpace +",GetDate())", SQLCon);
                    SQLCon.Open();
                    cmd.ExecuteNonQuery();
                    SQLCon.Close();
                }

                Dts.TaskResult = (int)ScriptResults.Success;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message.ToString());
            }
 }

Step6 : Executing whole task it will make entry in ServerDiskinfo Table.

Query to get ServerDiskInfo.

Declare @Today as datetime
Det @Today = DATEADD(DD,0,DATEDIFF(DD,0,GETDATE()))
Delect Drive,Cast(DriveSize/1024/1024/1024 as Numeric(10,2)) as SizeInGB,Cast(FreeSpace/1024/1024/1024 as Numeric(10,2)) as FreeSpaceInGB from ServerDiskInfo
Where DATEADD(DD,0,DATEDIFF(DD,0,CollectionDate)) = @Today</pre>

Regards,
Nirav gajjar