Como calendarizo un paquete creado en SSIS?

Rae,

puede que lo que ocurra sea que el propietario del paquete SSIS sea
diferente al usuario que ejecuta el job / lanza SQL Server Agent. O bien que
este usuario no tenga los permisos necesarios que requiere el paquete SSIS.

Sigue este articulo:

An SSIS package does not run when you call the SSIS package from a SQL
Server Agent job step
http://support.microsoft.com/?id‘8760

An SSIS package does not run when you call the SSIS package from a SQL
Server Agent job step
View products that this article applies to.
Article ID : 918760
Last Review : May 5, 2006
Revision : 1.0

On This Page
SYMPTOMS
CAUSE
RESOLUTION
Method 1: Use a SQL Server Agent proxy account
Method 2: Set the SSIS Package ProtectionLevel property to ServerStorage
Method 3: Set the SSIS Package ProtectionLevel property to
EncryptSensitiveWithPassword
Method 4: Use SSIS Package configuration files
Method 5: Create a package template
STATUS
MORE INFORMATION
Steps to reproduce the problem
Decrypt package secrets
Make sure that you have detailed error information about the SSIS package
failure
About SSIS logging
About the exec subsystem command and output information
REFERENCES
SYMPTOMS
When you call a Microsoft SQL Server 2005 Integration Services (SSIS)
package from a SQL Server Agent job step, the SSIS package does not run.
However, if you do not modify the SSIS package, it will run successfully
outside SQL Server Agent.
Back to the top

CAUSE
This problem occurs when one of the following conditions is true: . The user
account that is used to run the package under SQL Server Agent differs from
the original package author.
. The user account does not have the required permissions to make
connections or to access resources outside the SSIS package.
The package may not run in the following scenarios: . The current user
cannot decrypt secrets from the package. This scenario can occur if the
current account or the execution account differs from the original package
author, and the package's ProtectionLevel property setting does not let the
current user decrypt secrets in the package.
. A SQL Server connection that uses integrated security fails because
the current user does not have the required permissions.
. File access fails because the current user does not have the
required permissions to write to the file share that the connection manager
accesses. For example, this scenario can occur with text log providers that
do not use a login and a password. This scenario can also occur with any
task that depends on the file connection manager, such as a SSIS file system
task.
. A registry-based SSIS package configuration uses the
HKEY_CURRENT_USER registry keys. The HKEY_CURRENT_USER registry keys are
user-specific.
. A task or a connection manager requires that the current user
account has correct permissions.

Back to the top

RESOLUTION
To resolve this problem, use one of the following methods. The most
appropriate method depends on the environment and the reason that the
package failed.
Back to the top

Method 1: Use a SQL Server Agent proxy account
Create a SQL Server Agent proxy account. This proxy account must use a
credential that lets SQL Server Agent run the job as the account that
created the package or as an account that has the required permissions.

This method works to decrypt secrets and satisfies the key requirements by
user. However, this method may have limited success because the SSIS package
user keys involve the current user and the current computer. Therefore, if
you move the package to another computer, this method may still fail, even
if the job step uses the correct proxy account.
Back to the top

Method 2: Set the SSIS Package ProtectionLevel property to ServerStorage
Change the SSIS Package ProtectionLevel property to ServerStorage. This
setting stores the package in a SQL Server database and allows access
control through SQL Server database roles.
Back to the top

Method 3: Set the SSIS Package ProtectionLevel property to
EncryptSensitiveWithPassword
Change the SSIS Package ProtectionLevel property to
EncryptSensitiveWithPassword. This setting uses a password for encryption.
You can then modify the SQL Server Agent job step command line to include
this password.
Method 4: Use SSIS Package configuration files
Use SSIS Package configuration files to store sensitive information, and
then store these configuration files in a secured folder. You can then
change the ProtectionLevel property to DontSaveSensitive so that the package
is not encrypted and does not try to save secrets to the package. When you
run the SSIS package, the required information is loaded from the
configuration file. Make sure that the configuration files are adequately
protected if they contain sensitive information.
Method 5: Create a package template
For a long-term resolution, create a package template that uses a protection
level that differs from the default setting. This problem will not occur in
future packages.
Back to the top

STATUS
This behavior is by design.
Back to the top

MORE INFORMATION
Steps to reproduce the problem
1. Log in as a user who is not part of the SQLServer2005SQLAgentUser
group. For example, you can create a local user.
2. Create an SSIS package, and then add an ExecuteSQL task. Use an
OLEDB connection manager to the local msdb file by using the following
string: 'Windows Authentication' -SQLSourceType: "Direct
Input" -SQLStatement: "sp_who"
3. Run the package to make sure that it runs successfully.
4. Notice that the ProtectionLevel property is set to
EncryptSensitiveWithPassword.
5. Create a SQL Server Agent job and a job step. In the Run As list,
click SQL Server Agent Service to run the job step.
The text in the SQL Server Agent Job History displays information that
resembles the following:
Executed as user: DOMAIN\USERNAME. The package execution failed. The step
failed.

Back to the top

Decrypt package secrets
The default setting for the SSIS package ProtectionLevel property is
EncryptSensitiveWithUserKey. When the package is saved, SSIS encrypts only
the parts of the package that contain properties that are marked
"sensitive," such as passwords, usernames, and connection strings.
Therefore, when the package is reloaded, the current user must satisfy the
encryption requirements for the sensitive properties to be decrypted.
However, the current user does not have to satisfy the encryption
requirements to load the package. When you run the package through a SQL
Server Agent job step, the default account is the SQL Server Agent Service
account. This default account is most likely a different user than the
package author. Therefore, the SQL Server Agent job step can load and start
to run the job step, but the package fails because it cannot complete a
connection. For example, the package cannot complete an OLE DB connection or
an FTP connection. The package fails because it cannot decrypt the
credentials that it must have to connect.

Important Consider the development process and the environment to determine
which accounts are needed and used on each computer. The
EncryptSensitiveWithUserKey setting of the ProtectionLevel property is a
powerful setting. This setting should not be discounted because it causes
deployment complications at first. You can encrypt the packages when you are
logged in to the appropriate account. You can also use the Dtutil.exe SSIS
command-line utility to change the protection levels by using a .cmd file
and the SQL Server Agent command subsystem. For example, follow these steps.
Because you can use the Dtutil.exe utility in batch files and loops, you can
follow these steps for several packages at the same time. 1. Modify the
package that you want to encrypt by using a password.
2. Use the Dtutil.exe utility through an Operating System (cmd Exec)
SQL Server Agent job step to change the ProtectionLevel property to
EncryptSensitiveWithUserKey. This process involves decrypting the package by
using the password, and then re-encrypting the package. The user key that is
used to encrypt the package is the SQL Server Agent job step setting in the
Run As list.

Note Because the key includes the user name and the computer name, the
effect of moving the packages to another computer may be limited.

Back to the top

Make sure that you have detailed error information about the SSIS package
failure
Instead of relying on the limited details in the SQL Server Agent Job
History, you can use SSIS logging to make sure that you have error
information about the SSIS package failure. You can also run the package by
using the exec subsystem command instead of the SSIS subsystem command.
About SSIS logging
SSIS logging and log providers let you capture details about the package
execution and failures. By default, the package does not log information.
You must configure the package to log information. When you configure the
package to log information, you will see detailed information that resembles
the following. In this case, you will know that it is a permissions issue:
OnError,DOMAINNAME,DOMAINNAME\USERNAME,FTP
Task,{C73DE41C-D0A6-450A-BB94-DF6D913797A1},{2F0AF5AF-2FFD-4928-88EE-1B58EB431D74},4/28/2006
1:51:59 PM,4/28/2006 1:51:59 PM,-1073573489,0x,Unable to connect to FTP
server using "FTP Connection Manager".

OnError,DOMAINNAME,DOMAINNAME\USERNAME,Execute SQL
Task,{C6C7286D-57D4-4490-B12D-AC9867AE5762},{F5761A49-F2F9-4575-9E2B-B3D381D6E1F3},4/28/2006
4:07:00 PM,4/28/2006 4:07:00 PM,-1073573396,0x,Failed to acquire connection
"user01.msdb". Connection may not be configured correctly or you may not
have the right permissions on this connection.

About the exec subsystem command and output information
By using the exec subsystem command approach, you add verbose console
logging switches to the SSIS command line to call the Dtexec.exe SSIS
command-line executable file. Additionally, you use the Advanced job feature
of the output file. You can also use the Include Step Output in the history
option to redirect the logging information to a file or to the SQL Server
Agent Job History.

The following is an example of a command line:


dtexec.exe /FILE
"C:\_work\SSISPackages\ProtectionLevelTest\ProtectionLevelTest\AgentTesting.dtsx"
/MAXCONCURRENT " -1
" /CHECKPOINTING OFF /REPORTING V /CONSOLELOG NCOSGXMT


The /console logging returns details that resemble the following:


Error: 2006-04-27 18:13:34.76
Code: 0xC0202009
Source: AgentTesting Connection manager "(local).msdb"
Description: An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available. Source: "Microsoft SQL Native Client"
Hresult: 0x80040E4D Description: "Login failed for user
'DOMAINNAME\username'.".
End Error



Error: 2006-04-28 13:51:59.19
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Property" with
error 0x80070002 "The system cannot find the file specified.". You may not
be authorized to access this information. This error occurs when there is a
cryptographic error. Verify that the correct key is available.
End Error



Log:
Name: OnError
Computer: COMPUTERNAME
Operator: DOMAINNAME\username
Source Name: Execute SQL Task
Source GUID: {C6C7286D-57D4-4490-B12D-AC9867AE5762}
Execution GUID: {7AFE3D9E-5F73-42F0-86FE-5EFE264119C8}
Message: Failed to acquire connection "(local).msdb". Connection may
not be configured correctly or you may not have the right permissions on
this connection.
Start Time: 2006-04-27 18:13:34
End Time: 2006-04-27 18:13:34
End Log
Back to the top

REFERENCES
For more information about a similar problem, click the following article
number to view the article in the Microsoft Knowledge Base:
904800 (http://support.microsoft.com/kb/904800/) You receive an "Error
loading" error message when you try to run an SQL Server 2005 Integration
Services package in SQL Server 2005
For more information about how to use the Dtutil.exe utility in batch
operations, click the following article number to view the article in the
Microsoft Knowledge Base:
906562 (http://support.microsoft.com/kb/906562/) How to use the dtutil
utility (Dtutil.exe) to set the protection level of a batch of SQL Server
Integration Services (SSIS) packages in SQL Server 2005
For more information about how to create package templates, click the
following article number to view the article in the Microsoft Knowledge
Base:
908018 (http://support.microsoft.com/kb/908018/) How to create a package
template in SQL Server Business Intelligence Development Studio


For more information about SSIS package security and the ProtectionLevel
property, see the "Security Considerations for Integration Services" topic
in SQL Server 2005 Books Online.

Unfortunately, users are not aware that default agent job step settings put
them in this state. For more information about SQL Server Agent proxies and
SSIS, see the following topics in SQL Server 2005 Books Online: . Scheduling
package execution in SQL Server Agent
. Creating SQL Server Agent proxies

Back to the top



APPLIES TO
. Microsoft SQL Server 2005 Service Pack 1
. Microsoft SQL Server 2005 Developer Edition
. Microsoft SQL Server 2005 Enterprise Edition
. Microsoft SQL Server 2005 Enterprise Edition for Itanium-based
Systems
. Microsoft SQL Server 2005 Enterprise X64 Edition
. Microsoft SQL Server 2005 Standard Edition
. Microsoft SQL Server 2005 Standard X64 Edition
. Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems


Saludos!

Miguel Angel Cabrera.
"Rae Limon" wrote in message
news:

Quisiera saber si alguien me puede ayudar para calendarizar un paquete que
cree en SSIS es un archivo dtsx y quisiera que se corriera todos los dias
en
la madrugada, intente ponerlo en un job pero me marca lo siguiente

Date 2007-03-13 06:34:26 p.m.
Log Job History (Ventas)

Step ID 1
Server BDCTIENDA35
Job Name Ventas
Step Name Carga Ventas
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: BDCTIENDA35\SYSTEM. The package execution failed. The
step failed.

Alguien podria explicarme como ponerlo en un job? agradezco sus atenciones
mi mail es

begin 666 downarrow.gif
M1TE&.#EA"@`*`/<``````( ```" `(" ````@( `@ " @(" @,# P/\```#_
M`/__````__\`_P#______P``````````````````````````````````````
M````````````````````````````````````````````````````````````
M````,P``9@``F0``S ``_P`S```S,P`S9@`SF0`SS `S_P!F``!F,P!F9@!F
MF0!FS !F_P"9``"9,P"99@"9F0"9S "9_P#,``#,,P#,9@#,F0#,S #,_P#_
M``#_,P#_9@#_F0#_S #__S,``#,`,S,`9C,`F3,`S#,`_S,S`#,S,S,S9C,S
MF3,SS#,S_S-F`#-F,S-F9C-FF3-FS#-F_S.9`#.9,S.99C.9F3.9S#.9_S/,
M`#/,,S/,9C/,F3/,S#/,_S/_`#/_,S/_9C/_F3/_S#/__V8``&8`,V8`9F8`
MF68`S&8`_V8S`&8S,V8S9F8SF68SS&8S_V9F`&9F,V9F9F9FF69FS&9F_V:9
M`&:9,V:99F:9F6:9S&:9_V;,`&;,,V;,9F;,F6;,S&;,_V;_`&;_,V;_9F;_
MF6;_S&;__YD``)D`,YD`9ID`F9D`S)D`_YDS`)DS,YDS9IDSF9DSS)DS_YEF
M`)EF,YEF9IEFF9EFS)EF_YF9`)F9,YF99IF9F9F9S)F9_YG,`)G,,YG,9IG,
MF9G,S)G,_YG_`)G_,YG_9IG_F9G_S)G__\P``,P`,\P`9LP`F<P`S,P`_\PS
M`,PS,\PS9LPSF<PSS,PS_\QF`,QF,\QF9LQFF<QFS,QF_\R9`,R9,\R99LR9
MF<R9S,R9_\S,`,S,,\S,9LS,F<S,S,S,_\S_`,S_,\S_9LS_F<S_S,S___\`
M`/\`,_\`9O\`F?\`S/\`__\S`/\S,_\S9O\SF?\SS/\S__]F`/]F,_]F9O]F
MF?]FS/]F__^9`/^9,_^99O^9F?^9S/^9___,`/_,,__,9O_,F?_,S/_,____
M`/__,___9O__F?__S/___RP`````"@`*```('@`?"!Q(D*",@@@3/CBH,"'#
/AP8',HPXL6#%A@(#`@`[
`
end

begin 666 uparrow.gif
M1TE&.#EA"@`*`/<``````( ```" `(" ````@( `@ " @(" @,# P/\```#_
M`/__````__\`_P#______P``````````````````````````````````````
M````````````````````````````````````````````````````````````
M````,P``9@``F0``S ``_P`S```S,P`S9@`SF0`SS `S_P!F``!F,P!F9@!F
MF0!FS !F_P"9``"9,P"99@"9F0"9S "9_P#,``#,,P#,9@#,F0#,S #,_P#_
M``#_,P#_9@#_F0#_S #__S,``#,`,S,`9C,`F3,`S#,`_S,S`#,S,S,S9C,S
MF3,SS#,S_S-F`#-F,S-F9C-FF3-FS#-F_S.9`#.9,S.99C.9F3.9S#.9_S/,
M`#/,,S/,9C/,F3/,S#/,_S/_`#/_,S/_9C/_F3/_S#/__V8``&8`,V8`9F8`
MF68`S&8`_V8S`&8S,V8S9F8SF68SS&8S_V9F`&9F,V9F9F9FF69FS&9F_V:9
M`&:9,V:99F:9F6:9S&:9_V;,`&;,,V;,9F;,F6;,S&;,_V;_`&;_,V;_9F;_
MF6;_S&;__YD``)D`,YD`9ID`F9D`S)D`_YDS`)DS,YDS9IDSF9DSS)DS_YEF
M`)EF,YEF9IEFF9EFS)EF_YF9`)F9,YF99IF9F9F9S)F9_YG,`)G,,YG,9IG,
MF9G,S)G,_YG_`)G_,YG_9IG_F9G_S)G__\P``,P`,\P`9LP`F<P`S,P`_\PS
M`,PS,\PS9LPSF<PSS,PS_\QF`,QF,\QF9LQFF<QFS,QF_\R9`,R9,\R99LR9
MF<R9S,R9_\S,`,S,,\S,9LS,F<S,S,S,_\S_`,S_,\S_9LS_F<S_S,S___\`
M`/\`,_\`9O\`F?\`S/\`__\S`/\S,_\S9O\SF?\SS/\S__]F`/]F,_]F9O]F
MF?]FS/]F__^9`/^9,_^99O^9F?^9S/^9___,`/_,,__,9O_,F?_,S/_,____
M`/__,___9O__F?__S/___RP`````"@`*```(( `?"!Q(D*",@@,/'BRX\$%#
1AP(7/D2(<")%BA8O/@@(`#L`
`
end