Re: Problems launching DTS package from Stored Procedure

From: ATJaguarX (mrjboner_at_yahoo.com)
Date: 12/15/04 

Date: Wed, 15 Dec 2004 11:50:54 -0600

The SQL Service is running under the Domain Admin account which has access
to both directories and files.

I cannot run enterprise manager on our server because it is not installed.
Can enterprise manager be installed on the server?

"Darren Green" <darren.green@reply-to-newsgroup-sqldts.com> wrote in message
news:N4NA4OBi42vBFwks@sqldts.com...
> In message <uIbX$pi4EHA.3420@TK2MSFTNGP10.phx.gbl>, ATJaguarX
> <mrjboner@yahoo.com> writes
>>I have a stored procedure create for the sole purpose of launching a DTS
>>package. I can post the SQL later if necessary.
>>
>>The problem is that I have multiple DTS packages that I would like to
>>execute in a SP. The sole purpose for this is that we have data stored
>>and
>>being used in a foxpro database and I am writing Reports and would like to
>>have the data in SQL Server. So I created a SP that will accept a DTS
>>package name as a parameter, executes the package and continues on.
>>Well...
>>my DTS package consists of a table drop, a table creation, a connection to
>>Foxpro, a query to retrieve the data and a connection to SQL Server.
>>
>>If I run the DTS package directly from Enterprise manager, it executes
>>correctly. It drops the table (if it exists), creates it, queries the
>>data
>>and drops it into the table. Pretty simple considering it was all done
>>from
>>the "Import Wizard". The problem arises when I use my stored procedure to
>>execute the DTS package. It gets as far as dropping and recreating the
>>table, but never retrieves the data or populates the table. The stored
>>procedure does not error out or give me any signs of problems. In
>>actuality, it works perfectly fine for a VERY similiar DTS package for a
>>different table (which was also made from the "Import Wizard"). Both
>>packages do the same thing (drop, create, import) except they hit
>>different
>>tables.
>>
>>Does anyone know why this would happen? Any help would be GREATLY
>>appreciated.
>>
>
> I would not expect the store procedure to fail if there was an error
> during package execution, instead you need to check the log or the step
> error information post execution to find this out.
>
> I suspect the error is around security and location contexts. When running
> via the sp the package is executed under the security context of the SQL
> Server service account. So what is this account and does it have access to
> the Foxpro file? The package is also being executed on the SQL Server
> machine itself.
>
> The typical development scenario means, that when you "test" through
> Enterprise Manager it executes the package under the context of you, as
> the logged on user, and on your local workstation, not the server.
> So taking that into account it is not normally a very valid test. To do a
> more realistic test you need to log into the server console as the SQL
> Server service account and use Enterprise Manager there.
>
>
> --
> Darren Green (SQL Server MVP)
> DTS - http://www.sqldts.com
>
> PASS - the definitive, global community for SQL Server professionals
> http://www.sqlpass.org
>

Relevant Pages

  • Re: DTS schedule problem
    ... I found out that the package, sql server agent were running under the SQL ... Who owns the scheduled SQL Agent job? ...
    (microsoft.public.sqlserver.dts)
  • Re: SSIS as part of scheduled job fails
    ... An SSIS package does not run when you call the SSIS package from a SQL ... Microsoft SQL Server Management Studio ...
    (microsoft.public.sqlserver.dts)
  • Re: SSIS package wont run in production server
    ... I also would like to mention that on my production server, ... When you run the setup of SQL 2005, it is indeed possible to install only ... I have a package that I developed on my local machine and runs well. ...
    (microsoft.public.sqlserver.dts)
  • Re: SSIS as part of scheduled job fails
    ... deploying to SQL 2005. ... This server has SQL 2000 as the default instance and a ... Encryptall... ... when you installed the package on the server? ...
    (microsoft.public.sqlserver.dts)
  • Re: SQL JOB
    ... Another option could be to use Master/Target server configuration. ... Vyas, MVP (SQL Server) ... I created a SQL job which executes a Stored procedure. ... connection to one of the server/database fails, ...
    (microsoft.public.sqlserver.security)
Loading