Get List of Stopped SQL Services from central server with WMI

Get List of Stopped SQL Services from central server with WMI.
As a DBA we have to monitor many things. To monitor all SQL Server Serives wotking properly works fine we have to 
check status of services periodically.
We can do that it manually or automatically, Manually - By using Services.msc, Automatically - By using scripting and programming.
I have created a WMI Script which is very helpfull to DBA Monitoring Task.

Benifits of using WMI.
1) WMI gives detail information without affecting performance.
2) WMI is applicable to handle from central server, you can get information from all remote server without any overhead.
For this you just need to create Wnidows user which have WMI Permission. For Permission please see http://nirav.extreme-advice.com/2013/05/15/get-disk-space-usage-from-central-server-with-wmi/
3) Automatically sends mail for which service is stopped.
4) WMI script has a provision to start stopped services automatically.

Get List of Stopped SQL Services from central server with WMI.
Step1 : Open notepad and save below code with "list of stopped services.vbs"
Dim strComputer
Dim objWMIService, objItem, colItems
Dim strDriveType,txt,flg
Dim arrylist(4)

arrylist(0) = ("Server1")
arrylist(1) = ("Server2")
arrylist(2) = ("Server3")
arrylist(3) = ("Server4")
arrylist(4) = ("Server5")

txt = "<table cellspacing='0' cellpadding='0' border='0' bordercolor='black' id='shell' colspace ='2'>"
txt = txt & "<tr bgcolor='#DDDDDD' height=30>"
txt = txt & "<th width='50' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'>Server</th>"
txt = txt & "<th width='100' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'>Service</th>"
txt = txt & "<th width='50' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'>Status</th>"
txt = txt & "</tr>"

For Each strComputer in arrylist
On Error Resume Next

Set objWMIService = Nothing
Set colItems = Nothing
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_Service Where DisplayName like'%sql%' and AcceptStop = False")
    If Err.Number = 0 Then
	    For Each objItem in colItems
            txt = txt & "<tr Bordercolor='#000000'>"
            txt = txt & "<td align = 'Left' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'><font size='2'>"& strComputer &"</Font></td>"
            txt = txt & "<td align = 'Left' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'><font size='2'>"& objItem.DisplayName &"</Font></td>"
            txt = txt & "<td align = 'Left' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'><font size='2'>"& objItem.State &"</Font></td>"
            txt = txt & "</tr>"
            Set objWMIService = Nothing
            Set colItems = Nothing
        Next
    End If
Next
txt = txt & "</table>"

    Set objMessage = CreateObject("CDO.Message")
    objMessage.Subject = "List of stopped sql services"
    objMessage.From = "Alerts@Sender.com"
    objMessage.To = "niravgajjar@Receiver.com"
    objMessage.htmlbody = txt
    objMessage.Send

Step2 : Create one Schedule task and call this VBS file periodically like Daily ones or twice as need of process. You will get mail like below.

Server Service Status
Server1 SQL Active Directory Helper Service Stopped
Server2 SQL Active Directory Helper Service Stopped
Server2 SQL Server Agent (MSSQLSERVER) Stopped
Server3 SQL Active Directory Helper Service Stopped
Server3 SQL Server Agent (SQL2K8R2EXP) Stopped
Server4 SQL Server Distributed Replay Client Stopped
Server4 SQL Server Distributed Replay Controller Stopped
Server4 SQL Server Agent (SQL2K12EXPR) Stopped
Server5 SQL Server FullText Search (MSSQLSERVER) Stopped
Server5 SQL Server Active Directory Helper Stopped
 Step3 : This is an additional step to make changes in script.
if you need all stopped services then change query.
Select * from Win32_Service Where AcceptStop = False

if you need all SQL services then change query.

Select * from Win32_Service Where DisplayName like'%sql%'

if you need all services.

Select * from Win32_Service

Get Disk space usage from central server with WMI

Get Disk space usage from central server with WMI.

As a DBA job we have to check server health regularly. sometime that happens when you are working on multiple servers and its very difficult to monitor health of all servers. I faced the same issue last week. My application abnormally going down and i am unable to connect database, while i had tried to connect that particular server i found there is only 20KB free space in data drive.

It happens sometime that you don’t know how fast drive is filling and adversity occurs due to low disk space.
I have created one wmi application that observes disk space and mail us on your defined email id’s. This application will handles all server’s Disk usage from central server. For that you need to create one common user with WMI permission.
Please follow the below steps to manage disk space usage from all servers.

Step1 : All of your server must be enable for remote WMI connection.

