SSIS Package to Script All SQL Server Jobs to Individual Files

Question

SSIS Package to Script All SQL Server Jobs to Individual Files

Viewing 6 posts - 31 through 35 (of 35 total)

You must be logged in to reply to this topic. Login to reply

SQL Doc Mr or Mrs. 500 Points: 514 More actions · Answer 1

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

Just name the function something weird.

Good luck

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

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 2

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 FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

ken.trock SSCertifiable Points: 5147 More actions · Answer 3

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 😀

Ken