Calling SSIS packages from within a Script task

Hello,

I was wondering if someone could point me in the direction of any sample code or documentation that allows you to execute SSIS packages from within a Script task?

In SQL Server 2000 DTS this was accomplished by instantiating the DTS.Package object and calling either the LoadFromSQLServer or LoadFromStorageFile method.

I have not been able to find any similiar logic in the SQL Server 2005 SSIS environment.

Any help would be greatly appreciated.

Thank you,

Gordon Radley

Sample code to execute a Package from Script task

Public Sub Main()
Dim pkg As String = "C:\SSISPackage\Package.dtsx"
Dim app As Application = New Application()
Dim p As Package = app.LoadPackage(pkg, Nothing)
p.Execute()
Dts.TaskResult = Dts.Results.Success
End Sub

Hope this helps.

Thanks,
Loonysan

|||

Yes - it definately helps. Thanks for the quick reply.

Do I need to add any additional references if I'm running this from within a Script task in SSIS?

Thanks again!

|||

The Runtime reference is sufficient. You'll want to make sure that you have the Imports statement in your code.

As you've notice, the "Load" methods are now more logically methods of the SSIS Application rather than of the Package itself.

For what it's worth, the documentation team is fleshing out the section on how packages can be loaded, executed, enumerated, and managed programmatically, and how their output can be loaded directly into other applications by using the DtsClient DLL.

-Doug

|||

Doug,

We have discovered issues with DTSClient. Basically you don't get much information out of it. e.g. If a package fails to validate the only message you get is "The package failed to validate" rather than which task failed to validate.

Darren has uncompiled the managed code and noticed some shortcoming in the way it hasbeen implemented. This is wa too low level for me but there are some easy fixes to this. I'm sure Darren would be happy to share his experiences I'm sure.

-Jamie

|||

Darren would never do such a thing, but either way this is not DtsClient. This thread is all about loading and executing a package, directly. For general info (not Jamie) DtsClient does this, but wraps it all up such that you can consume data exposed from the DataReader destination, see the BOL for samples of consuming SSIS from extrenal applications.

When loading and executing you can capture.events, by passing in an implementation of IDTSEvents, see the overloaded methods. If you don't then if anything goes wrong all you really know is it went wrong. By examining the events you get to know why. The package failed validation is a start, but it failed validation because it could not make a connection to SQL Server XYZ in Connection "ABC" is somewhat more helpfull. I strongly suggest you look further at this and implement IDTSEvents.

Jamie got the two confused because DtsClient does not do what I have suggested, and as such is really a nightmare to use real world. Being told something failed is better than not, but if you don't know what failed in a large package you can waste some serious amounts of time trying to find out, we have! For the DtsClient authrors to have not captued the events and exposed the information is just shockingly poor I find, and we no longer use it for that reason.

So to sum up, if you are loading and executing packages then have a look at the overloaded methods that accept IDTSEvents, it will be worth your while when things go wrong. If using DtsClient, I hope it doesn't go wrong for you!

=== Edited by DarrenSQLIS @. 04 Mar 2006 10:17 PM UTC===
Umm... re-reading the thread I now see Doug raised DtsClient, so I was more confused than anyone. Comments still stand, but I'm confused not Jamie.

|||

I have tried as you explained. Nothing happened. I have a top package from here I call another package which contains only script task.

In this script task, the package is called as below.

Dim Lpkg As String = "C:\User Entry Window\LongitudinalLoadingData.dtsx"

Dim app As Microsoft.SqlServer.Dts.Runtime.Application = New Microsoft.SqlServer.Dts.Runtime.Application

Dim pL As Package = app.LoadPackage(Lpkg, Nothing)

If CStr(Dts.Variables("TypeOfSurvey").Value) = "L" Then

pL.Execute()

else

...Call another package.

End if.

I set the MsgBox message inside the LongitudinalLoadingData, but even this message will not appear. Do I need to add something?

Calling SSIS packages from within a Script task

Hello,

I was wondering if someone could point me in the direction of any sample code or documentation that allows you to execute SSIS packages from within a Script task?

In SQL Server 2000 DTS this was accomplished by instantiating the DTS.Package object and calling either the LoadFromSQLServer or LoadFromStorageFile method.

I have not been able to find any similiar logic in the SQL Server 2005 SSIS environment.

Any help would be greatly appreciated.

Thank you,

Gordon Radley

Sample code to execute a Package from Script task

Public Sub Main()
Dim pkg As String = "C:\SSISPackage\Package.dtsx"
Dim app As Application = New Application()
Dim p As Package = app.LoadPackage(pkg, Nothing)
p.Execute()
Dts.TaskResult = Dts.Results.Success
End Sub

Hope this helps.

Thanks,
Loonysan

|||

Yes - it definately helps. Thanks for the quick reply.

Do I need to add any additional references if I'm running this from within a Script task in SSIS?

Thanks again!

|||

The Runtime reference is sufficient. You'll want to make sure that you have the Imports statement in your code.

As you've notice, the "Load" methods are now more logically methods of the SSIS Application rather than of the Package itself.

For what it's worth, the documentation team is fleshing out the section on how packages can be loaded, executed, enumerated, and managed programmatically, and how their output can be loaded directly into other applications by using the DtsClient DLL.

-Doug

|||

Doug,

We have discovered issues with DTSClient. Basically you don't get much information out of it. e.g. If a package fails to validate the only message you get is "The package failed to validate" rather than which task failed to validate.

Darren has uncompiled the managed code and noticed some shortcoming in the way it hasbeen implemented. This is wa too low level for me but there are some easy fixes to this. I'm sure Darren would be happy to share his experiences I'm sure.

-Jamie

|||

Darren would never do such a thing, but either way this is not DtsClient. This thread is all about loading and executing a package, directly. For general info (not Jamie) DtsClient does this, but wraps it all up such that you can consume data exposed from the DataReader destination, see the BOL for samples of consuming SSIS from extrenal applications.

When loading and executing you can capture.events, by passing in an implementation of IDTSEvents, see the overloaded methods. If you don't then if anything goes wrong all you really know is it went wrong. By examining the events you get to know why. The package failed validation is a start, but it failed validation because it could not make a connection to SQL Server XYZ in Connection "ABC" is somewhat more helpfull. I strongly suggest you look further at this and implement IDTSEvents.

Jamie got the two confused because DtsClient does not do what I have suggested, and as such is really a nightmare to use real world. Being told something failed is better than not, but if you don't know what failed in a large package you can waste some serious amounts of time trying to find out, we have! For the DtsClient authrors to have not captued the events and exposed the information is just shockingly poor I find, and we no longer use it for that reason.

So to sum up, if you are loading and executing packages then have a look at the overloaded methods that accept IDTSEvents, it will be worth your while when things go wrong. If using DtsClient, I hope it doesn't go wrong for you!

=== Edited by DarrenSQLIS @. 04 Mar 2006 10:17 PM UTC===
Umm... re-reading the thread I now see Doug raised DtsClient, so I was more confused than anyone. Comments still stand, but I'm confused not Jamie.

|||

I have tried as you explained. Nothing happened. I have a top package from here I call another package which contains only script task.

In this script task, the package is called as below.

Dim Lpkg As String = "C:\User Entry Window\LongitudinalLoadingData.dtsx"

Dim app As Microsoft.SqlServer.Dts.Runtime.Application = New Microsoft.SqlServer.Dts.Runtime.Application

Dim pL As Package = app.LoadPackage(Lpkg, Nothing)

If CStr(Dts.Variables("TypeOfSurvey").Value) = "L" Then

pL.Execute()

else

...Call another package.

End if.

I set the MsgBox message inside the LongitudinalLoadingData, but even this message will not appear. Do I need to add something?

Calling SSIS packages from a Service Broker Queue

I would like to call an SSIS package from a Service Broker Queue.

There is one way that I am aware of -

Using xp_cmdshell from within an activation stored procedure and using DTEXEC.

Is there a more elegant way of executing an SSIS package from within SSB?

Also, I am not interested in writing a .NET external activator to process my messages in the queue. I would like this operation to be strictly database oriented. Having said this, I am also trying to avoid triggers processing the messages in the queue.

Thank you!

SQLCLR with unsafe assembly (in order to call Process.Start()) would be slightly better option than xp_cmdshell. Even then, starting external processes from an activated stored procedure is limiting and dangerous. Limiting because you can only start the external application as the service account running sqlservr.exe. Dangerous because if the process fails to start (eg> out of memory), you may orphan messages in the queue since there will be no process to process them.

You might want to look at the External Activator sample (linked from www.sqlservicebroker.com) and adapt it or build something similar.

I don't know what you mean by "triggers processing the messages in the queue". SQL Server 2005 has no triggers for queues.

Rushi

|||

We use this mechanism to farm out data warehouse ETL mechanism and it works fairly well.

Once you start up SSIS, though, your package should be the one that reaches back into the DB and pulls messages off the queue. Make sure to put your transaction logic, error handling, etc. in the SSIS control flow, taking into account what should be done with messages in the context of your package.

There's a ton of SSIS overhead (at least compared with pulling a message off a queue), so if you're getting a fair number of messages, you don't want to start up the engine each time you want to process a message. We opted to call out to dtexec rather than have a separate service running, although I think that's a matter of preference (and your level of comfort/concern of running external processes from SQL vs. having to install/manage other services).

|||

Couldn't you just create a SQLCLR stored procedure to execute the package directly & with full transactional support? I.e. Have an activated stored proc, authored in C#, executing the SSIS package without doing any "shelling out".

Something like the link below implemented as a SQLCLR proc?

http://www.codeproject.com/useritems/CallSSISFromCSharp.asp?df=100&forumid=309846&exp=0&select=1519306

Calling SSIS packages from a Service Broker Queue

I would like to call an SSIS package from a Service Broker Queue.

There is one way that I am aware of -

Using xp_cmdshell from within an activation stored procedure and using DTEXEC.

Is there a more elegant way of executing an SSIS package from within SSB?

Also, I am not interested in writing a .NET external activator to process my messages in the queue. I would like this operation to be strictly database oriented. Having said this, I am also trying to avoid triggers processing the messages in the queue.

Thank you!

SQLCLR with unsafe assembly (in order to call Process.Start()) would be slightly better option than xp_cmdshell. Even then, starting external processes from an activated stored procedure is limiting and dangerous. Limiting because you can only start the external application as the service account running sqlservr.exe. Dangerous because if the process fails to start (eg> out of memory), you may orphan messages in the queue since there will be no process to process them.

You might want to look at the External Activator sample (linked from www.sqlservicebroker.com) and adapt it or build something similar.

I don't know what you mean by "triggers processing the messages in the queue". SQL Server 2005 has no triggers for queues.

Rushi

|||

We use this mechanism to farm out data warehouse ETL mechanism and it works fairly well.

Once you start up SSIS, though, your package should be the one that reaches back into the DB and pulls messages off the queue. Make sure to put your transaction logic, error handling, etc. in the SSIS control flow, taking into account what should be done with messages in the context of your package.

There's a ton of SSIS overhead (at least compared with pulling a message off a queue), so if you're getting a fair number of messages, you don't want to start up the engine each time you want to process a message. We opted to call out to dtexec rather than have a separate service running, although I think that's a matter of preference (and your level of comfort/concern of running external processes from SQL vs. having to install/manage other services).

|||

Couldn't you just create a SQLCLR stored procedure to execute the package directly & with full transactional support? I.e. Have an activated stored proc, authored in C#, executing the SSIS package without doing any "shelling out".

Something like the link below implemented as a SQLCLR proc?

http://www.codeproject.com/useritems/CallSSISFromCSharp.asp?df=100&forumid=309846&exp=0&select=1519306

Calling Reporting Services from SSIS

Hi

I have created a packages which pull and push the data to SAP server.

I want to create a report every day and send that report to the manager.

For the same i want to call reporting services in my SSIS package.

I know i can write a SQL script and export the report in excel but i want to use Reporting services.

Have any one call reporting services from ssis.

bhalchandra.kunte wrote:

Hi

I have created a packages which pull and push the data to SAP server.

I want to create a report every day and send that report to the manager.

For the same i want to call reporting services in my SSIS package.

I know i can write a SQL script and export the report in excel but i want to use Reporting services.

Have any one call reporting services from ssis.

What do you mean by "call Reporting Services"?

RS is a web service that you can call from anythig that supports calling web services. including SSIS.

-Jamie

|||

In SSIS we can call/work on analysis within SSIS package

by using tools which are provided in toolbox...

--ToolBox -- Control Flow Items -- Analysis Services Execute DDL Task -- Provides ability to process DDL query statement against Analysis Services.

--ToolBox -- Control Flow Items--Analysis Services Processing Task -- Provides ability to process objects like cubes

Can we work / call Reporting Services within SSIS package?

|||

bhalchandra.kunte wrote:

In SSIS we can call/work on analysis within SSIS package

by using tools which are provided in toolbox...

--ToolBox -- Control Flow Items -- Analysis Services Execute DDL Task -- Provides ability to process DDL query statement against Analysis Services.

--ToolBox -- Control Flow Items--Analysis Services Processing Task -- Provides ability to process objects like cubes

Can we work / call Reporting Services within SSIS package?

There are no tasks within SSIS to do anything with Reporting Services. However, RS has an API which you can call from SSIS using the Web Service Task or (I suspect) a Script Task.

It would help if you could be more specific about what you want to do.

-Jamie

|||

Thanks

I have already tried that but not managed to get it done.

I will try again.

Thanks again.

|||did anyone sucessfully run the .rdl file from ssis|||

What do you mean by "run an .rdl file". What exactly do you want to do?

-Jamie

|||i want to run MS Reporting Services file from SSIS Task and subscribe the reportfile.rdl.data file to a local or network folder.|||

I got working, I scheduled a report on report server and in turn it creates a job on sql agent and from ssis I used execute job task and subscribed report file to a folder. i have email task to send the report as an attachment.

Thanks for the help.

|||I managed to generate a SSRS report directly from a script task within SSIS :

i set varSSRS_URL, varSSRS_LOGIN, varSSRS_PASSWORD & varSSRS_DOMAIN in the DTSCONFIG file

varSSRS_URL should be the first part of your SSRS Server URL : http://localhost/ReportServer

varSSRS_LOGIN/varSSRS_PASSWORD/varSSRS_DOMAIN : A windows user log/pass allowed to generate reports on the server (used for authentification)

//Sample call
SaveFile(Dts.Variables("varSSRS_URL").Value.ToString() + "?%2fYOUR_SSRS_FOLDER%2fYOUR_REPORT_NAME&rs:Command=Render&rs:Format=EXCEL", outpath + "FILENAME.xls")

//Note that you can replace "EXCEL" by "CSV" or "PDF" or any other supported export format

//The get & save file method
Protected Sub SaveFile(ByVal url As String, ByVal localpath As String)
Dim loRequest As System.Net.HttpWebRequest
Dim loResponse As System.Net.HttpWebResponse
Dim loResponseStream As System.IO.Stream
Dim loFileStream As New System.IO.FileStream(localpath, System.IO.FileMode.Create, System.IO.FileAccess.Write)
Dim laBytes(256) As Byte
Dim liCount As Integer = 1

Try
loRequest = CType(System.Net.WebRequest.Create(url), System.Net.HttpWebRequest)
loRequest.Credentials = New System.Net.NetworkCredential(Dts.Variables("varSSRS_LOGIN").Value.ToString(), Dts.Variables("varSSRS_PASSWORD").Value.ToString(), Dts.Variables("varSSRS_DOMAIN").Value.ToString())
loRequest.Timeout = 1000 * 60 * 15 'timeout 15 minutes
loRequest.Method = "GET"
loResponse = CType(loRequest.GetResponse, System.Net.HttpWebResponse)
loResponseStream = loResponse.GetResponseStream
Do While liCount > 0
liCount = loResponseStream.Read(laBytes, 0, 256)
loFileStream.Write(laBytes, 0, liCount)
Loop
loFileStream.Flush()
loFileStream.Close()
Catch ex As Exception

End Try

End Sub|||I got an error on 'Save File'. It said declaration expected. I maybe missing some import statement that you have.|||here is my import list :

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime|||Well I have the exact same thing. Did you have to declare SaveFile?|||please post your full class and FULL error message, it will be easier to help you|||

I am using what you had posted:

//Sample call
SaveFile(Dts.Variables("varSSRS_URL").Value.ToString() + "?%2fYOUR_SSRS_FOLDER%2fYOUR_REPORT_NAME&rs:Command=Render&rs:Format=EXCEL", outpath + "FILENAME.xls")

//Note that you can replace "EXCEL" by "CSV" or "PDF" or any other supported export format

//The get & save file method
Protected Sub SaveFile(ByVal url As String, ByVal localpath As String)
Dim loRequest As System.Net.HttpWebRequest
Dim loResponse As System.Net.HttpWebResponse
Dim loResponseStream As System.IO.Stream
Dim loFileStream As New System.IO.FileStream(localpath, System.IO.FileMode.Create, System.IO.FileAccess.Write)
Dim laBytes(256) As Byte
Dim liCount As Integer = 1

Try
loRequest = CType(System.Net.WebRequest.Create(url), System.Net.HttpWebRequest)
loRequest.Credentials = New System.Net.NetworkCredential(Dts.Variables("varSSRS_LOGIN").Value.ToString(), Dts.Variables("varSSRS_PASSWORD").Value.ToString(), Dts.Variables("varSSRS_DOMAIN").Value.ToString())
loRequest.Timeout = 1000 * 60 * 15 'timeout 15 minutes
loRequest.Method = "GET"
loResponse = CType(loRequest.GetResponse, System.Net.HttpWebResponse)
loResponseStream = loResponse.GetResponseStream
Do While liCount > 0
liCount = loResponseStream.Read(laBytes, 0, 256)
loFileStream.Write(laBytes, 0, liCount)
Loop
loFileStream.Flush()
loFileStream.Close()
Catch ex As Exception

End Try

End Sub

As soon as I paste it, I get an error on SaveFile, it says, "declaration expected."

sql

Calling Reporting Services from SSIS

Hi

I have created a packages which pull and push the data to SAP server.

I want to create a report every day and send that report to the manager.

For the same i want to call reporting services in my SSIS package.

I know i can write a SQL script and export the report in excel but i want to use Reporting services.

Have any one call reporting services from ssis.

bhalchandra.kunte wrote:

Hi

I have created a packages which pull and push the data to SAP server.

I want to create a report every day and send that report to the manager.

For the same i want to call reporting services in my SSIS package.

I know i can write a SQL script and export the report in excel but i want to use Reporting services.

Have any one call reporting services from ssis.

What do you mean by "call Reporting Services"?

RS is a web service that you can call from anythig that supports calling web services. including SSIS.

-Jamie

|||

In SSIS we can call/work on analysis within SSIS package

by using tools which are provided in toolbox...

--ToolBox -- Control Flow Items -- Analysis Services Execute DDL Task -- Provides ability to process DDL query statement against Analysis Services.

--ToolBox -- Control Flow Items--Analysis Services Processing Task -- Provides ability to process objects like cubes

Can we work / call Reporting Services within SSIS package?

|||

bhalchandra.kunte wrote:

In SSIS we can call/work on analysis within SSIS package

by using tools which are provided in toolbox...

--ToolBox -- Control Flow Items -- Analysis Services Execute DDL Task -- Provides ability to process DDL query statement against Analysis Services.

--ToolBox -- Control Flow Items--Analysis Services Processing Task -- Provides ability to process objects like cubes

Can we work / call Reporting Services within SSIS package?

There are no tasks within SSIS to do anything with Reporting Services. However, RS has an API which you can call from SSIS using the Web Service Task or (I suspect) a Script Task.

It would help if you could be more specific about what you want to do.

-Jamie

|||

Thanks

I have already tried that but not managed to get it done.

I will try again.

Thanks again.

|||did anyone sucessfully run the .rdl file from ssis|||

What do you mean by "run an .rdl file". What exactly do you want to do?

-Jamie

|||i want to run MS Reporting Services file from SSIS Task and subscribe the reportfile.rdl.data file to a local or network folder.|||

I got working, I scheduled a report on report server and in turn it creates a job on sql agent and from ssis I used execute job task and subscribed report file to a folder. i have email task to send the report as an attachment.

Thanks for the help.

|||I managed to generate a SSRS report directly from a script task within SSIS :

i set varSSRS_URL, varSSRS_LOGIN, varSSRS_PASSWORD & varSSRS_DOMAIN in the DTSCONFIG file

varSSRS_URL should be the first part of your SSRS Server URL : http://localhost/ReportServer

varSSRS_LOGIN/varSSRS_PASSWORD/varSSRS_DOMAIN : A windows user log/pass allowed to generate reports on the server (used for authentification)

//Sample call
SaveFile(Dts.Variables("varSSRS_URL").Value.ToString() + "?%2fYOUR_SSRS_FOLDER%2fYOUR_REPORT_NAME&rs:Command=Render&rs:Format=EXCEL", outpath + "FILENAME.xls")

//Note that you can replace "EXCEL" by "CSV" or "PDF" or any other supported export format

//The get & save file method
Protected Sub SaveFile(ByVal url As String, ByVal localpath As String)
Dim loRequest As System.Net.HttpWebRequest
Dim loResponse As System.Net.HttpWebResponse
Dim loResponseStream As System.IO.Stream
Dim loFileStream As New System.IO.FileStream(localpath, System.IO.FileMode.Create, System.IO.FileAccess.Write)
Dim laBytes(256) As Byte
Dim liCount As Integer = 1

Try
loRequest = CType(System.Net.WebRequest.Create(url), System.Net.HttpWebRequest)
loRequest.Credentials = New System.Net.NetworkCredential(Dts.Variables("varSSRS_LOGIN").Value.ToString(), Dts.Variables("varSSRS_PASSWORD").Value.ToString(), Dts.Variables("varSSRS_DOMAIN").Value.ToString())
loRequest.Timeout = 1000 * 60 * 15 'timeout 15 minutes
loRequest.Method = "GET"
loResponse = CType(loRequest.GetResponse, System.Net.HttpWebResponse)
loResponseStream = loResponse.GetResponseStream
Do While liCount > 0
liCount = loResponseStream.Read(laBytes, 0, 256)
loFileStream.Write(laBytes, 0, liCount)
Loop
loFileStream.Flush()
loFileStream.Close()
Catch ex As Exception

End Try

End Sub|||I got an error on 'Save File'. It said declaration expected. I maybe missing some import statement that you have.|||here is my import list :

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime|||Well I have the exact same thing. Did you have to declare SaveFile?|||please post your full class and FULL error message, it will be easier to help you|||

I am using what you had posted:

//Sample call
SaveFile(Dts.Variables("varSSRS_URL").Value.ToString() + "?%2fYOUR_SSRS_FOLDER%2fYOUR_REPORT_NAME&rs:Command=Render&rs:Format=EXCEL", outpath + "FILENAME.xls")

//Note that you can replace "EXCEL" by "CSV" or "PDF" or any other supported export format

//The get & save file method
Protected Sub SaveFile(ByVal url As String, ByVal localpath As String)
Dim loRequest As System.Net.HttpWebRequest
Dim loResponse As System.Net.HttpWebResponse
Dim loResponseStream As System.IO.Stream
Dim loFileStream As New System.IO.FileStream(localpath, System.IO.FileMode.Create, System.IO.FileAccess.Write)
Dim laBytes(256) As Byte
Dim liCount As Integer = 1

Try
loRequest = CType(System.Net.WebRequest.Create(url), System.Net.HttpWebRequest)
loRequest.Credentials = New System.Net.NetworkCredential(Dts.Variables("varSSRS_LOGIN").Value.ToString(), Dts.Variables("varSSRS_PASSWORD").Value.ToString(), Dts.Variables("varSSRS_DOMAIN").Value.ToString())
loRequest.Timeout = 1000 * 60 * 15 'timeout 15 minutes
loRequest.Method = "GET"
loResponse = CType(loRequest.GetResponse, System.Net.HttpWebResponse)
loResponseStream = loResponse.GetResponseStream
Do While liCount > 0
liCount = loResponseStream.Read(laBytes, 0, 256)
loFileStream.Write(laBytes, 0, liCount)
Loop
loFileStream.Flush()
loFileStream.Close()
Catch ex As Exception

End Try

End Sub

As soon as I paste it, I get an error on SaveFile, it says, "declaration expected."

Calling DTS package with Send Mail object from VB

I have several processes that call DTS packages (SQL Sever 7) from VB6, and they work fine. The problem is that if there is a "Send mail" task in the package, it won't send the email if called from VB.

If I run the package directly through enterprise manager, the email gets sent, but if run through VB it does not.

Any ideas?

The vb code is below.

Thanks.

Lionel

Private Sub Command1_Click()

Dim objPackage As DTS.Package
Const DTSReposFlag_UseTrustedConnection = 256
Dim tServer As String

Set objPackage = New DTS.Package
tServer = "SQLBOX"
objPackage.LoadFromSQLServer tServer, "", "", DTSReposFlag_UseTrustedConnection, "", "", "", "dts_test"
objPackage.Execute
Set objPackage = Nothing

End SubPlease, post your DTS task that includes a call to xp_sendmail.|||I'm not actually sure how to post the DTS Package. When I script it through enterprise manager (Open the package and Save AS) then it saves in a unreadable format

(e.g. DZ b% o?`;\ D|Jkjìg).

Please could you let me know.

Thanks,

lwakersql