[SSIS] Automatisation génération script SQL de création de base en C#

Cet article explique comment générer un fichier .sql de création de base (schémas, jobs, tables, vues, procédures stockées) via un package SSIS

Voici un exemple de mise en place (il existe beaucoup d'autres façons de le faire)

1. Les variables contenant respectivement
• database_name: le nom de la base à scripter
• root_folder: le chemin vers le dossier où sera stocké le .sql généré
• Script_Folder: la concaténation de la variable root_folder et database_name
2. L'expression évaluée
3. La valeur de l'expression
4. Les composants SSIS
• Un composant file système qui videra le contenu du dossier
• Le composant C# qui générera le script

Voici le code C# (avec les variables database_name et Script_Folder en readOnly en entrée)

//DLLs

using System.Collections.Specialized;

using System.IO;

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Smo.Agent;

using Microsoft.SqlServer.Management.Sdk.Sfc;

using Microsoft.SqlServer.Management.Common;

using System.Text;

using System.Collections.Generic;

public void Main()

        {

            //Passage des paramètres

            string dbname = Dts.Variables["database_Name"].Value.ToString();

            string Scipt_Folder = Dts.Variables["Script_Folder"].Value.ToString();

            // Connection au serveur localhost et à la base de donnée

            Server srv = new Server();

            Database db = srv.Databases[dbname.ToString()];

            //Définition des options du script

            Scripter scrp = new Scripter(srv);

            scrp.Options.ScriptDrops = false;

            scrp.Options.WithDependencies = true;

            scrp.Options.Indexes = true;

            scrp.Options.DriAllConstraints = true; //Pour inclure les contraintes

            scrp.Options.Triggers = true;

            scrp.Options.FullTextIndexes = true;

            scrp.Options.NoCollation = false;

            scrp.Options.Bindings = true;

            scrp.Options.IncludeIfNotExists = false;

            scrp.Options.ScriptBatchTerminator = true;

            scrp.Options.ExtendedProperties = true;

            scrp.PrefetchObjects = true;

            /*Remarque: Les schemas et les jobs étant stockés au niveau de l'instance et non

              au niveau de la base j'ai utilisé deux techniques différentes pour   

              récupérer les infos.

            */

            //****TECHNIQUE 1 pour les jobs et les schémas******

            // Boucle des jobs

            StringCollection strCol = new StringCollection();

            ScriptingOptions scriptOpt = new ScriptingOptions();

            scriptOpt.IncludeDatabaseContext = false;

            string script = "";

            string JobName;

           

            foreach (Job J in srv.JobServer.Jobs)

            {

                JobName = J.Name.ToString();

                strCol = J.Script(scriptOpt);

                //concatenation des jobs

                foreach (string s in strCol)

                {

                    script += s + "\nGO\n";

                }

            }

            // Boucle des schemas

            foreach (Schema S in db.Schemas)

            {

                if (S.IsSystemObject == false)

                {

                    JobName = S.Name.ToString();

                    strCol = S.Script(scriptOpt);

                   

                    //concatenation des jobs

                    foreach (string s in strCol)

                    {

                        script += s + "\nGO\n";

                    }

                }

            }

            //****TECHNIQUE 2 pour les tables, vues, PS******

            var urns = new List<Urn>();

            // Boucle des tables

            foreach (Table tb in db.Tables)

            {

                // Exclusion des tables système

                if (tb.IsSystemObject == false)

                {

                    urns.Add(tb.Urn);

                }

            }

            // Boucle des vues

            foreach (Microsoft.SqlServer.Management.Smo.View view in db.Views)

            {

                // Exclusion des vues système

                if (view.IsSystemObject == false)

                {

                    urns.Add(view.Urn);

                }

            }

            // Boucle des procédures stockées

            foreach (StoredProcedure sp in db.StoredProcedures)

            {

                // Exclusion des procédures stockées système

                if (sp.IsSystemObject == false)

                {

                    urns.Add(sp.Urn);

                }

            }

            StringBuilder builder = new StringBuilder();

            System.Collections.Specialized.StringCollection sc =scrp.Script(urns.ToArray());

           

            //Ajout de la technique 1 dans le builder

            builder.AppendLine(script);

            //Concaténation des instructions de la techniques 2 avec la technique 1

            foreach (string st in sc)

            {

                builder.AppendLine(st);

                builder.AppendLine("GO");

            }

            //Ecriture du fichier .sql

            TextWriter tw = new StreamWriter(Scipt_Folder.ToString() + ".sql");

            tw.Write(builder.ToString());

            tw.Close();

        }

Source: 
http://blog.extreme-advice.com/2013/01/15/script-all-sql-server-job-automatically-by-ssis-and-smo/