Re: Unable to get Sql Server Agent Job to run a SSIS Package

Tech-Archive recommends: Speed Up your PC by fixing your registry

Allan,

I did as you said and found my problem. When creating the package I was
wrting the contents of a table out to an Excel file that was mapped to a
shared drive. Since I was able to browse to the location without a problem
and when I ran the package I had no problem I thought everything was good for
that step. When I ran the package from a job using dtexec /SQL I was able to
see that the job could not find the path requested to store the excel file. I
changed the path to a UNC path and reran the job and all went well. I went
back into the job changed from a "cmdExec" back to a SSIS Package Exec and
the job again worked fine.

Thanks so much for the tip on using dtexec to display more information to
resolve this error...

"Allan Mitchell" wrote:

OK So we are going to need a better error than that. Are you using the
SSIS job step? If you are can you change to a cmdExec job step, that
usually gives better info.

Can you go to the server and execute this in Bids or wherever and see
what you get?

--


Allan Mitchell
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
http://www.konesans.com




"AOTX San Antonio" <ElTejano@xxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:28BC8445-3AA9-452E-AC8C-3149BEC1BB0E@xxxxxxxxxxxxx:

I have run a TASk against a Sql Server 2005 (Enterprise Edition) database in
order
to create a SSIS package. The Export was a simple Copy of a table to an
Excel file.
I have created the SSIS package with a DTSProtection Level of
EncryptSensitiveWithUserKey as well as ServerStorage. The SSIS package runs
fine both via BIDS and via Mgt Studio in either Protection level. The two
users that I have tried to run the SSIS package have Sysadmin rights.

The problem I have is that I can't get a scheduled job to run it. My
scheduled job has only 2 steps - One to delete the excel file and a step
that runs
the package. When I right click in Mgt Studio (SQL Agent) and choose "Start
Job" I get this error USing ServerStorage as the Protection Level:


Date 1/10/2007 11:34:07 AM
Log Job History (Gorilla - SSIS - NSTDB Course Information to Excel)

Step ID 2
Server LEGHORN
Job Name Gorilla - SSIS - NSTDB Course Information to Excel
Step Name Run SSIS PAckage - Gorilla - SSIS - NSTDB Non-Encrypted Export
Course Information To Excel
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: JUNGLE\rmayfield. The package execution failed. The step
failed.


I have run the job using a Proxy that was created via the instructions in
the book
"MS Sql Server 2005 Integration Services" Chapter 18 pg. 402.
I have tried running the job as the user who created the Package and nothing
seems to work...I have run the job with a user I setup with the database
roles SqlAgentOperatorRole, SqlAgentUserRole, db_dtsadmin.

I have SSIS loaded on the server I am working with and I am logged into the
same server when I create the package and try and run it...


My ultimate goal is to take My Sql Server 2000 DTS packages and convert them
to
SSIS packages that get run via a Sql Server Agent Job. We do not do Data
warehousing
so SSIS is a little excessive for the task we need to perform which is copy
data
from one database to another or copy data from the database out to a file.
I do not want to continue with 2000 DTS packages and would rather convert
them to SSIS packages as I am sure in a later version of Sql Server all
support for DTS will be dropped.

I need to upgrade to Sql Server 2005 but this migration of DTS packages has
brought the whole migration to a halt...I can't count how many articles and
Newsgroups I have read and still nothing seems to work .


.

Relevant Pages

  • 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: Run a ssis package on SQL Server 2005 Error
    ... Failure saving package. ... This is most likely a known issue in SQL Server 2005, ... Set the SSIS Package ProtectionLevel property to ServerStorage ...
    (microsoft.public.sqlserver.dts)
  • RE: Run and Edit DTS in SQL Server 2005
    ... Charles Kangai, MCT, MCDBA ... we've the schedule to migrate all DTS to SSIS but the run DTS in ... Packages folder in Solution Explorer and choose New SSIS Package). ... The SSIS package you just created will then go and execute your DTS ...
    (microsoft.public.sqlserver.dts)
  • Re: SSIS - can SSIS pkg recognize a drive letter within SMS job?
    ... The one alternative I've been wondering about is setting the default package ... set a starting path from within an SSIS package script. ... What is the result if you modify the FullPath value? ... indicates that your SQL Server 2005 has no service packs installed. ...
    (microsoft.public.sqlserver.dts)
  • Saving a file on remote server
    ... I have a DTS package that queries a table and generates an Excel file. ... excel file is saved on a remote file server. ... Can a package run in a different security context from the Service Agent (or ...
    (microsoft.public.sqlserver.dts)