SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Package to Script All SQL Server Jobs to Individual Files


SSIS Package to Script All SQL Server Jobs to Individual Files

Author
Message
mike.renwick-894639
mike.renwick-894639
SSC-Enthusiastic
SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)

Group: General Forum Members
Points: 196 Visits: 190
Some similar powershell scripts I cobbled together.

Script jobs and some other server objects I'm interested in:
Note that there should be as many "paths" as there are "output paths" if you add types to this.


$Scripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter") ("(local)")

$Paths = "SQLSERVER:\SQL\localhost\DEFAULT\JobServer\Jobs","SQLSERVER:\SQL\localhost\DEFAULT\Logins","SQLSERVER:\SQL\localhost\DEFAULT\LinkedServers","SQLSERVER:\SQL\localhost\DEFAULT\Mail\Accounts","SQLSERVER:\SQL\localhost\DEFAULT\Mail\Profiles","SQLSERVER:\SQL\localhost\DEFAULT\JobServer\Operators"
$OutputPaths = "Server\Jobs","Server\Logins","Server\LinkedServers","Server\Mail\Accounts","Server\Mail\Profiles","Server\Operators"

$i = 0

do
{
$path = $Paths[$i]
$Outputpath = "Source\SQL\"+$OutputPaths[$i]
new-item -Force -path D:\ -name $Outputpath -type directory
$dirname = "D:\" + $Outputpath
cd $path

foreach ($item in Get-Childitem)
{
if ($item.Name.Substring(0,2) -eq "##")
{
echo $item.Name
continue
}

$Scripter.Options.AnsiPadding=$False
$Scripter.Options.AnsiFile=$True
$Scripter.Options.AppendToFile=$False
$Scripter.Options.Bindings=$False
$Scripter.Options.DriAll=$True
$Scripter.Options.ExtendedProperties=$False
$Scripter.Options.IncludeHeaders=$False
$Scripter.Options.IncludeIfNotExists=$False
$Scripter.Options.IncludeDatabaseRoleMemberships=$True
$Scripter.Options.IncludeDatabaseContext=$True
$Scripter.Options.Permissions=$True
$Scripter.Options.SchemaQualify=$True
$Scripter.Options.ScriptData=$False
$Scripter.Options.ScriptDrops=$False
$Scripter.Options.Statistics=$False
$Scripter.Options.ToFileOnly=$True

$p = "D:\"+$Outputpath+"\"+$item.Name.Replace("\","_").Replace(" ","_")+".sql"

$Scripter.Options.FileName=$p

$Scripter.Script($Item)
}

$i++
}
while ($i -lt $Paths.Length)



Script database objects I'm interested in (including certain specific table bits like indexes and foreign keys)


Param
(
$Database
)

$Scripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter") ("(local)")

$TableSubtypes = "Indexes","Triggers","ForeignKeys"
$ExcludedScriptTypes = "ApplicationRoles","AsymmetricKeys","Certificates","DatabaseAuditSpecifications","Defaults","ExtendedProperties","ExtendedStoredProcedures","FileGroups","LogFiles","SymmetricKeys","ServiceBroker"

$Path = "SQLSERVER:\SQL\localhost\DEFAULT\Databases\"+$Database
cd $Path