Click on Start –> Run –> WMIMGMT.MSC

WMI1

Just right click on WMI Control ans select property.

On “WMI Control Property” go to “Security” tab and select “Root” click on Security button which will pop one more window for “Security for root”.

On “Security for Root” window mark on Remote enable for your “WMIUser” or “Everyone”.

WMI2

Step2 : Open Notepad and copy below WMI Code to notepad and save with “DiskUsageWithWMi.csv”.

Sub SendMail(Sender, Recipient, Subject, Message)
   Set objMessage = CreateObject("CDO.Message")
   objMessage.Subject = Subject
   objMessage.From = Sender
   objMessage.To = Recipient
   objMessage.htmlbody = Message
   objMessage.Send
End Sub

Dim strComputer
Dim objWMIService, objItem, colItems
Dim strDriveType,txt
Dim arrylist(4)
Dim pctFreeSpace,strFreeSpace,strusedSpace,strDiskSize

arrylist(0) = ("Server1")
arrylist(0) = ("Server2")
arrylist(0) = ("Server3")
arrylist(0) = ("Server4")
arrylist(0) = ("Server5")

txt = "<table cellspacing='1' cellpadding='0' border='0' bordercolor='black' id='shell'>"
txt = txt & "<tr bgcolor='#DDDDDD' height=30>"
txt = txt & "<th width='50' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'>Drive</th>"
txt = txt & "<th width='75' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'>Size(GB)</th>"
txt = txt & "<th width='75' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'>Used(GB)</th>"
txt = txt & "<th width='75' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'>Free(GB)</th>"
txt = txt & "<th width='75' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'>Free(%)</th>"
txt = txt & "</tr>"

For Each strComputer in arrylist
'On Error Resume Next

   Set objWMIService = Nothing
   Set colItems = Nothing

   Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
   Set colItems = objWMIService.ExecQuery("Select * from Win32_LogicalDisk WHERE DriveType=3 or DriveType=4")

   txt = txt & "<tr bgcolor='#DDDDDD'Bordercolor='#000000'><td colspan='5' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'><font size='2'>"& strComputer & "</font></td></tr>"

   For Each objItem in colItems

      pctFreeSpace = FormatNumber(((objItem.FreeSpace / objItem.Size) * 1000)/10,2)
      strDiskSize = FormatNumber(objItem.Size /1073741824,3)
      strFreeSpace = FormatNumber(objItem.FreeSpace /1073741824,3)
      strUsedSpace = objItem.Size-objItem.FreeSpace
      strUsedSpace = FormatNumber(strUsedSpace/1073741824,3)
      IF pctFreeSpace <= 10 Then
         txt = txt & "<tr bgcolor='#9C3333' Bordercolor='#000000'>"
      Else
         txt = txt & "<tr Bordercolor='#000000'>"
      End If
      txt = txt & "<td style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'><font size='2'>" & objItem.Name & "</Font></td>"
      txt = txt & "<td align = 'Right' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'><font size='2'>"& strDiskSize &"</Font></td>"
      txt = txt & "<td align = 'Right' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'><font size='2'>"& strUsedSpace &"</Font></td>"
      txt = txt & "<td align = 'Right' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'><font size='2'>"& strFreeSpace &"</Font></td>"
      txt = txt & "<td align = 'Right' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'><font size='2'>"& pctFreeSpace &"</Font></td>"
      txt = txt & "</tr>"
      pctFreeSpace = 0
      strDiskSize = 0
      strFreeSpace = 0
      strUsedSpace = 0
      strUsedSpace = 0
      Set objWMIService = Nothing
      Set colItems = Nothing
   Next
Next
txt = txt & "</table>"
SendMail "emailid@sender.com","emailid.reciever.com", strComputer & "Disk Space Information was executed at " & Now(), txt

Step3 : You can create a schedule task which calls this VBS file daily on your required duration like every hour or every 3 hour or daily one time etc.

This code will send you a message like this.

Drive Size(GB) Used(GB) Free(GB) Free(%)
Server1
C: 465.150 213.078 252.073 54.19
D: 465.247 340.871 124.376 26.73
E: 465.247 425.474 39.773 8.55
F: 465.247 328.124 137.123 29.47
L: 169.853 125.357 44.496 26.20
P: 749.997 664.429 85.568 11.41
T: 169.991 72.388 97.603 57.42
Server2
C: 20.407 15.690 4.717 23.12
E: 54.052 35.063 18.988 35.13
F: 465.759 279.674 186.085 39.95
Server3
C: 22.399 20.217 2.182 9.74
D: 67.750 37.477 30.273 44.68
E: 45.281 37.560 7.721 17.05
G: 67.748 53.881 13.868 20.47
H: 272.243 244.077 28.166 10.35
W: 5,527.029 4,654.987 872.042 15.78
Server4
C: 20.216 18.012 2.204 10.90
E: 115.243 58.476 56.767 49.26

