I re-created my DTS package in SQL Server 2005. I now execute the package
using: [ Exec Master..xp_cmdshell 'DTExec /f C:\Data\packagename.dtsx' ]

It is working fine however, is there another way of executing this package
in SQL Server 2005 without having to go to xp_cmdshell and run DTExec?

Okay, so the [ packagename.dtsx ] is external but let's say it was internal.
Is there a way to execute a package in one step instead of 2 as above?

There is no native T-SQL command to execute a package, You coudl probably
write one through the CLR integration.

I don't think executing packages from T-SQL is the best idea. It is more
common to call them via secheduled jobs. You can aways start a job from
T-SQL. This of course depends on yoru requirements.


--
Darren Green
http://www.sqldts.com
http://www.sqlis.com


"Ravi Persaud" <rkcsp (AT) yahoo (DOT) com> wrote

sorry dear i am helping you exactly but telling you what i do in these kind
of situations,
run sql profiler
now execute any thing either by right clicking or some other you knows then
see in profiler for its backend command, note it and use every where as t-sql

"Ravi Persaud" wrote:

Thank you Darren,
I will have to play around with SQL Server 2005 since I am new to it. I will
schedule a job and try to execute it via T-Sql as you suggested. I will need
some time before I can update you on this.

Thank you Imran,
I don't even know how to use the SQL Profiler but I will try so I can find
an alternative solution. It I have any problems I will post again.

Okay, I discovered what the SQL Profiler is all about. For execution of an
internal SSIS package I used:

[ Exec msdb.dbo.sp_dts_getpackage
N'NewPkg','00000000-0000-0000-0000-000000000000' ] but for some reason this
does not work.

I created a job and used the profiler to check the t-sql syntax. This is
what I came up with:

[ EXEC msdb.dbo.sp_start_job @job_name=N'packagename' ] this works fine.

And my original t-sql statement:

[ Exec Master..xp_cmdshell 'dtexec /f c:\data\packagename.dtsx' ] works as
well.

So I am quite happy with the help I received on this site. It was my first
post and I can say it was a great experience.

Thanks again to Darren and Imran.

The fastest (and best) way to execute an SSIS package can be found here:

http://msdn2.microsoft.com/en-us/library/ms136090.aspx

Hello Ravi,

I have come into this discussion late and you have not included the original
posters Q with your reply so I am unsure about what the question was but
I am not sure that the way described (programmatically) in the article could
be described as the best and the fastest. it depends on the situation as
to which way is best and there are a great many factors to think about when
talking about speed, this for instance

Comparing Overhead On The Execution Methods
(http://www.sqlis.com/default.aspx?84)


Allan

Thank you for stopping by Allan.
I wanted to execute an SSIS package at the click of a button from a web page.

I came across 3 ways of doing this, all from the click on a button. The
first was using DTExec, the second was by executing an unscheduled job and
the third was by using SqlServer.Dts.Runtime in Asp.Net 2.0.

I found that the last option was the fastest and speed is what I am looking
for.

What are your suggestions? I would really appreciate your insite.
Thank you for your intesest.

Hello Ravi,

I would do it programmatically in that case as well. I would use the SSIS
object model to do the execution.

Allan