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