Backup SQL Server DB Objects (user defined) as script

using below tool, you can backup a database, with all user defined objects, as a script and save it to the file.... without any third party/extra tool.

NOTE: download the ZIP file "DBScriptGenerator.zip"

After download, extract the zip and place it anywhere you want.
in my case, location is -- "C:\DBScriptGenerator\"

this zip file contains an EXE file named "DBScriptGenerator.exe", along with the other DLL files.

now we have to call this executable from anywhere we can. of course, we need to pass all the parameters.
in my case, I am calling from CMD...

Format :
DBScriptGenerator.exe "DatabaseServerName" "DatabaseName" "DatabaseUser" "DatabasePassword" "FullPathToSaveWithFileName"

Example:
DBScriptGenerator.exe "PRODSV" "EMPMAS" "UADMIN1" "u@Dwin!" "C:\DBScriptGenerator\EMPMAS.sql"

NOTE:::
for Advanced users only ...

There is a DLL file, DBScriptGenerator.dll, available to use programmatically. we can use this DLL from C# classes and/or SSIS scripts also.

Object List: Table, Procedure, Function, View, Trigger, Schema, assembly .. all user objects

Use DLL/Assembly without install in GAC, C# /SSIS script

This post will describe the use of Assembly type DLL without installing it on GAC

Always we have to install an assembly first then we can use the DLL file into C# code. But when we need to develop something portable and need to run from any machine. So, for this case, we have to use an assembly like a normal DLL, means which DLL has no any strong name. But we everybody knows that assembly type DLL has a strong name so that we can’t use it without install.

Furthermore, there are many procedures to use an Assembly or Strong Named DLL as a reference in the C# coding …

·         Remove Strong Name from the DLL

·         Use DLL merger to change the strong name by the new one, every time we need to run the C# code.

But above process is not so easy to implement. But there is another procedure available which is as simple as it says.

·         Load the Assembly DLL file at the runtime of the beginning of the program which needs it to execute.

Yes, it’s so easy to implement and every time c#.NET will load the assembly from the defined location. Follow below points to implement the procedure.

·         Because of we have to load the assembly/DLL at the beginning of the program, we will create a static constructor of the class of first entry point, that means Main Class should have a static constructor.

·         Put the assembly/DLL file on a fix location and mention this location here to load at runtime.

·         And then implement an interface/event handler named ResolveEventHandler to include the missing assemblies from the pre-defined location

Let’s see the coding for that ...

NOTE: you need to change the GREEN parts only

public class assemblyLoadAtRuntime 

{

…..
…..

…..

static string[] Path = { @"D:\temp\" , "AssemblyDLL" } ; // { "Path of the DLL file" , "Name of the DLL file without extension" }

     static assemblyLoadAtRuntime () // static Constractor of your class

     {

         AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(CurrentResolve);

     }

     static System.Reflection.Assembly CurrentResolve (object sender, ResolveEventArgs args)

     {

         if (args.Name.Contains(Path[1]))  // change this code if you have many dll file to load

         {

return System.Reflection.Assembly.LoadFile(Path[0] + Path[1]+".dll");

         }

         return null;

     }

…..
…..

…..

}

Here assemblyLoadAtRuntime is the main class

AssemblyResolve represent the list of assemblies

And finally, assemblyLoadAtRuntime is the interface to implement to load the SN DLL.

This procedure is very much simple but it can solve the problem of assembly use.

Execute SSIS package from SSIS Script/ C#

Execute SSIS package from C# / SSIS script with configuration file and/or preset variable value and get all types of messages/events from that package like information, error, warning etc.

For this execution you have to add below references and use below packages…

  Ø  References (Assembly location “C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\”)

ü  Microsoft.SqlServer.ScriptTask

ü  Microsoft.SqlServer.ManagedDTS

  Ø  Packages

ü  Microsoft.SqlServer.Dts.Runtime

ü  Microsoft.SqlServer.Dts.Tasks.ScriptTask

First of all you have to load a package by using Application object and then set configuration file and/or variables value by using Package. Then add a listener to get messages/events from loaded package.

Example declaration and calling … Download code only (CallingCode.c#)

run.Package pkg;

run.Application app;

pkg = app.LoadPackage(pkgPath + @"\TestCall.dtsx", null);

run.Variables pkgVars = pkg.Variables;

pkgResults = pkg.Execute(null, pkgVars, eventListener, null, null);

Download ExecuteSSISPkgC#.zip for example. This zip contains following three (3) files...

File Name	Purpose	Comment
CallingPkg.dtsx	This package will call the target TestCall.dtsx package	Add congif file (XML), set variables, retrieve event messages to/from TestCall.dtsx
TestCall.dtsx	This package will be executed by the package CallingPkg.dtsx	Will be executed
TestConfig.dtsConfig	Use to set variable “ValueOfConfig”	Used by “TestCall.dtsx”. set by “CallingPkg.dtsx”

see also -- SQL script to check duplicate record existence of a table/SQL statement of SQL Server