SSIS Package to Script All SQL Server Jobs to Individual Files

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Thomas Lane » SSIS Package to Script All SQL Server Jobs to...

34 posts, Page 4 of 4

«««1 2 3 4

SSIS Package to Script All SQL Server Jobs to Individual Files

Rate Topic

Display Mode

Topic Options

Author

Message

SQL Doc

Posted Monday, February 07, 2011 11:22 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, May 17, 2013 10:09 AM
Points: 239, Visits: 316

For 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 luck

Thomas Lane, PhD
DBA
Edgenet / Big Hammer Data
SQL Lane

Post #1059793

Kenneth.Fisher

Posted Monday, February 07, 2011 1:05 PM

Hall of Fame

Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:31 AM
Points: 3,367, Visits: 1,563

I 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.

Dim src As Server
src = DirectCast(Dts.Connections("SMOSOURCE").AcquireConnection(Dts.Transaction), _
                Server)

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.

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 #1059872

ken.trock

Posted Monday, October 08, 2012 12:02 PM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 2:37 PM
Points: 315, Visits: 1,353

Tom, 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 BigGrin