Hi,

I'm keen to find out the recommended way to retrieve the job status for a
running IS package on a remote server (SQL Server 2005 SP1 Enterprise
Edition). The scenario is that a user has my Windows Forms application
(VB.NET v2) which has various database maintenance jobs to do, several of
which are done via IS packages.

I found out fairly early on that this was best achieved by setting up SQL
Agent jobs which point to the relevant package on the server (I'm using the
file system approach). Since some of the jobs take a while to run, I want
also to provide the user with the ability to check the status of the
job/package.

Unfortunately, the information returned by querying the SQL Agent job itself
is ambiguous; the job may be reported as idle since the IS package happens
then to be in some long-running script component of the data flow. So, after
a fair bit of hunting, I found the Microsoft.SqlServer.Dts.Runtime assembly
which offers up an Application object which I can then use to query for
running packages. If the package is still running, I report that to be the
case; if not running, I get the LastRunOutcome of the SQL Agent job.

All works fine on my development machine, but on a user's machine the error
is:

Microsoft.SqlServer.Dts.Runtime.DtsPipelineExcepti on: Retrieving the COM
class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E}
failed due to the following error: 80040154

This, it seems, is due to the fact that IS is not installed on the user's
PC, nor are the components redistributable. Question then is how to get the
RunningPackages information back to a client PC? Is there some other part of
the DTS runtime that I can use? Or another class? The SQL Agent job status
is not sufficient in this case.

Most grateful for any suggestions. With thanks and regards,

Sebastian Crewe

Sebastian,
Did were you ever able to resolve this issue?
Thanx,
Mike

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

Yes I was. A custom assembly seemed to be the route to take and, other than
the hassle of getting the security right, it is doing what I wanted. The
assembly and associated stored procedure is housed in the master database.

The comments and code below are from my working project (VB.NET, VS2005,
SQL2005 Enterprise SP1). I hope this will be sufficient to get things
working for you. If not, do drop a line.

You'll see a few steps where an assembly is marked as unsafe. I was
sufficiently worried about this to call Microsoft for their guidance. They
advised that this was the only way I was going to get the code to work.
Since the assembly only returns a string result about a runing job, I don't
think security is compromised too severely.

Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub GetISPackageProgressProc(ByVal JobName As String,
<Runtime.InteropServices.Out()> ByRef strResult As String)
Dim ssisApplication As Application
Dim pkgCollection As IDTSRunningPackages90

'In order to get this to work, needed to add the
Microsoft.SQLServer.DTSRuntimeWrap assembly to the
'master database, Assemblies container, marked with the UNSAFE
Permission Set. This was achieved using
'the steps given in MSDN, and saved as an SQL script (CREATE UNSAFE
and EXTERNAL_ACCESS assemblies.sql) - see below

'Also needed to create a strong name key file for this project, in
order to Allow Partially Trusted Callers.
'Create this by opening an SDK Command Prompt, cd to the project
directory, then sn -k KeyName.snk

'Added this and the APTCA attribute to the AssemblyInfo.vb file
(Project, Show All Files)

'Then copy over dll and key file to a directory on the server. Need
them there in order to run the
'CREATE ASYMMETRIC KEY and CREATE ASSEMBLY statements with a local
path reference. Note that a different
'LOGIN is required for each asymmetric key. These logins are stored
in the background, but don't show up
'in SQL Security. Again, the Permission Set is UNSAFE in order to
have the SQL runtime treat this
'assembly as trusted code.

'Finally, use the Create Stored Procedure from Assembly script in
order to make the externally
'accessible stored procedure.

'The default value
strResult = String.Empty

Try
ssisApplication = New Application
pkgCollection = ssisApplication.GetRunningPackages("localhost")

For Each package As IDTSRunningPackage90 In pkgCollection
If package.PackageName = JobName Then
strResult = "Still Executing"
Exit For
End If
Next
Catch ex As Exception
strResult = "So sorry - there was an exception getting the
package status"
End Try

End Sub

End Class

The CREATE UNSAFE and EXTERNAL_ACCESS assemblies.sql code reads as follows:

/*
The point of this is to add a reference that is required by a .NET
procedure, eg within a
CLR Stored Procedure. I needed the DTSRuntime library in order to make a
stored procedure that
could query the Running Packages of Integration Services, but DTSRuntimeWrap
was not one of
the standard references.
*/

USE [master]
GO

-- First create the Key
CREATE ASYMMETRIC KEY DTSRuntimeWrapKey FROM EXECUTABLE FILE =
'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer. DTSRuntimeWrap\9.0.242.0__89845dcd8080cc91\Microso ft.SQLServer.DTSRuntimeWrap.dll'
CREATE LOGIN SQLCLRLogin FROM ASYMMETRIC KEY DTSRuntimeWrapKey
GRANT UNSAFE ASSEMBLY TO SQLCLRLogin
GO

-- Next create the assembly
CREATE ASSEMBLY [Microsoft.SQLServer.DTSRuntimeWrap]
FROM
'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer. DTSRuntimeWrap\9.0.242.0__89845dcd8080cc91\Microso ft.SQLServer.DTSRuntimeWrap.dll'
WITH PERMISSION_SET = UNSAFE
GO

Thanks and regards

Sebastian Crewe

"Mike Voi" wrote: