Wednesday, November 9, 2011

SSIS package does not run when you call the SSIS package from a SQL Server Agent job step

When you call a Microsoft SQL Server 2008 R2 Integration Services (SSIS) package from a SQL Server Agent job step, the SSIS package does not run. But, when You run it mannulay, it wil run fine

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.
Solution
 further information go to http://support.microsoft.com/kb/918760 page read further.

and what i did was,

  • I created a proxy, and navigate to SSIS Packages through Proxy.
  • To create a Proxy you need to Have a Credential that can be created by navigating Security Folder in your SQL Management Studio.
  • Then Go to the Credential Folder and provide your login Information and give it a name.
  • Now Go to the SQL Server Agent and Create a New Proxy for SSIS and provide the Credential which you created.
  • Now Schedule Your SSIS Package and Run as with your newly created Proxy.  
:-)

No comments:

Post a Comment

SAP HANA Cloud Integration SAP : Part -01

What Is SAP HANA Cloud Integration? CPI-DS is a cloud-based data integration tool for batch & scheduled data integration between on-pr...