foreach ($objectType in Get-ChildItem)
{
if ($ExcludedScriptTypes -contains $objectType)
{
continue
}

$Path = "SQLSERVER:\SQL\localhost\DEFAULT\Databases\"+$Database+"\"+$objectType
cd $Path

$dirname = "Source\SQL\Databases\" + $Database + "\" + $objectType
new-item -Force -path D:\ -name $dirname -type directory

foreach ($Item in Get-ChildItem)
{
$Scripter.Options.AnsiPadding=$False
$Scripter.Options.AnsiFile=$True
$Scripter.Options.AppendToFile=$False
$Scripter.Options.Bindings=$False
$Scripter.Options.DriAll=$True
$Scripter.Options.ExtendedProperties=$False
$Scripter.Options.IncludeHeaders=$False
$Scripter.Options.IncludeIfNotExists=$False
$Scripter.Options.IncludeDatabaseRoleMemberships=$True
$Scripter.Options.IncludeDatabaseContext=$False
$Scripter.Options.Permissions=$True
$Scripter.Options.SchemaQualify=$True
$Scripter.Options.ScriptData=$False
$Scripter.Options.ScriptDrops=$False
$Scripter.Options.Statistics=$False
$Scripter.Options.ToFileOnly=$True

$modifier = ""

if ($Item.Schema -ne $null)
{
$modifier += $Item.Schema + "."
}

if ($Item.Name -ne $null)
{
$p = $Item.Name
$modifier += $Item.Name.Replace("\","_") + "."
}

$Scripter.Options.FileName="D:\Source\SQL\Databases\"+$Database+"\"+$objectType+"\" + $modifier + "sql"

$p = "D:\Source\SQL\Databases\"+$database+"\"+$objectType+"\" + $modifier + "sql"
echo $p

$Scripter.Script($Item)

if ($objectType -eq "Tables")
{

foreach ($subtype in $TableSubtypes)
{
foreach ($subitem in $Item.$subtype)
{

$Scripter.Options.AnsiPadding=$False
$Scripter.Options.AnsiFile=$True
$Scripter.Options.AppendToFile=$False
$Scripter.Options.Bindings=$False
$Scripter.Options.DriAll=$False
$Scripter.Options.ExtendedProperties=$False
$Scripter.Options.IncludeHeaders=$False
$Scripter.Options.IncludeIfNotExists=$False
$Scripter.Options.Permissions=$True
$Scripter.Options.SchemaQualify=$True
$Scripter.Options.ScriptData=$False
$Scripter.Options.ScriptDrops=$False
$Scripter.Options.Statistics=$False
$Scripter.Options.ToFileOnly=$True


$modifier = ""

if ($Item.Schema -ne $null)
{
$modifier += $Item.Schema + "."
}

if ($Item.Name -ne $null)
{
$modifier += $Item.Name.Replace("\","_") + "."
}


if ($subitem.Name -ne $null)
{
$modifier += $subitem.Name.Replace("\","_") + "."
}

if ($subtype -eq "Indexes")
{
if ($subitem.IsClustered)
{
$modifier += "Clustered."
}

if ($subitem.IndexKeyType -eq "DriPrimaryKey")
{
#Primary keys fall under indexes as well as primary key branches
$subtype = "PrimaryKeys"
} else
{
$subtype = "Indexes"
}
}

$dirname = "Source\SQL\Databases\" + $Database + "\" + $subtype
new-item -Force -path D:\ -name $dirname -type directory

$Scripter.Options.FileName="D:\Source\SQL\Databases\"+$database+"\"+$subtype+"\" + $modifier + "sql"
$p = "D:\Source\SQL\Databases\"+$database+"\"+$subtype+"\" + $modifier + "sql"
echo $p
$Scripter.Script($subitem)
}
}
}
}
}



A sort of wrapper script to iterate through all the databases I want to script like this (e.g. nightly from a job), assuming of course you called the script above "ScriptDatabase.ps1" and stored in path shown Smile. This could be rolled into the above script, but wanted to retain ability to take a snapshot of a single database scripts.


$Path = "SQLSERVER:\SQL\localhost\DEFAULT\Databases"
cd $Path
foreach ($item in Get-Childitem)
{
echo $item.Name
D:\Scripts\ScriptDatabase.ps1 $item.Name
}


All of these scripts write to specific folders on the D:\ of my servers but could be adapted.

Lookup $Scripter.Options on the net for parameters to pretty much mirror anything you can do via "Generate scripts" in SSMS

Obviously a disclaimer applies: This does not cover every aspect of every type of object in SQL Server and should not be relied on in lieu of a proper backup. Adjust this to ensure that permissions / extended properties / other object types that this script excludes are included appropriately.

Regards,
Mike
Ben Ho-404335
Ben Ho-404335
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 197
Thanks for this great article. Now i'm one step closer to source controlling everything in my SQL Production environment. Cheers
salman788
salman788
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 438
Its working fine ----- but if I have to change user_id and password what do I need to do.
dave-dj
dave-dj
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3382 Visits: 1149
Hi All,

Hopefully someone can help point me in the right direction. Let me just start by saying I am a complete newbie when it comes to scripting a task using SMO and VB

What I want to acheive is the following:

1. Read in SSIS variables:
- 'live' server
- 'mirror' server
- JobID
- Job name

2. connect to the 'Live server'
3. script a specific job using either the JobID or JobName.
4. Either assign this back out to an SSIS variable or transfer the job to the 'mirror server'.


I've now got the Script task to connect to the the required server. I belive I've also managed to find out how to filter for the job I want to script.

Where I am still confused and lost is being able to actually read the SQL Script.



Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.common
Imports System.Collections.Specialized


Public Class ScriptMain

Public Sub Main()

'Get the Live/Mirror Server Address Details
Dim LiveServerName As String = Dts.Variables("LiveServerName").Value.ToString
Dim MirrorServerName As String = Dts.Variables("MirrorServerName").Value.ToString

'Get the JobID and Jobname that needs to be copied accross.
Dim Job_CopyJobToMirrorJobID As String = Dts.Variables("Job_CopyJobToMirrorJobID").Value.ToString
Dim Job_CopyJobToMirrorName As String = Dts.Variables("Job_CopyJobToMirrorName").Value.ToString

'Convert String to GUID - note: the string is already a correctly formated version of the job GUID
Dim GUID_CopyJobToMirrorJobID As New Guid(Job_CopyJobToMirrorJobID)

'Declare a ServerConnection object variable to specify SQL authentication, login and password.
Dim conn As New ServerConnection
conn.LoginSecure = False

'Connect to the local, default instance of SQL Server.
Dim LiveSvr As Server
LiveSvr = New Server(LiveServerName)

Dim MirrorSvr As Server
MirrorSvr = New Server(MirrorServerName)


'Script Out the Job

'Dim sc As stringCollection
Dim scrp As Scripter
scrp = New Scripter(LiveSvr)
scrp.Options.ScriptDrops = False
scrp.Options.ToString()

Dim sc As String
Dim JobScript As String
sc = LiveSvr.JobServer.Jobs.ItemById(GUID_CopyJobToMirrorJobID).Script().ToString()

MsgBox("SC: " & sc, MsgBoxStyle.OkOnly, "Script")

Dim st As String
For Each st In sc
JobScript = JobScript & st
Next

MsgBox("JobScript: " & JobScript)

'
Dts.TaskResult = Dts.Results.Success
End Sub

End Class




I'm struggling to get to grips with this at the moment, so any direction or help would be very appreciated.

Thanks

_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
dave-dj
dave-dj
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3382 Visits: 1149
any ideas anyone? .........

_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
Kenneth Fisher
Kenneth Fisher
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7396 Visits: 2076
Great article. I'm actually working on something similar and I'm hoping you can help me out with one last small piece. I'm trying to pull the connection information from a connection manager. I can get the server name without a problem. Where I'm running into an issue is that I want to use SQL Auth and I can't get the username or password out of the connection manager. Currently I'm trying the following code:


Dim sourceConn As ServerConnection
sourceConn = DirectCast(Dts.Connections("SOURCE").AcquireConnection(Dts.Transaction), _
ServerConnection)
Dim src As New Server(sourceConn)



Unfortunately I get an error trying to convert my acquired connection into a ServerConnection object. I'm using an ADO.Net connection type.

Thanks for any help
Kenneth

Kenneth FisherI strive to live in a world where a chicken can cross the road without being questioned about its motives.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/Link to my Blog Post --> www.SQLStudies.com
SQL Doc
SQL Doc
SSC Veteran
SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)

Group: General Forum Members
Points: 285 Visits: 429
I commented out a section in the code on how to use SQL Server Auth:

/* Setup connection, this is SQL Server authentication
ServerConnection conn = new ServerConnection();
conn.LoginSecure = false;
conn.Login = "YourUserName";
conn.Password = "YourPassword";
conn.ServerInstance = Dts.Variables["varServerName"].Value.ToString();
Server srv = new Server(conn);
*/
Just remove the trusted auth code and uncomment this section using the correct user name and password.

Thomas Lane, PhD
DBA
Edgenet / Big Hammer Data
SQL Lane
Kenneth Fisher
Kenneth Fisher
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7396 Visits: 2076
Unfortunatly I'm having to use a sysadmin login and I don't want to hard code my password into the code. Is there a way to pull it from the connection manager? Or better yet have the connection manager make the connection and then use that connection?

Thanks
Kenneth

Kenneth FisherI strive to live in a world where a chicken can cross the road without being questioned about its motives.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/Link to my Blog Post --> www.SQLStudies.com
SQL Doc
SQL Doc
SSC Veteran
SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)

Group: General Forum Members
Points: 285 Visits: 429
Sorry that is a classic problem with SQL Server logins. An option is to encrypt the password in a the config file and decrypt in the SSIS package using SQL Servers built in encryption/decryption or a home grown one using CLR sp or function. You can find info on both by doing a search here or on Google or Bing.

Thomas Lane, PhD
DBA
Edgenet / Big Hammer Data
SQL Lane
Kenneth Fisher
Kenneth Fisher
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7396 Visits: 2076
Is there no way to generate the connection using the AcquireConnection method and pass it to the SMO server object? That way I don't have to mess with the password at all within my script.

Kenneth

Kenneth FisherI strive to live in a world where a chicken can cross the road without being questioned about its motives.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/Link to my Blog Post --> www.SQLStudies.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search