Running a SSIS Package using DtsExec.exe

Question

Running a SSIS Package using DtsExec.exe

greg1882

SSC-Addicted

Points: 454
More actions
April 8, 2010 at 10:06 am

#220293

I am experiencing problems running a SSIS package on 2008 via DtsExec.exe via a simple c# client.
My client uses Windows impersonation for a valid windows account that has access to the database that my package is based on.
My client spawns a new process that calls the DtsExec.exe
When I run the client with impersonation I get the following error,
"Could not create DTS.Application because of error 0x80040154"
If I run the client as my account the it's fine.
Do I need to add my impersonated account to any Windows group/role.
Any suggestions?
Thanks.
Greg.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

Quick question.. Are you trying to run an SSIS package or a DTS pacakge. Because DTSEXEC is for DTS and DTEXEC is for SSIS. Please elaborate.

CEWII

greg1882 SSC-Addicted Points: 454 More actions · Answer 2

Apologies, typo mistake.

I'm running a SSIS package using DTExec.exe.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

A quick Google search reveals this:

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/18ed000a-6e60-4687-a923-3db630b04226

Does that fit?

CEWII

greg1882 SSC-Addicted Points: 454 More actions · Answer 4

Thanks for the reply.

I've read that article and similar ones several times over. I've even tried implementing the sugessted code snippet from http://support.microsoft.com/kb/889251.

Thought I'd try this forum as an alternate from a SSIS security point of view.

Thanks.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

I reviewed that KB and have an idea of what you are doing. When you are using that other user (the one you are impersonating) does it have access to the *.dtsx file, and does it have access to the databases. Also, that error works out to "Class Not Registered" (at least by my search). Does your package use Excel or some other source/destination? AND is your web server 64-bit?

CEWII

greg1882 SSC-Addicted Points: 454 More actions · Answer 6

A bit more background of what I have tested, my client app is a simple console application.

I've dumbed my SSIS package right down so it just executes a query statement for proof of concept purposes.

I have run the package from BI Dev studio (opened the devenv executable with the windows "Run As" option and used the windows account my client intends to impersonate). The SSIS package executes successfully in that context.

I've added the impersonated account to have Windows Admininstrator rights on the box to eliminate a potential security issue on the file system.

I am unable to use SQL agent as a possible solution as all my packages must be opened from the file system and not the database.

I would have thought what I am trying to do is common practice for running SSIS packages.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 7

Why is calling a package from a file system a disqualifier to use SQL Agent?

You are calling this console app on the machine that has SSIS installed or from another machine?

It runs fine from BIDS when running as that user. Does your console app call the DTSX from the same directory that BIDS is working from?

Calling a DTSX from an app is not UNcommon but it is not the norm. Calling them from SQL Agent is by FAR the norm.

CEWII

greg1882 SSC-Addicted Points: 454 More actions · Answer 8

Unfortunately due to the DB policies where I work SQL Agent is not an option that is available to me.

You are calling this console app on the machine that has SSIS installed or from another machine? - from a machine that has SSIS installed.

It runs fine from BIDS when running as that user. Does your console app call the DTSX from the same directory that BIDS is working from? Yes.

Thanks Elliot for your help so far.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 9

You are welcome.

You had me a little confused, it sounded like you were saying that because the packages are in the file system that there was some reason that you couldn't use SQL Agent. It sounds like the reason is policy, can you elaborate or the policy issue, it is often possible to satisfy a policy with a simple workaround that makes everyone happy.

As far as the other stuff it is pretty baffling. Security for accessing the package seems to be resolved by the impersonated user being in the Administrators group. In 2005 that is usually enough to give you sysadmin rights on the local SQL Server so it sounds like permissions is probably not the issue. The package runs ok in BIDS but not from your app. I'm thinking it might have to do with the way the process is instantiated.

I might have a different solution.

Just looking at some of my code that I use for samples I think you might want to do it differently. You can do impersonation by doing something like:

http://vbnotebookfor.net/vbnb_impersonation.htm

And you can open and call the SSIS with something like this:

Dim _oAppl As New Microsoft.SqlServer.Dts.Runtime.Application

Dim _oPackage As Microsoft.SqlServer.Dts.Runtime.Package

Dim _oExecResult As Microsoft.SqlServer.Dts.Runtime.DTSExecResult

_oPackage = _oAppl.LoadPackage("C:\path_to_ssis_package\ssis_package_name.dtsx", Nothing)

_oExecResult = _oPackage.Execute()

You would also need to add a reference to Microsoft.SQLServer.ManagedDTS which is probably located at:

c:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll

Just a thought.

CEWII