Hi All,

Hopefully someone can help point me in the right direction. Let me just start by saying I am a complete newbie when it comes to scripting a task using SMO and VB

What I want to acheive is the following:

1. Read in SSIS variables:

- 'live' server

- 'mirror' server

- JobID

- Job name

2. connect to the 'Live server'

3. script a specific job using either the JobID or JobName.

4. Either assign this back out to an SSIS variable or transfer the job to the 'mirror server'.

I've now got the Script task to connect to the the required server. I belive I've also managed to find out how to filter for the job I want to script.

Where I am still confused and lost is being able to actually read the SQL Script.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.SqlServer.Management.Smo

Imports Microsoft.SqlServer.Management.common

Imports System.Collections.Specialized

Public Class ScriptMain

Public Sub Main()

'Get the Live/Mirror Server Address Details

Dim LiveServerName As String = Dts.Variables("LiveServerName").Value.ToString

Dim MirrorServerName As String = Dts.Variables("MirrorServerName").Value.ToString

'Get the JobID and Jobname that needs to be copied accross.

Dim Job_CopyJobToMirrorJobID As String = Dts.Variables("Job_CopyJobToMirrorJobID").Value.ToString

Dim Job_CopyJobToMirrorName As String = Dts.Variables("Job_CopyJobToMirrorName").Value.ToString

'Convert String to GUID - note: the string is already a correctly formated version of the job GUID

Dim GUID_CopyJobToMirrorJobID As New Guid(Job_CopyJobToMirrorJobID)

'Declare a ServerConnection object variable to specify SQL authentication, login and password.

Dim conn As New ServerConnection

conn.LoginSecure = False

'Connect to the local, default instance of SQL Server.

Dim LiveSvr As Server

LiveSvr = New Server(LiveServerName)

Dim MirrorSvr As Server

MirrorSvr = New Server(MirrorServerName)

'Script Out the Job

'Dim sc As stringCollection

Dim scrp As Scripter

scrp = New Scripter(LiveSvr)

scrp.Options.ScriptDrops = False

scrp.Options.ToString()

Dim sc As String

Dim JobScript As String

sc = LiveSvr.JobServer.Jobs.ItemById(GUID_CopyJobToMirrorJobID).Script().ToString()

MsgBox("SC: " & sc, MsgBoxStyle.OkOnly, "Script")

Dim st As String

For Each st In sc

JobScript = JobScript & st

Next

MsgBox("JobScript: " & JobScript)

'

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

I'm struggling to get to grips with this at the moment, so any direction or help would be very appreciated.

Thanks