Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

SSIS Package to Script All SQL Server Jobs to Individual Files Expand / Collapse
Author
Message
Posted Friday, July 9, 2010 2:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 31, 2014 8:17 AM
Points: 110, Visits: 182
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 :). 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
Post #949781
Posted Wednesday, August 18, 2010 10:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 19, 2012 3:09 PM
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
Post #971269
Posted Monday, September 13, 2010 12:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 16, 2014 1:09 PM
Points: 11, Visits: 334
Its working fine ----- but if I have to change user_id and password what do I need to do.
Post #985019
Posted Monday, December 20, 2010 10:04 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 2:57 AM
Points: 405, Visits: 1,137
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)
Post #1037271
Posted Tuesday, January 4, 2011 6:52 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 2:57 AM
Points: 405, Visits: 1,137
any ideas anyone? .........

_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
Post #1042356
Posted Monday, February 7, 2011 9:02 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:11 AM
Points: 3,462, Visits: 1,801
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 Fisher
I 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
Post #1059630
Posted Monday, February 7, 2011 9:16 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 2:37 PM
Points: 239, Visits: 372
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
Post #1059645
Posted Monday, February 7, 2011 9:23 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:11 AM
Points: 3,462, Visits: 1,801
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 Fisher
I 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
Post #1059658
Posted Monday, February 7, 2011 9:45 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 2:37 PM
Points: 239, Visits: 372
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
Post #1059693
Posted Monday, February 7, 2011 10:02 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:11 AM
Points: 3,462, Visits: 1,801
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 Fisher
I 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
Post #1059717
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse