Create a SSIS package that scripts out all DB objects (1 file per object)

  • We are in the process of trying to automate our production releases (what a concept 😉

    The database is SQL server 2005

    All objects are being stored in VSS

    Using Nant and Cruise Control for the actual migrations.

    I have two directories - Create (for a brandnew database) and Change (db object changes)

    In my 'Change' script, I do the following -

    1 - Take backup of database

    2 - Migrate objects from 'change' directory to production

    3 - Script out all objects of database and save in the 'Create' directory

    For the #3, I was hoping I could create an SSIS package that would

    script out all database objects and save them on the VSS server.

    I'm new to SSIS and want to verify it's something that can be done before I start down that path.

    If anyone has any examples or references, it would be much appreciated.

    Thanks

  • You could take this VBS script below and rewrite into .NET (shouldn't be too difficult),

    put into a SSIS script task (use some variables). If your new to SSIS, just Google

    "Script Tasks SSIS" and you should find a bunch of articles to help get you started.

    Good luck 🙂

    ' Will script all non-system databases on a SQL Server

    ' Args Server = which SQL Server to script

    ' Path = where to write script

    Dim sServer

    Dim sPath

    Dim oFSO

    Dim sLogFile

    Dim oLogFile

    Set oFSO = WScript.CreateObject("Scripting.FileSystemObject")

    sServer = WScript.Arguments(0)

    sPath = WScript.Arguments(1)

    If sServer <> "" and sPath <> "" Then

    If Right(sPath, 1) <> "\" Then

    sPath = sPath & "\"

    End If

    sPath = sPath & Replace(sServer, "\", "_") & "_" & Replace(FormatDateTime(Now(), 2), "/", "") & "\"

    If Not oFSO.FolderExists(sPath) Then

    oFSO.CreateFolder sPath

    End If

    sLogFile = sPath & "log.txt"

    Set oLogFile = oFSO.CreateTextFile(sLogFile, True)

    Log "Starting SQL script generation for " & sServer

    ScriptDB sServer, sPath

    End If

    Set oFSO = Nothing

    Sub ScriptDB(Server, Path)

    Dim oSQLServer

    Dim oDB

    Dim sDBExclude

    Dim nCount

    Dim sDBCheck

    sDBExclude = "|master|model|Northwind|BUGTRACK|msdb|pubs|tempdb|TIME_ATTEND_OLD|ClientWebs_Roosevelt|ClientWebs_Lincoln|ClientWebs_Saved|"

    Log Path

    If Not oFSO.FolderExists(Path) Then

    oFSO.CreateFolder(Path)

    End If

    Set oSQLServer = WScript.CreateObject("SQLDMO.SQLServer")

    oSQLServer.LoginSecure = True

    oSQLServer.Connect Server

    If Not oSQLServer Is Nothing Then

    Log "Connected to: " & oSQLServer.Name & ", DB Count: " & oSQLServer.Databases.Count

    nCount = 0

    For Each oDB In oSQLServer.Databases

    If Not oDB Is Nothing Then

    sDBCheck = "|" & oDB.Name & "|"

    nCount = nCount + 1

    If Instr(1, sDBExclude, sDBCheck, 1) = 0 Then

    Log nCount & " of " & oSQLServer.Databases.Count & ". Scripting " & sServer & "_" & oDB.Name & "_DDL.sql"

    ScriptObjects oDB, Path & oDB.Name & "_DDL.sql"

    Else

    Log nCount & " of " & oSQLServer.Databases.Count & ". Skipping " & oDB.Name

    End If

    End If

    Next

    Set oSQLServer = Nothing

    Else

    Log "Unable to connect to SQL Server: '" & sServer & "'"

    End If

    Log "Finished"

    End Sub

    Sub ScriptObjects(DB, ScriptFile)

    Dim nScriptOptions

    Dim sScript

    Dim oObject

    nObjOptions = 1 Or 4 Or 256 Or 131072 Or 4096 Or 73736 Or 262144 Or 34 Or 64

    nTableOptions = 1 Or 2 Or 4 Or 8 Or 16 Or 64 Or 128 Or 256 Or 1024 Or 4096 Or 8192 Or 73736 Or 131072 Or 262144 Or 532676608

    ' Users

    Log "Users"

    For Each oObject in DB.Users

    If Not oObject.SystemObject Then

    oObject.Script nObjOptions, ScriptFile

    End If

    Next

    ' UDTs

    Log "UDTs"

    For Each oObject in DB.Users

    oObject.Script nObjOptions, ScriptFile

    Next

    ' UDFs

    Log "UDFs"

    For Each oObject in DB.UserDefinedFunctions

    If Not oObject.SystemObject Then

    oObject.Script nObjOptions, ScriptFile

    End If

    Next

    ' Tables

    Log "Tables"

    For Each oObject in DB.Tables

    If Not oObject.SystemObject Then

    oObject.Script nTableOptions, ScriptFile

    End If

    Next

    ' Rules

    Log "Rules"

    For Each oObject in DB.Rules

    oObject.Script nObjOptions, ScriptFile

    Next

    ' Defaults

    Log "Defaults"

    For Each oObject in DB.Defaults

    oObject.Script nObjOptions, ScriptFile

    Next

    ' Views

    Log "Views"

    For Each oObject in DB.Views

    If Not oObject.SystemObject Then

    oObject.Script nTableOptions, ScriptFile

    End If

    Next

    ' Stored Procs

    Log "Sprocs"

    For Each oObject in DB.StoredProcedures

    If Not oObject.SystemObject Then

    oObject.Script nObjOptions, ScriptFile

    End If

    Next

    '

    End Sub

    Sub Log(Message)

    'WScript.Echo Message

    oLogFile.WriteLine Message

    End Sub

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply