November 29, 2007 at 11:16 am
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
November 29, 2007 at 3:21 pm
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
Tommy
Follow @sqlscribeViewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply