Click here to monitor SSC
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
salman788
salman788
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 438
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
kimetch
kimetch
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 87
Turns out that I can run from the server, just not from my desktop. Script is great and very useful. Thanks.
SQL Doc
SQL Doc
SSC Veteran
SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)

Group: General Forum Members
Points: 239 Visits: 428
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
SQL Doc
SQL Doc
SSC Veteran
SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)

Group: General Forum Members
Points: 239 Visits: 428
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
philcart
philcart
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2748 Visits: 1434
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
SQL Doc
SQL Doc
SSC Veteran
SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)

Group: General Forum Members
Points: 239 Visits: 428
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
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21075 Visits: 18259
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

philcart
philcart
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2748 Visits: 1434
Yeh, was planning to post it, but was rushed with the earlier posting :-D

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
SQL Doc
SQL Doc
SSC Veteran
SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)

Group: General Forum Members
Points: 239 Visits: 428
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
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21075 Visits: 18259
Thanks Phil.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw

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