Hi all,
I've spent about 3 days trying and hitting dead ends on how to execute an
SSIS package programmatically. Anyway, after failing with SQL Assemblies and
not liking the security work arounds for xp_cmdshell I am down to create a
whole job on the server just to kick off this one SSIS package or finding a
sucessful way to make a web service launch it. The later is a cleaner
solution but but when I execute the thing I get a DTSExecResult return code
of Failed. I had MAJOR issues trying to get file permissions on win 2003
which was another issue and still not entirely resolved (had to create a
network file share to launch it locally??), but it still returned Failed. If
I launch the package locally, no problem, so I'm not sure what gives with why
I'm getting the ever so helpful Failed message.

Does anyone have any suggestions? SOmething that I thought would take
30minutes max to come up with a good solution has turned into a several day
chore. I'd really just like to get this simple, locally running web service
to call the thing and I'd be happy...

Thanks all,
Jason!!

Hi Jason,

To run a package programmatically, you need to reference the library
Microsoft.SqlServer.ManagedDTS.dll. You then need to create instances of the
objects Application and Package:

Imports Microsoft.SqlServer.Dts.Runtime

dim oApp as New Application
dim oPackage as New Package

You can then call one of the following Application object's methods:
LoadPackage, LoadFromSQLServer or LoadFromDTSServer, depending on whether
your package is stored in a file, in MSDB or in SSIS Package Store.

For example,

oPackage = oApp.LoadPackage("c:\Data\MyPackage.dtsx", nothing)

Now execute the package:

dim oResults as DTSExecResult

oResults = oPackage.Execute()



That's it!

You raise other issues such as not being able to access the SSIS service
remotely. By default, the SSIS service is not accessible remotely. Go to
Administrative Tools, Component Services, Component Services, expand until
you see DCOM Config. Expand this and select MsDtsServer. Go to properties. On
the Security tab under Launch and Activation Permissions, click Edit and
configure the component for remote access.

It seems you have other security issues with scheduling. You can look for an
earlier post by me in which I explain the issues around scheduling and how to
do it so it works.

Hope this helps.

Charles Kangai, MCT, MCDBA
Author of Learning Tree's 4-day course: "SQL Server 2005 Integration
Services" http://www.learningtree.com/courses/134.htm
Author of Learning Tree's 4-day course: "SQL Server Reporting Services"
http://www.learningtree.com/courses/523.htm
email: charles at kangai.demon.co.uk






"JasonBarton" wrote:

Hi Charles,
Thanks for the feedback. I found the DCOM service and set it to be used by
Network Service which is the user that the ASP process is attached to these
days apparently. That didn't do the trick though. I read somewhere that I
have to set database login permissions with something resembling the
following:
osql -E -S %SERVER%\%INSTANCE% -Q "sp_grantlogin 'NT AUTHORITY\NETWORK
SERVICE'"
osql -E -S %SERVER%\%INSTANCE% -d %DATABASE% -Q
"sp_grantdbaccess 'NT AUTHORITY\NETWORK SERVICE'"
osql -E -S %SERVER%\%INSTANCE% -d %DATABASE% -Q
"sp_addrolemember 'db_owner', 'NT AUTHORITY\NETWORK SERVICE'"

Just to make it clear, I have a web service running on the same machine as
the database which uses the components you already mentioned. From those
components I'm getting a DTSExecResult enumeration back with the Failure flag
set.

Whew, what a bit of work... If you know anything else about making this
work, I'd really appreciate it. I'm sure this is pretty straight forward, but
finding the knowledge has been a bit of a task.

p.s. I'm not sure what scheduling issues you're talking about. I'm not
trying to scheduling anything. In fact the reason for my kicking thigns off
with a web service is to avoid scheduling.
Thanks again.

"Charles Kangai" wrote:

I got it. Like the osql commands below, I just went through the Management
Studio and added Network Service as a user and a login for the database that
was being worked with and gave the user db_reader and db_writer roles and
that was it. I undid the permission changes in the DCOM service and it still
ran. I don't know what the role of those services are. Maybe if the web app
is from a different box they would be used, but it doesn't appear that right
now those services are being used for an asp/IIS/network service call into
the local db.

"Charles Kangai" wrote:

Yes, the MsDtsServer configuration is for remote users, i.e. on a different
box. I obviously misunderstood your scenario and thought you were accessing
SSIS from a remote client.

But I am glad it was a simple security issue and you gave the necessary
permissions. The code you used also should have worked, since you were giving
the user db_owner access. Maybe there was something wrong in the commandline
you used.

Charles Kangai, MCT, MCDBA
Author of Learning Tree's 4-day course: "SQL Server 2005 Integration
Services" http://www.learningtree.com/courses/134.htm
Author of Learning Tree's 4-day course: "SQL Server Reporting Services"
http://www.learningtree.com/courses/523.htm
email: charles at kangai.demon.co.uk

"JasonBarton" wrote: