Re: SSIS as part of scheduled job fails

If you use the SQL 2005 Management Studio to connect to your 2005 server
(connect to Integration Services, not Database Engine), can you find your
package under the Stored Packages? Are you deploying as SQL stored or File
based? Can you manually execute the package and it succeed? Does it always
fail? Can you post some specific errors from the Event Log or SSIS package
history?





"Joe S." <joecrew@xxxxxxxxxxxxxx> wrote in message
news:36EBCCC7-B24E-4AF9-AE8A-5AC394795A02@xxxxxxxxxxxxxxxx
deploying to SQL 2005. This server has SQL 2000 as the default instance
and a
named SQL 2005 instance. Yes, I was prompted for a password and I tried
both
Encryptall... and EncryptSensative...

"Matt Yeager" wrote:

This might be a silly question, but...are you trying to deploy to a SQL
2000
Server? If not, meaning it is SQL 2005, were you prompted for a password
when you installed the package on the server? Which ProtectionLevel did
you
use in the package?

-Matt Yeager


"Joe S." <joecrew@xxxxxxxxxxxxxx> wrote in message
news:902401D3-AD5D-488B-9D8F-CB15374B74C5@xxxxxxxxxxxxxxxx
I tried the steps you outline and now I get an error that the package
cannot
be loaded...the client cannot establish a connection...Encryption not
supported on SQL Server. What now.

"Matt Yeager" wrote:

I posted this in another thread, but this looks like the same issue so
I'm
copying the message here:

I encountered the same problem, even on the same server upon
deployment.
I ended up contacting Microsoft and opening a support case. After a
couple
of hours on the phone, we found that if the SSIS Package's Security
setting
"ProtectionLevel" was set to EncryptAllWithUserKey or
EncryptSensativeWithUserKey that the passwords would actually be lost.
This
has to do with the fact that the SQL Server Agent process on your
server
is
running as a different user and cannot validate the user key
basically.
What
I ended up having to do is switch the Security ProtectionLevel to use
EncryptAllWithPassword or EncryptSensativeWithPassword and specify a
password for the package. I then re-deployed to SQL.

How I scheduled the Job also had to change. I could no longer specify
my
package as a SSIS Step in a Job. I had to make my Job execute an
"Operating
System (CmdExec)". The command line was :

C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe /DTS
"\MSDB\YOURPACKAGEHERE" /SERVER Q /DECRYPT YOURPASSWORDHERE
/MAXCONCURRENT
" -1 " /CHECKPOINTING OFF /REPORTING V

It seems like very much a work-around, but that's roughly the way I
was
told
to keep the protected passwords. My support case person spoke with the
engineers and that was the desired result evidently. They are working
on
documenting the Security Levels more though, as this seems to be
coming
up a
lot. I honestly wouldn't be suprised if something in Security levels
changed
in SP2.


Hope this helps.


"Joe S." <joecrew@xxxxxxxxxxxxxx> wrote in message
news:45738B3D-3021-4E7C-A81F-155792D04CC5@xxxxxxxxxxxxxxxx
I have created a simple import job that first truncates three tables
and
then
imports update data into those tables from an Access database. My
problem
is
that the job fails on when running the SSIS import package. The
import
works
when I create the package, but not from within the jop. I believe it
might
be
the account the job is run under, but I cannot change that, since it
always
reverts back to running under the local system account. WHen looking
at
the
job history I see that the truncate step runs under NT
Authority/system
while
the SSIS package is listed as running under Athena/system (athena is
the
server name). I had to correct a problem with the Reporting Service
being
installed with a simular error and not being able to run until I
changed
the
config file to have NT Authority/system as the user account.








.

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: Problems launching DTS package from Stored Procedure
    ... I cannot run enterprise manager on our server because it is not installed. ... I can post the SQL later if necessary. ... >>package name as a parameter, executes the package and continues on. ...
    (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)