Some of them showing in red color which means that drive has lower than 10 % disk space.

Regards,

Nirav Gajjar

Log Shipping with SQL Server 2012

Log Shipping with SQL Server 2012.

SQL Server provides many ways for disaster recovery and log shipping is one of them.

You can configure log shipping in three topology.

1). Configure log shipping on one instance with different database name.

2). Configure log shipping on different instance in same server.

3). Configure log shipping on different server.

Log shipping engine just restore only transaction logs. the procedure is to restore one full backup of original database with no recovery and one transaction backup with also norecovery mode.

Please follow the below steps to configure Log shipping on your machine.

Step1 :  Creat sample database.

Use Master
Go
Create Database MyDatabase

Step2 : You must have your database full recovery mode.

Use Master
Go
ALTER DATABASE [MyDatabase] SET RECOVERY FULL WITH NO_WAIT

Step3 : Take one full backup and one transaction backup of your primary database.

Use Master
Go
Backup Database MyDatabase to Disk = 'D:\MyDatabase_Full.bak'
Backup log MyDatabase to Disk = 'D:\MyDatabase_Tran.trn'

Step4 : Restore full backup and then transactiosnal baackup.

Use Master
Go
RESTORE DATABASE [MyDatabase_LogShipping] FROM  DISK = N'D:\MyDatabase_Full.bak' WITH  FILE = 1,
MOVE N'MyDatabase' TO N'C:\SQL Server\Data\MyDatabase_LogShiiping.mdf',
MOVE N'MyDatabase_log' TO N'C:\SQL Server\Data\MyDatabase_LogShipping_log.ldf',
NORECOVERY,  NOUNLOAD,  STATS = 5

RESTORE Log [MyDatabase_LogShipping] FROM  DISK = N'D:\MyDatabase_Tran.trn'
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

Step5 : Now right click on MyDatabase and click on property and go to “Option” pane.
Check on “Enable this as a priamary database in a log shipping configuration”
and click on “Backup Setting” button.
LogShipping1

Step6 : Please create one sharing folder with full rights and that path in Network path to backup folder.

There is a default schedule of taking backup every 15 minutes, you can change it.

LogShipping2

Click “OK” to complete transaction log backup setting window.

Step7 : Click on “ADD” button for Secondary databases in Log shipping property.

Click on connect to connect your log shipping server and database and move to Copy files tab.

LogShipping3

Step8 : Create one folder to copy files from primary server with full permission.LogShipping4

Step9 : Please select your log shipping database where you want to restore your log on Secondary database.

There is two option to restore backup.

1).  No recovery mode – you can’t use log shipped database in this mode, but it gives better performance.

2) Standby mode – you can use log shipped database for only select statement, you can’t change data you can just read data. log shipping is stopped working when any user reading data from log shipping database, so this mode will slow down the performance.

LogShipping5

Click “OK” button to complete your log shipping.

Step10 : To Recovery Log shipping database.

Use Master
Go
Restore database MyDatabase_logshipping with Recovery

now, log shipping database is ready to use.

Regards,

Nirav Gajjar

Schedule SSIS Package Without Deploying

Schedule SSIS Package Without Deploying.

You can schedule your package before you completely deploy it. you just need to build your SSIS Package and you will get executable “.dtsx” file.

Path for “.dtsx” file is different according to SQL Server versions.

For SQL Serve 2008 :  “your package path \bin\*.dtsx”

For SQL Serve 2012 :  “your package path \bin\Development\*.dtsx”

There are two ways to schedule SSIS Package if you have created package through “File System”.

1) SQL Server Agent Job.

You need to create new job in your SQL Server.

Step1 : Connect your local sql server –> expand SQL Server Agent –> right click on job and click on new job.

SSIS1

Step2 : Give “Call Package” to your job name.

SSIS2

Step3 : Click on “New” button on Steps tab.

SSIS3

Step4 : On New Job Step – Select SQL Server Integration Package in TYPE and File System in Package Source and give “.dtsx” file path.

SSIS4

Step5 : Schedule your package as you convenient time.

SSIS5

2) Windows Schedule Task.

You can’t call “.dtsx” file directly from Schedule task, you must need to create batch file which calls “.dtsx” file and letter you can create schedule task for that batch file.

Step1 : Create batch file with following code.


"C:\Program Files\Microsoft SQL Server\110\DTS\Binn\dtexec.exe" /FILE "D:\Work\SSIS\Nirav's Diary\Nirav's Diary\bin\Development\GetSpaceInfoWithWMI.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF  /REPORTING EWCDI

save it to “D:\Work\SSIS\BatchFiles\GetSpaceInfoWithWMI.bat”

Step2 : Create Windows Schedule Task.

Click on Start button — > Run –> and Type — > control schedtasks and click ok.

Step3 : Click on Task Scheduler Library on left pane of Task Scheduler, it will show a window to create new task.

Now on the right side of Task Scheduler –> click on Create Task under Actions pane. It will open one pop menu to create new task.

Give schedule name  and select option “Run whether user is logged in or not”.

SSIS6

Step4 :  Select Trigger tab from Create Task window –> click on new to schedule time.

SSIS7

Step5 : Select Action tab from Create Task window –> click on browse to map file path.

SSIS8

Click on “OK” button to complete it. windows will ask for user name and password.

Please apply administrator user and password or any other user which have a permission to execute schedule task.

Regards,

Nirav Gajjar

SSIS Package to generate MailProfile script

SSIS Package to generate MailProfile script.

There is no provision to generate script for mail profile in sql server. but i have one script that can be used to create mail profile, mail account and map them together.

I am using this script whenever i needed it, letter i have an idea to start scripting automatically of mail profile and i have created one package that genrates all three scripts and store it in one sql file. this is very helpfull for disaster recovery planning for script backup.

Step1 : To create package you need one Data Flow Task, One Flat File Connection, One OLEDB Connection, and one variable
Please see in figure.

BackupMailProfileScript 1

Step2 : Drag one OLE DB Source and one Flat File Destination. Right click on Data Flow Task –> Edit

BackupMailProfileScript 2

Step3 : Right click on OLEDB Source Editor — > Edit

Select your local machine connection.

Select SQL Command in Data Access mode.

Paste Below Code.


Select '--Create Account' AS CMD
Union All
SELECT 'EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = ''' + m.name + ''',
@description = ''' + m.description + ''',
@email_address = ''' + m.email_address + ''',
@replyto_address = ''' + m.replyto_address + ''',
@display_name = ''' + m.display_name + ''',
@mailserver_name = ''' + s.servername + ''',
@mailserver_type = ''' + s.servertype + ''',
@port = ''' + cast(s.port as nvarchar) + ''',
@username = ''' + isnull(c.credential_identity,0) + ''',
@password = ''x'',
@use_default_credentials = 0,
@enable_ssl = 0' AS CMD
From msdb.dbo.sysmail_account m
LEFT OUTER JOIN msdb.dbo.sysmail_server s
ON m.account_id = s.account_id
LEFT OUTER JOIN master.sys.credentials c
ON s.credential_id = c.credential_id
Union All
Select '--Create Profile' AS CMD
Union All
SELECT '
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = ''' + name + ''',
@description = ''' + isnull(description,'NULL')+ '''
' AS CMD
from msdb.dbo.sysmail_profile
Union All
Select '--Create Link For Profile to Account' AS CMD
Union All
Select '
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = '''+ a.name +''',
@account_name = ''' + b.name + ''',
@sequence_number = 1 ' as CMD
from msdb.dbo.sysmail_profile as a
join msdb.dbo.sysmail_account as b on a.name = b.name

BackupMailProfileScript 3

Step4 : Click on Columns left side on OLE DB Source editor, configure column and click ok.

BackupMailProfileScript 4

Step5 : Right Click on Flat File Destination and select “Primary_Location” to flat file connection manager.

BackupMailProfileScript 5

Click on Mappings to map variable.

BackupMailProfileScript 6

Step6 : Now to make folder dynamic – select Primary_location from connection manager and hit F4.

BackupMailProfileScript 7

Click on Expression button.

BackupMailProfileScript 8
Copy below code to Expression.

@[User::FolderName]  + "<a href="file://mailprofile/">\\MailProfile</a>_" +  (DT_WSTR,4)YEAR(GETDATE())
+ RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2)
+ RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2) + ".sql"

Click OK and execute the package it will creates script to “D:\ScriptBackup\MailProfile\MailProfile_YYYYMMDD.Sql” this file contains three different bunch of script. Fist to create Mail Account Second to create Mail Profile. Third to map Mail Account to Mail Profile.

Regards,

Nirav Gajjar

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