InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 05:23:35 GMT20http://www.sqlservercentral.com/Forums/Topic947688-2733-1.aspxYour wish is my command. This should comment out the schedule_uid parts.Mon, 31 Dec 2012 14:02:02 GMTSQL Dochttp://www.sqlservercentral.com/Forums/Topic947688-2733-1.aspxTom, thanks for sharing this. Not sure what you did to prevent that annoying @schedule_uid from appearing in the new job (which would cause it not to compile) but I'm glad it's out. This saved me some time when trying to script out 30 cloned jobs on a new machine. And now I have a backup of the jobs :-DKenMon, 08 Oct 2012 12:02:21 GMTken.trockhttp://www.sqlservercentral.com/Forums/Topic947688-2733-1.aspxI did in fact just find a solution. It turns out the AcquireConnection method of a SMO connection is an SMO Server, so I was able to use the following code.[code]Dim src As Serversrc = DirectCast(Dts.Connections("SMOSOURCE").AcquireConnection(Dts.Transaction), _ Server)[/code]I have actually done some encryption with SQL Server and would probably have gone that way at need. I just prefer this way because I can let the connection manager handle everything, and it just seems more neatly handled this way.ThanksKennethMon, 07 Feb 2011 13:05:35 GMTKenneth.Fisherhttp://www.sqlservercentral.com/Forums/Topic947688-2733-1.aspxFor a sql server login I can not think of a way. The AcquireConnection method requires that the connection object have a connection string. Doing encryption is really not that hard try something like this [url=http://www.databasejournal.com/scripts/article.php/1496441/Enhanced-RC4-EncryptionDecryption-Script.htm][/url]Just name the function something weird.Good luckMon, 07 Feb 2011 11:22:32 GMTSQL Dochttp://www.sqlservercentral.com/Forums/Topic947688-2733-1.aspxIs 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.KennethMon, 07 Feb 2011 10:02:35 GMTKenneth.Fisherhttp://www.sqlservercentral.com/Forums/Topic947688-2733-1.aspxSorry 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.Mon, 07 Feb 2011 09:45:29 GMTSQL Dochttp://www.sqlservercentral.com/Forums/Topic947688-2733-1.aspxUnfortunatly 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?ThanksKennethMon, 07 Feb 2011 09:23:38 GMTKenneth.Fisherhttp://www.sqlservercentral.com/Forums/Topic947688-2733-1.aspxI 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.Mon, 07 Feb 2011 09:16:46 GMTSQL Dochttp://www.sqlservercentral.com/Forums/Topic947688-2733-1.aspxGreat 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:[code]Dim sourceConn As ServerConnectionsourceConn = DirectCast(Dts.Connections("SOURCE").AcquireConnection(Dts.Transaction), _ ServerConnection)Dim src As New Server(sourceConn)[/code]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 helpKennethMon, 07 Feb 2011 09:02:54 GMTKenneth.Fisherhttp://www.sqlservercentral.com/Forums/Topic947688-2733-1.aspxany ideas anyone? .........Tue, 04 Jan 2011 06:52:15 GMTdave-djhttp://www.sqlservercentral.com/Forums/Topic947688-2733-1.aspxHi 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 VBWhat I want to acheive is the following:1. Read in SSIS variables: - 'live' server - 'mirror' server - JobID - Job name2. 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.[code="vb"]Imports SystemImports System.DataImports System.MathImports Microsoft.SqlServer.Dts.RuntimeImports Microsoft.SqlServer.Management.SmoImports Microsoft.SqlServer.Management.commonImports System.Collections.SpecializedPublic 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 SubEnd Class[/code]I'm struggling to get to grips with this at the moment, so any direction or help would be very appreciated.ThanksMon, 20 Dec 2010 10:04:59 GMTdave-djhttp://www.sqlservercentral.com/Forums/Topic947688-2733-1.aspxIts working fine ----- but if I have to change user_id and password what do I need to do.Mon, 13 Sep 2010 12:37:54 GMTsalman788http://www.sqlservercentral.com/Forums/Topic947688-2733-1.aspxThanks for this great article. Now i'm one step closer to source controlling everything in my SQL Production environment. CheersWed, 18 Aug 2010 10:16:40 GMTBen Ho-404335http://www.sqlservercentral.com/Forums/Topic947688-2733-1.aspxSome 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.[code="plain"]$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 = 0do { $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)[/code]Script database objects I'm interested in (including certain specific table bits like indexes and foreign keys)[code="plain"]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\"+$Databasecd $Pathforeach ($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) } } } }}[/code]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. [code="plain"]$Path = "SQLSERVER:\SQL\localhost\DEFAULT\Databases"cd $Pathforeach ($item in Get-Childitem){ echo $item.Name D:\Scripts\ScriptDatabase.ps1 $item.Name}[/code] 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 SSMSObviously 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,MikeFri, 09 Jul 2010 02:36:04 GMTmike.renwick-894639http://www.sqlservercentral.com/Forums/Topic947688-2733-1.aspxThanks Phil.Wed, 07 Jul 2010 19:00:07 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic947688-2733-1.aspxThanks Philcart!I really need to look at this powershell someday when I have some free time.Wed, 07 Jul 2010 17:27:33 GMTSQL Dochttp://www.sqlservercentral.com/Forums/Topic947688-2733-1.aspxYeh, was planning to post it, but was rushed with the earlier posting :-DJust replace "&lt;&lt; YOUR SERVER &gt;&gt;" with server name"&lt;&lt; INSTANCE &gt;&gt;" with instance name, or DEFAULT if no instance."&lt;&lt; YOUR FOLDER &gt;&gt;" the folder you want the scripts saved in.[code]CD SQLSERVER:\SQL\&lt;&lt; YOUR SERVER &gt;&gt;\&lt;&lt; INSTANCE &gt;&gt;\JobServer\Jobs$outDir = "&lt;&lt; YOUR FOLDER &gt;&gt;"$Scripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter") ("&lt;&lt; YOUR SERVER &gt;&gt;")$Scripter.Options.DriAll=$False$Scripter.Options.IncludeHeaders=$False$Scripter.Options.ToFileOnly=$True$Scripter.Options.WithDependencies=$Falseforeach ($Item in Get-ChildItem) { $nm = $Item -replace ":", "" $Scripter.Options.FileName=$outDir + "SQLAgentJob_" + $nm + ".sql" $Scripter.Options.AppendToFile=$False $Scripter.Options.IncludeIfNotExists=$True; $Scripter.Options.ScriptDrops=$True; $Scripter.Script($Item) $Scripter.Options.AppendToFile=$True $Scripter.Options.IncludeIfNotExists=$False; $Scripter.Options.ScriptDrops=$False; $Scripter.Script($Item)}[/code]Couple of things I'll change when I get some spare time ( yeh right, who has spare time )- Add server name/instance to file name- Enhance the -replace part to do regex replace to remove invalid chars- maybe see what static values can be replace with variables, or dynamic infoEnjoyWed, 07 Jul 2010 16:41:33 GMTphilcarthttp://www.sqlservercentral.com/Forums/Topic947688-2733-1.aspxYes, please do post that powershell script.This was a nice article - thanks for sharing.Wed, 07 Jul 2010 14:47:23 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic947688-2733-1.aspxWhy don't you post it? I am always interested in multiple ways of doing the same thing. This would only work for SQL Server 2008 and above because powershell is not available as a Job step type in SQL Server 2000 or 2005, right?Wed, 07 Jul 2010 08:07:57 GMTSQL Dochttp://www.sqlservercentral.com/Forums/Topic947688-2733-1.aspxI have a script that runs in a Powershell script step of a SQL Agent job to do this.Makes deployment pretty easy as it's just T-SQL to create the SQL Agent jobWed, 07 Jul 2010 06:50:10 GMTphilcarthttp://www.sqlservercentral.com/Forums/Topic947688-2733-1.aspx[quote][b]kimetch (7/6/2010)[/b][hr]Although I have sql2005 with SP3 and ssis and sdk installed, I receive "Error the task "script task" cannot run on this edition of integration services. it requires a higher level edition. Any ideas what I might be missing?[/quote]Retrieve ScriptJobs2005.dtsx package and config file from my earlier post. If that does not work make sure you have BIDS and SDKs installed.Tue, 06 Jul 2010 16:00:34 GMTSQL Dochttp://www.sqlservercentral.com/Forums/Topic947688-2733-1.aspx[quote][b]salman788 (7/6/2010)[/b][hr]I have figured out the how to change MYJOB folder. But can you please tell me how can I change TEMP folder location where you keep ScriptJobs.dtsconfig[/quote]You would need to open the package in the BIDS(SQL Server Business Intelligence Development Studio) and change it. Just select "SQL Server Business Intelligence Development Studio" from the SQL Server menu and create a new integration service project name it anything you like and right click on the SSIS Package and click on add existing package. From Add copy of existing package dialog box select file system from the Package Location drop-down and navigate to where you saved the package. Once added to the project open the package and right click in an open space on the design interface. Then select "Package Configuration" and remove the existing file and add a new one in any location you want. Add the two variable values to the configuration file. Save the SSIS package and retrieve the new package from the location in the project and it will run with the new configuration file.Tue, 06 Jul 2010 15:57:38 GMTSQL Dochttp://www.sqlservercentral.com/Forums/Topic947688-2733-1.aspxTurns out that I can run from the server, just not from my desktop. Script is great and very useful. Thanks.Tue, 06 Jul 2010 14:31:49 GMTkimetchhttp://www.sqlservercentral.com/Forums/Topic947688-2733-1.aspxI have figured out the how to change MYJOB folder. But can you please tell me how can I change TEMP folder location where you keep ScriptJobs.dtsconfigTue, 06 Jul 2010 14:28:46 GMTsalman788http://www.sqlservercentral.com/Forums/Topic947688-2733-1.aspxPlease check SSC Vetran's reply it has two file in it.Tue, 06 Jul 2010 13:52:15 GMTsalman788http://www.sqlservercentral.com/Forums/Topic947688-2733-1.aspxAlthough I have sql2005 with SP3 and ssis and sdk installed, I receive "Error the task "script task" cannot run on this edition of integration services. it requires a higher level edition. Any ideas what I might be missing?Tue, 06 Jul 2010 13:37:32 GMTkimetchhttp://www.sqlservercentral.com/Forums/Topic947688-2733-1.aspxThanks you so much it worked.Tue, 06 Jul 2010 12:14:32 GMTsalman788http://www.sqlservercentral.com/Forums/Topic947688-2733-1.aspxTo use the scriptJobs2005.dtsx file you will need to have SQL Server 2005 installed with BIDS (SQL Server Business Intelligence Development Studio) and the SDKs (the folder C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies should many ddls in it). Then put the ScriptJobs.dtsconfig file in C:\temp. Then edit the ScriptJobs.dtsconfig file so that the server name is correct where the "########" is below. Then right click on scriptJobs2005.dtsx in windows explorer and click open. This will open the execute package Utility in which you will click execute. Then after it finishes, the sql files for each job will be in the C:\MyJobs folder. I am assuming the you have access to the SQL Server via your network password using integrated security. &lt;?xml version="1.0"?&gt;&lt;DTSConfiguration&gt; &lt;DTSConfigurationHeading&gt; &lt;DTSConfigurationFileInfo GeneratedBy="xxxx" GeneratedFromPackageName="ScriptJobs" GeneratedFromPackageID="{BE7E98AD-FC33-4C70-BB2B-B081DBBB6B97}" GeneratedDate="6/10/2010 3:47:27 PM"/&gt; &lt;/DTSConfigurationHeading&gt; &lt;Configuration ConfiguredType="Property" Path="\Package.Variables[User::varFilePath].Properties[Value]" ValueType="String"&gt; &lt;ConfiguredValue&gt;C:\MyJobs&lt;/ConfiguredValue&gt; &lt;/Configuration&gt; &lt;Configuration ConfiguredType="Property" Path="\Package.Variables[User::varServerName].Properties[Value]" ValueType="String"&gt; &lt;ConfiguredValue&gt;########&lt;/ConfiguredValue&gt; &lt;/Configuration&gt;&lt;/DTSConfiguration&gt;Tue, 06 Jul 2010 11:57:19 GMTSQL Dochttp://www.sqlservercentral.com/Forums/Topic947688-2733-1.aspxQuestion I have downloaded both the files removed the .txt extension, but still I am not sure what to do. Can you please tell me the step by step process.Tue, 06 Jul 2010 11:05:13 GMTsalman788http://www.sqlservercentral.com/Forums/Topic947688-2733-1.aspxNice. Thanks for sharing!Tue, 06 Jul 2010 09:48:12 GMTJJ Bhttp://www.sqlservercentral.com/Forums/Topic947688-2733-1.aspxThanks that seems to work with SQL 2005 now.IanTue, 06 Jul 2010 07:12:51 GMTIan Ragghttp://www.sqlservercentral.com/Forums/Topic947688-2733-1.aspxI have attached the SQL Server 2005 version just remove the txt file extensions. Let me know if you have any issues. This version will only work on SQL Server 2000 and 2005 jobs with the package run from a SQL Server 2005 installed PC.Tue, 06 Jul 2010 07:05:08 GMTSQL Dochttp://www.sqlservercentral.com/Forums/Topic947688-2733-1.aspxGreat article, unfortunately when I try to use the package on a SQL 2005 server I get a message indicating the package is a higher version than installed. Is there a SQL 2005 version of the package available?Ian.Tue, 06 Jul 2010 01:27:56 GMTIan Ragghttp://www.sqlservercentral.com/Forums/Topic947688-2733-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/SSIS/70475/">SSIS Package to Script All SQL Server Jobs to Individual Files</A>[/B]Mon, 05 Jul 2010 22:16:22 GMTSQL Doc