Tuesday, September 27, 2011

SSIS Script Database objects:

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.

Imports System
Imports System.Data
Imports System.Math
Imports System.IO
' compile with:
' /r:Microsoft.SqlServer.Smo.dll
' /r:Microsoft.SqlServer.ConnectionInfo.dll
' /r:Microsoft.SqlServer.Management.Sdk.Sfc.dll
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
Imports System.Collections.Specialized
Imports Microsoft.SqlServer.Management.Sdk.Sfc


_
_
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum
  

    ' The execution engine calls this method when the task executes.
    ' To access the object model, use the Dts property. Connections, variables, events,
    ' and logging features are available as members of the Dts property as shown in the following examples.
    '
    ' To reference a variable, call Dts.Variables("MyCaseSensitiveVariableName").Value
    ' To post a log entry, call Dts.Log("This is my log text", 999, Nothing)
    ' To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, True)
    '
    ' To use the connections collection use something like the following:
    ' ConnectionManager cm = Dts.Connections.Add("OLEDB")
    ' cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"
    '
    ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    '
    ' To open Help, press F1.

    Public Sub Main()
        '
        ' Add your code here
        '
        CreateDBScript()
        Dts.TaskResult = ScriptResults.Success
    End Sub

    Public Sub CreateDBScript()
        ' Connect to the local, default instance of SQL Server.
        Dim srv As New Server(Dts.Variables("DBInstanceName").Value.ToString)

        'Get DB's to script
        Dim scDB() As String
        scDB = Split(Dts.Variables("DBToScript").Value.ToString, ";")
        'MsgBox(Dts.Variables("DBToScript").Value.ToString)
        'Open the Script File
        Dim scrDB As New StreamWriter(Dts.Variables("ScriptOutputFileName").Value.ToString, False)

        'Get all the databases
        Dim scrp As New Scripter(srv)
        scrDB.WriteLine("-- Scripting databases for  " + Dts.Variables("DBInstanceName").Value.ToString)

        For Each dbn As Database In srv.Databases
            If dbn.IsSystemObject = False Then
                Dim sc As System.Collections.Specialized.StringCollection = dbn.Script
                For Each st As String In sc
                    scrDB.WriteLine(st)
                Next
                scrDB.WriteLine("--")
                scrDB.WriteLine("")
            End If
        Next




        'Loop the array
        For i As Integer = scDB.GetLowerBound(0) To scDB.GetUpperBound(0)

            ' database name
            Dim dbName As [String] = scDB(i)  ' database name

            ' Reference the database.
            Dim db As Database = srv.Databases(dbName)

            ' Define a Scripter object and set the required scripting options.

            scrp.Options.ScriptDrops = False
            scrp.Options.WithDependencies = True
            scrp.Options.Indexes = True   ' To include indexes
            scrp.Options.DriAllConstraints = True   ' to include referential constraints in the script
            scrp.Options.SchemaQualify = True
            scrp.Options.ExtendedProperties = True

            scrDB.WriteLine("-- Scripting schemas for  " + dbName)

            For Each sch As Schema In db.Schemas

                If sch.IsSystemObject = False Then
                    ' Generating script for schema sch
                    Dim sc As System.Collections.Specialized.StringCollection = sch.Script
                    For Each st As String In sc
                        scrDB.WriteLine(st)
                    Next

                    scrDB.WriteLine("--")
                    scrDB.WriteLine("")
                End If
            Next

            scrp.Options.WithDependencies = True

            ' Iterate through the tables in database and script each one. Display the script.
            For Each tb As Table In db.Tables
                ' check if the table is not a system table
                If tb.IsSystemObject = False Then
                    scrDB.WriteLine("-- Scripting for table " + tb.Name)

                    ' Generating script for table tb
                    Dim sc As System.Collections.Specialized.StringCollection = scrp.Script(New Urn() {tb.Urn})
                    For Each st As String In sc
                        scrDB.WriteLine(st)
                    Next
                    scrDB.WriteLine("--")
                    scrDB.WriteLine("")


                End If
            Next
            scrp.Options.WithDependencies = False
            ' Iterate through the views in database and script each one. Display the script.
            For Each vw As View In db.Views
                ' check if the table is not a system table
                If vw.IsSystemObject = False Then
                    scrDB.WriteLine("-- Scripting for view " + vw.Name)

                    ' Generating script for table tb
                    Dim sc As System.Collections.Specialized.StringCollection = scrp.Script(New Urn() {vw.Urn})
                    For Each st As String In sc
                        scrDB.WriteLine(st)
                    Next
                    scrDB.WriteLine("--")
                    scrDB.WriteLine("")
                End If
            Next

        Next i
        scrDB.Close()
    End Sub

End Class