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 Tuesday, July 6, 2010 2:28 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 16, 2014 1:09 PM
Points: 11, Visits: 334
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
Post #948187
Posted Tuesday, July 6, 2010 2:31 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 3, 2013 8:58 AM
Points: 42, Visits: 81
Turns out that I can run from the server, just not from my desktop. Script is great and very useful. Thanks.
Post #948191
Posted Tuesday, July 6, 2010 3:57 PM
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
salman788 (7/6/2010)
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


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.


Thomas Lane, PhD
DBA
Edgenet / Big Hammer Data
SQL Lane
Post #948222
Posted Tuesday, July 6, 2010 4:00 PM
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
kimetch (7/6/2010)
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?


Retrieve ScriptJobs2005.dtsx package and config file from my earlier post. If that does not work make sure you have BIDS and SDKs installed.


Thomas Lane, PhD
DBA
Edgenet / Big Hammer Data
SQL Lane
Post #948223
Posted Wednesday, July 7, 2010 6:50 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 5:41 PM
Points: 2,693, Visits: 1,203
I 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 job



Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
Post #948498
Posted Wednesday, July 7, 2010 8:07 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
Why 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?

Thomas Lane, PhD
DBA
Edgenet / Big Hammer Data
SQL Lane
Post #948555
Posted Wednesday, July 7, 2010 2:47 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 1:51 PM
Points: 21,644, Visits: 15,317
Yes, please do post that powershell script.


This was a nice article - thanks for sharing.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #948847
Posted Wednesday, July 7, 2010 4:41 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 5:41 PM
Points: 2,693, Visits: 1,203
Yeh, was planning to post it, but was rushed with the earlier posting

Just replace
"<< YOUR SERVER >>" with server name
"<< INSTANCE >>" with instance name, or DEFAULT if no instance.
"<< YOUR FOLDER >>" the folder you want the scripts saved in.

CD SQLSERVER:\SQL\<< YOUR SERVER >>\<< INSTANCE >>\JobServer\Jobs
$outDir = "<< YOUR FOLDER >>"
$Scripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter") ("<< YOUR SERVER >>")
$Scripter.Options.DriAll=$False
$Scripter.Options.IncludeHeaders=$False
$Scripter.Options.ToFileOnly=$True
$Scripter.Options.WithDependencies=$False

foreach ($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)
}

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 info

Enjoy


Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
Post #948900
Posted Wednesday, July 7, 2010 5:27 PM
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
Thanks Philcart!

I really need to look at this powershell someday when I have some free time.


Thomas Lane, PhD
DBA
Edgenet / Big Hammer Data
SQL Lane
Post #948917
Posted Wednesday, July 7, 2010 7:00 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 1:51 PM
Points: 21,644, Visits: 15,317
Thanks Phil.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #948934
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse