Re: SSIS as part of scheduled job fails

No solution as of yet. Tried using Proxy, tried changing the job to a cmd
step, but everything still fails.

To recap enviroment and desired solution:

WIn 2003 x64 server with SQL 2000 sp4 as default instance and SQL 2005 std
x64 as named instance. All options installed with SQL 2005 (Reporting,
Integration, Analysis, etc). I want to import three tables from a Access 2003
database on a network share into a database on the SQL 2005 instance.
Everything works when done maually, but not if added as part of a scheduled
job.

"Vincent Xu [MSFT]" wrote:

Hi Joe,

I posted your issue in our internal forum and now is waiting for the
response.

I noticed that you are currently working with Matt and I'd like to know how
is everything going now?


Best regards,

Vincent Xu
Microsoft Online Partner Support

======================================================
Get Secure! - www.microsoft.com/security
======================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================
This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================



--------------------
Thread-Topic: SSIS as part of scheduled job fails
thread-index: AcZvr3azlP0jylIXRiCmJuIMm88YAQ==
X-WBNR-Posting-Host: 216.39.152.73
From: =?Utf-8?B?Sm9lIFMu?= <joecrew@xxxxxxxxxxxxxx>
References: <45738B3D-3021-4E7C-A81F-155792D04CC5@xxxxxxxxxxxxx>
<OinSNZrbGHA.1856@xxxxxxxxxxxxxxxxxxxx>
<902401D3-AD5D-488B-9D8F-CB15374B74C5@xxxxxxxxxxxxx>
<OemA8B4bGHA.1272@xxxxxxxxxxxxxxxxxxxx>
<36EBCCC7-B24E-4AF9-AE8A-5AC394795A02@xxxxxxxxxxxxx>
<uLtVXS6bGHA.3956@xxxxxxxxxxxxxxxxxxxx>
Subject: Re: SSIS as part of scheduled job fails
Date: Thu, 4 May 2006 12:18:02 -0700
Lines: 166
Message-ID: <8AC394ED-6711-4D3F-82EB-2C19F4460F0E@xxxxxxxxxxxxx>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
Newsgroups: microsoft.public.sqlserver.dts
Path: TK2MSFTNGXA01.phx.gbl
Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.dts:65685
NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
X-Tomcat-NG: microsoft.public.sqlserver.dts

Can't even open the package. Deloyed to SQL store. Get the following when
I
click on the MSDB folder:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

Client unable to establish connection
Encryption not supported on SQL Server. (Microsoft SQL Native Client)

------------------------------

Client unable to establish connection
Encryption not supported on SQL Server. (Microsoft SQL Native Client)

------------------------------
BUTTONS:

OK
------------------------------







"Matt Yeager" wrote:

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.












.