|
|
|
Grasshopper
      
Group: General Forum Members
Last Login: Wednesday, May 08, 2013 1:21 PM
Points: 11,
Visits: 270
|
|
| 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
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:17 AM
Points: 42,
Visits: 65
|
|
| Turns out that I can run from the server, just not from my desktop. Script is great and very useful. Thanks.
|
|
|
|
|
SSC Veteran
      
Group: General Forum Members
Last Login: Yesterday @ 8:26 AM
Points: 239,
Visits: 317
|
|
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
|
|
|
|
|
SSC Veteran
      
Group: General Forum Members
Last Login: Yesterday @ 8:26 AM
Points: 239,
Visits: 317
|
|
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
|
|
|
|
|
SSCrazy
      
Group: General Forum Members
Last Login: Friday, May 17, 2013 5:53 AM
Points: 2,692,
Visits: 1,074
|
|
|
|
|
|
SSC Veteran
      
Group: General Forum Members
Last Login: Yesterday @ 8:26 AM
Points: 239,
Visits: 317
|
|
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
|
|
|
|
|
SSCoach
         
Group: General Forum Members
Last Login: Monday, May 20, 2013 1:07 PM
Points: 18,733,
Visits: 12,332
|
|
|
|
|
|
SSCrazy
      
Group: General Forum Members
Last Login: Friday, May 17, 2013 5:53 AM
Points: 2,692,
Visits: 1,074
|
|
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
|
|
|
|
|
SSC Veteran
      
Group: General Forum Members
Last Login: Yesterday @ 8:26 AM
Points: 239,
Visits: 317
|
|
|
|
|
|
SSCoach
         
Group: General Forum Members
Last Login: Monday, May 20, 2013 1:07 PM
Points: 18,733,
Visits: 12,332
|
|
|
|
|